RE: Why Replication stops ?
I have only recently been looking into replication with MySQL. How does it work? Can I do this? I want our externally hosted web mysql DB to be replicated on our internally hosted non public domain intranet DB. Is it possible? Kind regards, Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 -Original Message- From: Jeff Kilbride [mailto:[EMAIL PROTECTED] Sent: 26. maaliskuuta 2003 9:56 To: [EMAIL PROTECTED] Cc: Jeremy Zawodny Subject: Re: Why Replication stops ? - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: hemanth [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 11:45 PM Subject: Re: Why Replication stops ? On Wed, Mar 26, 2003 at 01:00:33PM +0530, hemanth wrote: Hi Jeremy, Many Thanks for your support. You're welcome. That's what this list is all about (usually). May I know about ur participation in this IT industry. Yes, you may. :-) LOL. Thanks! I needed a laugh after having to re-sync my master and slaves... (see earlier post) --jeff -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 50 days, processed 1,733,356,144 queries (394/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG: Load data infile replication - 3.23.56
On Tue, Mar 25, 2003 at 11:05:06PM -0800, Jeff Kilbride wrote: Replication doesn't seem to be replicating LOAD DATA INFILE correctly in 3.23.56. Starting with a master and slave that were in sync, I imported a file: [snip] Notice that the slave doesn't skip the records that the master does, so the master and slave are out of sync after the LOAD DATA INFILE executes. Interesting. I have a theory, but I'd need to toy with it a bit before embarassing myself with a guess... Might you also put this up on bugs.mysql.com? Also, is there any way to find out what records were skipped and why? Nope. Not yet. I think that's coming in 4.1? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 50 days, processed 1,736,124,981 queries (394/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_query to return immediately when connection lost
Hi, I also tried that but the same result, mysql_ping doesn't return immediately. Any other hint please? Thank you very much. Pedro Gelu Gogancea wrote: Hi, In this case i guess is better to execute mysql_ping() before mysql_query(). Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Pedro Alvarez-Tabío [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Gelu Gogancea [EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 4:34 PM Subject: Re: mysql_query to return immediately when connection lost Hi, In this case I do have a DNS server. Any other hint please? I strongly need mysql_query to return immediately if connection is lost with the server. Any configuration parameter or similar on MySQL? Need to reconfigure something in the OS? Thanks. Pedro Gelu Gogancea wrote: Hi, This is usually happened becuase you don't have configured a DNS server in your network. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Pedro Alvarez-Tabío [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 1:47 PM Subject: mysql_query to return immediately when connection lost Hello, My client uses mysql_query to access a remote server. When the connection is lost (cable disconnected), mysql_query doesn't return immediately and I need a fast error return. I have tried executing mysql_options(mysql, MYSQL_OPT_CONNECT_TIMEOUT, connectTimeout); (connectTimeout=1), always before connecting (mysql_real_connect). Also set mysql.reconnect to 0 before executing mysql_query. But no success. I need mysql_query to immediately return when a connection is lost in order the client to switch to the replicated server. Would you please tell me how can I do it? Thank you. Pedro -- |\ | \ | \ |___\ | | -- 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: Why Replication stops ?
On Wed, Mar 26, 2003 at 10:02:52AM +0200, Steve Jackson wrote: I have only recently been looking into replication with MySQL. How does it work? Can I do this? I want our externally hosted web mysql DB to be replicated on our internally hosted non public domain intranet DB. Is it possible? Yes. A good place to start is the Replication section of the manual: http://www.mysql.com/doc/ Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 50 days, processed 1,736,303,250 queries (395/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sizes
which one is the biggest datatype: enum('true', 'false');OR tinyint(1); I mena biggest when it comes to store them on disk. Thank you, Mattias.
Re: sizes
Mattias, which one is the biggest datatype: enum('true', 'false');OR tinyint(1); I mena biggest when it comes to store them on disk. ENUM has a storage requirement of 1 or 2 bytes. If you have up to 256 ENUM values to choose from, you need 1 byte, if you have more, it will take 2 bytes. TINYINT will always need 1 byte. http://www.mysql.com/doc/en/Storage_requirements.html Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Permission Setup
Terry, mysqlGRANT ALL ON Security.* to [EMAIL PROTECTED] Identified by 'password'; Query OK, 0 rows affected(0,00 sec) Try to rewrite that as follows: GRANT ALL ON Security.* TO 'newbuddy'@'network.srv' ... The dot in network.srv could otherwise cause problems. After granting, do: SHOW GRANTS FOR 'newbuddy'@'network.srv'; If newbuddy still isn't granted access, you should check your DNS. Alternatively, you could also issue the following, which should work in any case (even if your DNS isn't set up correctly): GRANT ALL ON Security.* TO 'newbuddy'@'192.111.111.111' ... Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: BUG: Load data infile replication - 3.23.56
Jeremy, Also, is there any way to find out what records were skipped and why? Nope. Not yet. I think that's coming in 4.1? Yes and no: http://www.mysql.com/doc/en/SHOW_WARNINGS.html The MySQL server sends back the total number of warnings and errors you got for the last command. My interpretation of that is you won't be able to identify individual records that were skipped, just the total number of those. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sizes
They both use 1 byte Check this for more information http://www.mysql.com/doc/en/Storage_requirements.html Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ - Original Message - From: Mattias Barthel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 19:02 Subject: sizes which one is the biggest datatype: enum('true', 'false');OR tinyint(1); I mena biggest when it comes to store them on disk. Thank you, Mattias. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql under unix in batch mode
Hi, I want to execute a simple query in batch mode in a mysql database. my OS : Unix (solaris 2.6) when I run this command : mysql -h localhost -u root -p mysql did.query did.out I'm always asked password even if I've already specified it in the previous command line (mysql) Enter password: How to avoid it ? Didier ([EMAIL PROTECTED]).
Re: mysql under unix in batch mode
Dnia Wed, 26 Mar 2003 12:12:44 +0100 Marianadin, Didier [EMAIL PROTECTED] zezna/a co nastpuje: Hi, I want to execute a simple query in batch mode in a mysql database. my OS : Unix (solaris 2.6) when I run this command : mysql -h localhost -u root -p mysql did.query did.out I'm always asked password even if I've already specified it in the previous command line (mysql) Enter password: mysql -h localhost -u root -pyour_password mysql did.query did.out Notice there is no space between -p and your_password. -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql under unix in batch mode
Hi, most probably there should not be a space after -p. Try this mysql -h localhost -uroot -pmysql did.query did.out Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ - Original Message - From: Marianadin, Didier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 13:12 Subject: mysql under unix in batch mode Hi, I want to execute a simple query in batch mode in a mysql database. my OS : Unix (solaris 2.6) when I run this command : mysql -h localhost -u root -p mysql did.query did.out I'm always asked password even if I've already specified it in the previous command line (mysql) Enter password: How to avoid it ? Didier ([EMAIL PROTECTED]). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: RE: load data infile question
On Tuesday 25 March 2003 19:54, Salada Duncan wrote: I was able to work around the problem by giving insert privileges to the user for the WHOLE database. This is definitely not optimal because I only wish this user to be able to insert into one table in the database. But as soon as I gave the user insert privileges for the whole database the load data infile command worked. This seems like a bug to me, perhaps it has been dealt with in newer versions though. It works fine for me on 3.23.56. Could you upgrade MySQL server and test on the 3.23.56? -- 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: mysql under unix in batch mode
On Wednesday 26 March 2003 13:12, Marianadin Didier wrote: I want to execute a simple query in batch mode in a mysql database. my OS : Unix (solaris 2.6) when I run this command : mysql -h localhost -u root -p mysql did.query did.out I'm always asked password even if I've already specified it in the previous command line (mysql) Enter password: How to avoid it ? Use mysql -h localhost -u root -proot_password mysql did.query did.out There is no space between -p and root password. -- 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: Restoring a DB
On Tuesday 25 March 2003 15:30, Todd Cary wrote: If I have a file, mydb.mbk that was created with mysqldump, how do I recreate/restore the DB? Do I run a command line using mysql with a switch and mydb.mbk? Yup. musql -uuser_name -puser_password database_name mydb.mbk -- 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]
Error: The table is full
i'm running the command: update contratante,pessoa set contratante.endereco = pessoa.endereco ,contratante.cidade = pessoa.cidade ,contratante.bairro = pessoa.bairro ,contratante.estado = pessoa.estado ,contratante.telefone = pessoa.telefone ,contratante.cep = pessoa.cep where (contratante.codpes = pessoa.codpes); The table contratante have 148867 rows The table pessoa have 119319 rows i can update the columns endereco,cidade,bairro,estado,telefone and cep of table contratante with the information of table pessoa... but I start the SQL STATEMENT and the error: The Table 'contratante' is full. Why ? i don't insert anything inside the table contratante,only update the columns with the pessoa information... My System is: Linux 2.4.18 / MySQL/InnoDB 4.0.12 with 512 of RAM / ReiserFS The free space on tablespace is 10G The my.cnf of innodb is: innodb_data_file_path=ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M;ibdata5:4000M;ibdata6:4000M;ibdata7:4000M innodb_data_home_dir = /database/innodb/data set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /database/innodb/logs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=60M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=2 innodb_log_arch_dir = /database/innodb/logs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=256M set-variable = innodb_additional_mem_pool_size=30M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=60 innodb_fast_shutdown = 1 The create table of contratante is: CREATE TABLE `contratante` ( `Sequencia` int(4) unsigned NOT NULL default '0', `Protocolo` varchar(8) NOT NULL default '', `PLivro` varchar(10) NOT NULL default '', `PData` date NOT NULL default '-00-00', `Registro` varchar(8) NOT NULL default '', `Rlivro` varchar(10) NOT NULL default '', `RData` date NOT NULL default '-00-00', `CodPes` int(4) NOT NULL default '0', `Denominacao` tinyint(4) NOT NULL default '0', `NumAverb` int(2) NOT NULL default '0', `SequenciaPri` int(4) unsigned NOT NULL default '0', `RegistroPri` varchar(8) NOT NULL default '', `RLivroPri` varchar(10) NOT NULL default '', `Ordem` int(2) NOT NULL default '0', `control` char(1) NOT NULL default '', `CodNat` int(2) NOT NULL default '0', `ImpNot` tinyint(1) default '1', `Endereco` varchar(255) NOT NULL default '', `Cidade` varchar(40) NOT NULL default '', `Bairro` varchar(20) NOT NULL default '', `Estado` char(2) NOT NULL default '', `Telefone` varchar(30) NOT NULL default '', `Cep` varchar(9) NOT NULL default '', UNIQUE KEY `cont_idx7` (`Sequencia`,`CodPes`), KEY `cont_idx1` (`Sequencia`,`Ordem`), KEY `cont_idx2` (`CodNat`), KEY `cont_idx3` (`SequenciaPri`,`Ordem`), KEY `cont_idx5` (`CodPes`), KEY `cont_idx6` (`ImpNot`), KEY `socio_idx5` (`CodPes`) ) TYPE=InnoDB; The Create table of pessoa is: CREATE TABLE `pessoa` ( `CodPes` int(4) NOT NULL auto_increment, `Nome` varchar(255) NOT NULL default '', `CpfCgc` varchar(18) NOT NULL default '', `CpfCgc2` varchar(18) NOT NULL default '', `TipoPes` tinyint(4) NOT NULL default '0', `CodCus` int(2) NOT NULL default '0', `Endereco` varchar(255) NOT NULL default '', `Telefone` varchar(50) NOT NULL default '', `Cidade` varchar(60) NOT NULL default '', `Bairro` varchar(40) NOT NULL default '', `Estado` char(2) NOT NULL default '', `Mensalista` tinyint(4) NOT NULL default '0', `Cep` varchar(9) NOT NULL default '', UNIQUE KEY `CodPes` (`CodPes`), KEY `PESSOA_INDX1` (`CodPes`), KEY `PESSOA_INDX2` (`CpfCgc`), KEY `PESSOA_INDX3` (`Nome`) ) TYPE=InnoDB Tanks in advance. sql,query,innodb,HEP ! - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 221602060 $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug Report
__ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: load data infile question
-Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 6:55 AM To: [EMAIL PROTECTED] Subject: re: RE: load data infile question Hi! On Tuesday 25 March 2003 19:54, Salada Duncan wrote: I was able to work around the problem by giving insert privileges to the user for the WHOLE database. This is definitely not optimal because I only wish this user to be able to insert into one table in the database. But as soon as I gave the user insert privileges for the whole database the load data infile command worked. This seems like a bug to me, perhaps it has been dealt with in newer versions though. It works fine for me on 3.23.56. Could you upgrade MySQL server and test on the 3.23.56? Thanks for the suggestion to upgrade Victoria. It jogged my memory that our development server had a copy of 3.23.54 (we recently upgraded it to 4.0.12 in prepration for moving to 4 for production). I fired up 3.23.54, and everything worked as planned. So, it seems this isn't a problem in 3.23.54. Sorry to take up your time. Duncan --- Duncan Salada Titan Systems Corporation 301-925-3222 x375 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't set timeout on MyODBC/MySQL from an ADO/ASP program on Windows XP?
Hi, I've tried without success to set a timeout for SQL queries on a MySQL server (version 4.0.12 - so far the latest production release) running on a Windows XP SP1 box. The calling program is a Web site developed in ASP (going through ADO to access MyODBC...). I tried many different methods but there is no way I can STOP a SQL query that takes too long to execute (like... more than 5 minutes) and RAISE AN ERROR to the calling application (in that case an ASP page). METHOD 1: ADODB.Connection.CommandTimeout = % var strConnectionString = DSN=MyDSN; var strSQLRequest = SELECT * FROM MyHugeTable; // Create connection object var objConnection = Server.CreateObject(ADODB.Connection); // Set CommandTimeout to 30 seconds (on the Connection object) objConnection.CommandTimeout = 30; // Open the connection objConnection.Open(strConnectionString); // Create recordset object var objRecordset = Server.CreateObject(ADODB.recordset); // Execute SQL request objRecordset.Open(strSQLRequest, objConnection); // The above line is very long to execute and NEVER TIMES OUT!!! // Just check if it's open and not empty Response.Write(objRecordset.EOF = +objRecordset.EOF+br); % METHOD 2: ADODB.Command.CommandTimeout = % var strConnectionString = DSN=MyDSN; var strSQLRequest = SELECT * FROM MyHugeTable; // Create connection object var objConnection = Server.CreateObject(ADODB.Connection); // Open the connection objConnection.Open(strConnectionString); var objCommand = Server.CreateObject(ADODB.Command); objCommand.ActiveConnection = objConnection; objCommand.CommandText = strSQLRequest; // Set CommandTimeout to 30 seconds (this time on the Command object) objCommand.CommandTimeout = 30; // Execute SQL request objRecordset = objCommand.Execute(); // The above line is very long to execute and NEVER TIMES OUT!!! // Just check if it's open and not empty Response.Write(objRecordset.EOF = +objRecordset.EOF+br); % METHOD 3: ADODB.Connection.Properties(General Timeout) = % var strConnectionString = DSN=MyDSN; var strSQLRequest = SELECT * FROM MyHugeTable; // Create connection object var objConnection = Server.CreateObject(ADODB.Connection); // Set General Timeout to 30 (seconds?) objConnection.Properties(General Timeout).Value = 30; // Open the connection objConnection.Open(strConnectionString); // Create recordset object var objRecordset = Server.CreateObject(ADODB.recordset); // Execute SQL request objRecordset.Open(strSQLRequest, objConnection); // The above line is very long to execute and NEVER TIMES OUT!!! // Just check if it's open and not empty Response.Write(objRecordset.EOF = +objRecordset.EOF+br); % Nothing works... Has anybody ever tried this? Am I the only one with this problem? Is there any known solution to that problem? Note: I tried this on a Windows XP SP1 box as well as on a Windows 2000 Server SP3, with MySQL 4.0.12 and MyODBC 3.51.05 (all production releases), and MDAC 2.7 Emmanuel KARTMANN Web Consultant Replay Software Development Company Email: [EMAIL PROTECTED] Phone: +33 (0)6 60 41 68 83 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Autoincrement/last_insert_id race safe?
A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? It seems that another program could be inserting at almost the same time, and could increment the counter again before my last_insert_id() checks it. In that case, I would not be dealing with the same line I just inserted. Does anyone know if this is a genuine concern? If it is, can anyone think of a workaround? Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Formatting timestamp in MySQL outfile
hi everybody, i would like to know, if there exists a method for getting a format like '2003-12-31 12:00:00' for the time stamp in the text file generated using the outfile command. the normal format is '2003123112' any help would be greatly appreciated. Thanks in advance anirudh Filter guard: sql,query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
Hi, On Wed, Mar 26, 2003 at 03:17:42PM +0100, Steve Rapaport wrote: A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? Yes. The last inserted id is kept per connection. So unless YOU insert another record using the same connection, the last_insert_id isn't changed. Also, last_insert_id in a new connection will give you NULL, regardless of what you did in a previous connection. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? Yes It seems that another program could be inserting at almost the same time, and could increment the counter again before my last_insert_id() checks it. In that case, I would not be dealing with the same line I just inserted. last_insert_id is stored on a per-connection basis, and frecords the last insert done by that connection -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
missing file-
Hello In attempting to build winmysqladmin.cpp winmysqladmin.cpp \MySQL\mysql-4.1\VC++Files\winmysqladmin\winmysqladmin.cpp(2) : fatal error C1083: Cannot open include file: 'vcl.h': No such file or directory initsetup.cpp \MySQL\mysql-4.1\VC++Files\winmysqladmin\initsetup.cpp(2) : fatal error C1083: Cannot open include file: 'vcl.h': No such file or directory db.cpp \MySQL\mysql-4.1\VC++Files\winmysqladmin\db.cpp(2) : fatal error C1083: Cannot open include file: 'vcl.h': No such file or directory Error executing cl.exe. winmysqladmin.exe - 3 error(s), 0 warning(s) Where is vcl.h ??? Many Thanks, Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
On Wednesday 26 March 2003 08:25, you wrote: A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? Yes It seems that another program could be inserting at almost the same time, and could increment the counter again before my last_insert_id() checks it. In that case, I would not be dealing with the same line I just inserted. last_insert_id is stored on a per-connection basis, and frecords the last insert done by that connection so, if your using a connection pooling system then a race condition can happen, but its only due to the connections being shared. anyone happen to know if coldfusion's use of odbc locks the connection per page request or does it just do it on queries and unlocks it as soon as the query is done? -- sql sql sql mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to remove mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 25 Mar 2003, katherine bjork wrote: How do I remove mysql from my mac. I tried the script on the entropy website but it only works on version older than 4.x. I can seem to find any documentation regarding removal on the products website either. To remove MySQL, you need to remove the following directories: /usr/local/mysql* /Library/Receipts/mysql-version.pkg/ Make sure to backup any important data first! Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+gaxBSVDhKrJykfIRAr7rAJ42iZp3mGyFU023tuvdUehDtlzquwCdGfTm rplXES3PG3UkdZX5dRBCDxw= =tSTJ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqladmin -u root -h myhost password 'new-password' fails
You guys probably need to start mysql with the --skip-grant option and fix the allow tables... See the mysql.com website or google.com for lost password mysql Thanks for the tip about how to proceed. Just so we don't lose track here, the point of the bug we're reporting is that mysql_install_db is telling us to do something that doesn't work (and, even worse, leaves the [EMAIL PROTECTED] user without a password). So either mysql_install_db should be changed to say the right thing, or the command should be made to work :). I do not know enough to even guess what the underlying problem might be. Thanks, karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daemon wont start
The mysql data directories and files must be owned by mysql. Error 13 is a permission denied operating system error. Toto Gamez wrote: i installed mysql-3.23.41-1 on my RH7.2 box but when i run safe_mysqld, mysql daemon started but dies i tried to check the log and says: 030326 14:35:46 mysqld started 030326 14:35:47 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 030326 14:35:47 mysqld ended how do I correct this -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql under unix in batch mode
Create a .my.cnf file in your home directory with permissions set to 600. In that file put the lines [mysql] user=root password=mysql now you can enter: mysql did.query did.out Marianadin, Didier wrote: Hi, I want to execute a simple query in batch mode in a mysql database. my OS : Unix (solaris 2.6) when I run this command : mysql -h localhost -u root -p mysql did.query did.out I'm always asked password even if I've already specified it in the previous command line (mysql) Enter password: How to avoid it ? Didier ([EMAIL PROTECTED]). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE function question
I have a small question about the DATE function. This is what I want to do: - User enters information into form and submits it to database - When info is added to database the current date is stored ala 2003-03-26 - Now I want to take that date 2003-03-26 and tack on either 15 or 30 days on to it so the date in another column reads either 2003-04-10 or 2003-04-25 I just can't seem to figure out how to do that math right so I get the result I'm looking for. Any thoughts or ideas? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE function question
I believe the following will work: date_add([thedatefield],Interval 15 days) I am almost sure I got the syntax right off the top of my head. Check mysql.com for date functions if the above doesn't work. Serge. On Wed, 26 Mar 2003 10:16:18 -0500 Tom Ray [EMAIL PROTECTED] wrote: I have a small question about the DATE function. This is what I want to do: - User enters information into form and submits it to database - When info is added to database the current date is stored ala 2003-03-26 - Now I want to take that date 2003-03-26 and tack on either 15 or 30 days on to it so the date in another column reads either 2003-04-10 or 2003-04-25 I just can't seem to figure out how to do that math right so I get the result I'm looking for. Any thoughts or ideas? 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: mysql under unix in batch mode
the other thing is make sure you don't have a space after -p and before your password mysql -h localhost -u root -p mysql did.query did.out says ask for the password and start in the database mysql mysql -h localhost -u root -pmysql did.query did.out says the password is mysql and don't start in any database On Wednesday 26 March 2003 09:12, you wrote: Create a .my.cnf file in your home directory with permissions set to 600. In that file put the lines [mysql] user=root password=mysql now you can enter: mysql did.query did.out Marianadin, Didier wrote: Hi, I want to execute a simple query in batch mode in a mysql database. my OS : Unix (solaris 2.6) when I run this command : mysql -h localhost -u root -p mysql did.query did.out I'm always asked password even if I've already specified it in the previous command line (mysql) Enter password: How to avoid it ? Didier ([EMAIL PROTECTED]). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.12 rpm's
Can anyone tell me where I can get 4.0.12 server and client rpm's? TIA, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
missing safe_mysql
Good Day MYSQL helpers, On Redhat 8 we were running a FAQman software. A few days ago, mysql was not working, come to find out that the safe_mysql file was missing. This is the error that we get even though we can start up the safe_mysql » MySQL Error There was an error connecting to the database. Error Number: 2002 Error Message: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) We do see all the entries in the table (using phpmyadmin).. .just need to relink .. I think. Does anyone have a procedure to recover .. or maybe we are going about it the wrong way. Thanks for your time and talents Make it a great day! Jeff Hatala - Computing Resources - BCC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Mysqld 4.0.12 crashes on signal 11 when static]
Description: Mysqld 4.0.12 crashes on Slackware 9.0 when compiled with -all-static. I tried changing the thread stack up to 2M - no effect. When compiled without -all-static mysqld seems stable. The crash is first reported by mysql_install_db. Output of mysql_install_db bellow. Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 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=262144 read_buffer_size=131072 sort_buffer_size=2097144 max_used_connections=0 max_connections=16 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 35071 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x82c3ea8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbf5fea48, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80894f4 0x815efca 0x8175387 0x8098f75 0x809bc6e 0x8098d94 0x80937cc 0x815913e 0x8198f24 /usr/local/mysql/bin/mysql_install_db: line 1: 1596 Segmentation fault /usr/local/mysql/libexec/mysqld --bootstrap --skip-grant-tables --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --skip-innodb --skip-bdb Installation of grant tables failed! How-To-Repeat: Install Slackware 9.0 vanilla (less precompiled mysql). Untar mysql source, configure, compile and install. Run mysql_install_db. Fix: Remove -all-static from mysql configuration. That will make mysqld slightly slower. Submitter-Id: Originator: Gabriel Tataranu Organization: MySQL support: [ none ] Synopsis: Mysqld 4.0.12 crashes on signal 11 when static Severity: [ non-critical ] Priority: [ medium ] Category: mysql Class: [ sw-bug ] Release: mysql-4.0.12 (Source distribution) C compiler:gcc (GCC) 3.2.2 C++ compiler: g++ (GCC) 3.2.2 Environment: Pentium III , Slackware 9.0. System: Linux office2 2.4.20 #2 Mon Mar 17 22:02:15 PST 2003 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/3.2.2/specs Configured with: ../gcc-3.2.2/configure --prefix=/usr --enable-shared --enable-threads=posix --enable-__cxa_atexit --disable-checking --with-gnu-ld --verbose --target=i386-slackware-linux --host=i386-slackware-linux Thread model: posix gcc version 3.2.2 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 24 11:02 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1435624 Mar 5 00:57 /lib/libc-2.3.1.so -rw-r--r--1 root root 2425490 Mar 5 00:57 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 5 00:50 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler' '--without-debug' '--with-mysqld-ldflags=-all-static' '--without-bench' '--without-innodb' '--without-docs' '--with-extra-charsets=none' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Formatting timestamp in MySQL outfile
On 26-Mar-2003 anirudha kukreti wrote: hi everybody, i would like to know, if there exists a method for getting a format like '2003-12-31 12:00:00' for the time stamp in the text file generated using the outfile command. the normal format is '2003123112' any help would be greatly appreciated. Thanks in advance anirudh DATE_FORMAT(ts, '%Y-%m-%d %T') Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sum() using group, and duplicates problems...
Don Thanks. I will have to change them and try that. I had been bouncing back and forth between decimal and numeric but wasn't sure which was working better. I still think it might have to do with my sql query. Here's an example as you previously mentioned... SELECT DISTINCT measurement, sum(val), pcfver, hour, release FROM pcf WHERE release = curdate()-1 group by measurement, hour; The -sql runs fine, but there are hours in which I receive duplicated input data. I have filtered as much as I can, but was really needing help from the database driver to eliminate the rest. Thanks for your help. Regards, Kelly W. Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Don Read [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 8:30 PM To: Black, Kelly W [PCS] Cc: Mysql (E-mail) Subject: RE: sum() using group, and duplicates problems... On 25-Mar-2003 Black, Kelly W [PCS] wrote: On 25-Mar-2003 Black, Kelly W [PCS] wrote: Hello. Sorry for the long post. This has been a big problem for me, and I hope someone will help... I have a long standing problem with an MySQL query. Or perhaps a couple of sql query problems... Firstly I have been told I cannot use sum() on a value while using group by parm because it's not accurate. This is obvious as the result sets are often quite wrong... I'm not sure where you got that. I've never seen it. See attached screen shot. It's common knowledge that when comparing six or eight there's no problem. But once you move into the millions there's quite a margin for error. mysql select .3334455 * 94343473264278464 as test; +---+ | test | +---+ | 314478244215319756800.000 | +---+ 1 row in set (0.00 sec) Since you'll only get 15 significant digits w/ a 64bit float, you're in for considerable margins of error. MySQL is _NOT_ an arbitrary precision calculator. If you need that kind of precision, you should be working with DECIMAL(). Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug Report, timestamp columns
Hi Mysql, This is a bug report. There are two cases, because the bug is quite old, I detected it the first time a year ago on a 3.23.44 with MyISAM, but I thought it would be fixed soon, sorry. This bug happens in 4.0.8 too with MyISAM tables. Test Case 1 --- Operating System: Linux Intel, SuSE 6.3 (Kernel 2.2.13) Processor : ADM K6-2 400 System RAM : 128M Disk Subsystem : SCSI-2 (dpt) FIle Sytem handler : ext2 MySQL version: mysql-max-3.23.49a-linux-gnu-i686 Table Handler: Derkeley DB. Symptom : Two colums are updated insted of one. ---the script starts here create database prueba; use prueba; create table pr (a int, b timestamp, c timestamp, d timestamp, e int) type=bdb; insert into pr values (0, now(), now(), now(), 1); select * from pr; # # Insert a delay here !!! # update pr set c=now(); select * from pr; the script ends here- the output from `mysql -t pr1.sqlsal' client is: +--++++--+ | a| b | c | d | e| +--++++--+ |0 | 20030324145209 | 20030324145209 | 20030324145209 |1 | +--++++--+ +--++++--+ | a| b | c | d | e| +--++++--+ |0 | 20030324145304 | 2003010110 | 20030324145209 |1 | +--++++--+ As you can see the colum `b' is updated, too. Note, you have to insert a delay of almost 1 second between the first select and the update, because the column `b' takes the current time!. Only happens with timestamp columns not with datetime ones. Test Case 2 --- Operating System: Linux Intel, RedHat 7.3 6.3 (Kernel 2.4.18) Processor : ADM K6-2 400 System RAM : 128M Disk Subsystem : SCSI-2 (dpt) FIle Sytem handler : ext2 MySQL version: mysql-standard-4.0.12-pc-linux-i686 Table Handler: InnoDB. Symptom : Two colums are updated insted of one. ---the script starts here create database prueba2; use prueba2; create table pr (a int, b timestamp, c timestamp, d timestamp, e int) type=innodb; insert into pr values (0, now(), now(), now(), 1); select * from pr; # # Insert a delay here !!! # update pr set c=now(); select * from pr; the script ends here- the output from `mysql -t pr2.sqlsal' client is: +--++++--+ | a| b | c | d | e| +--++++--+ |0 | 20030324145653 | 20030324145653 | 20030324145653 |1 | +--++++--+ +--++++--+ | a| b | c | d | e| +--++++--+ |0 | 20030324145739 | 2003010111 | 20030324145653 |1 | +--++++--+ As you can see the colum `b' is updated, too. Note, you have to insert a delay of almost 1 second between the first select and the update, because the column `b' takes the current time!. Only happens with timestamp columns not with datetime ones. I hope it will help you to make MySQL better. For further information, please don't hesitate to contact me at: [EMAIL PROTECTED] Roebrto Alejandro Paz Schmidt Republica Argentina __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.12 rpm's
On Wed, 26 Mar 2003, Paul wrote: Can anyone tell me where I can get 4.0.12 server and client rpm's? www.mysql.com Regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Formatting timestamp in MySQL outfile
You should be able to use DATE_FORMAT to format the TIMESTAMP anyway you need. Check out DATE_FORMAT at: http://www.mysql.com/doc/en/Date_and_time_functions.html At 20:01 +0530 3/26/03, anirudha kukreti wrote: hi everybody, i would like to know, if there exists a method for getting a format like '2003-12-31 12:00:00' for the time stamp in the text file generated using the outfile command. the normal format is '2003123112' any help would be greatly appreciated. Thanks in advance anirudh Filter guard: sql,query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Shapiro, Colorado Springs, CO, USA At work I *have* to use a Windows machine, at home I *get* to use a Mac. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange Query
Don't know if this has already come through to everyone, if so I apologize. Ok, here's the deal Table1 is a master list holding ID numbers, and related information sent by clients Table2 is a queue that utilizes Table1.ID numbers, and updates other information on the current state of a job associated with these ID numbers within Table2. This table is used only as a queue, so the Table1.ID numbers come and go as jobs complete. There can be multiple instances, or 0 instances of Table1.ID on Table2. Table3 is a log that contains even more information concerning the Table1.ID numbers, and associated processing information from Table2, and information of further processing, shipping status, tracking numbers, etc. There can be multiple instances, or 0 instances of the Table1.ID number on Table3. So, what I am trying to do is this: Table1 holds this info(simplified): MasterID(Unique) int NumberRequested int Table2 holds this info(simplified): MasterID int OrderID(Unique) int Table3 holds this info(simplified): MasterID int OrderID(Unique) int JobState bool What I need to extract, within a single statement, is this: For each MasterID on Table1 What is the NumberRequested Does the MasterID exist on Table2, if so, how many times? (QUEUED) Does the MasterID exist on Table3, and if so, How many times with JobState TRUE (GOOD) How many times with JobState FALSE (BAD) If the NumberRequested is greater than QUEUED+GOOD, then return a 1 as (UHOH) So, looking for a return like: MASTERIDNUMBERREQUESTED QUEUED GOOD BAD UHOH 1 105 4 1 1 2 1010 0 0 0 3 108 2 1 0 4 106 0 4 1 5 100 100 0 6 100 5 5 1 This is what I tried: SELECT Table1.MasterID AS MasterID, Table1.numberofcopies AS numberrequested, SUM(CASE WHEN TABLE3.jobstate=0 AND TABLE3.MasterID=Table1.MasterID THEN 1 END) AS good, SUM(CASE WHEN Table3.jobstate=1 AND Table3.MasterID=Table1.MasterID THEN 1 END) AS bad, SUM(CASE WHEN Table2.MasterID=Table1.MasterID THEN 1 END) AS queued, (CASE WHEN numberrequested queued+good THEN 1 ELSE 0 END) AS uhoh FROM Table1, Table2, Table3 GROUP BY Table1.MasterID; Help please? Thanks, Mike
RE: sum() using group, and duplicates problems...
On 26-Mar-2003 Black, Kelly W [PCS] wrote: Don Thanks. I will have to change them and try that. I had been bouncing back and forth between decimal and numeric but wasn't sure which was working better. I still think it might have to do with my sql query. Here's an example as you previously mentioned... SELECT DISTINCT measurement, sum(val), pcfver, hour, release FROM pcf WHERE release = curdate()-1 group by measurement, hour; The -sql runs fine, but there are hours in which I receive duplicated input data. I have filtered as much as I can, but was really needing help from the database driver to eliminate the rest. Thanks for your help. Couple of problems I see in the query: 1. curdate()-1 don't do what you think it do: mysql select curdate(), curdate()-27; ++--+ | curdate() | curdate()-27 | ++--+ | 2003-03-26 | 20030299 | ++--+ 1 row in set (0.00 sec) --use DATE_SUB(curdate(), INTERVAL 1 DAY) instead. 2. If you have duplicates that is going to hammer your results. The DISTINCT function filters the result set _after_ they're selected (and SUM'ed). If you have duplicate entries, the sum(val) will total both rows in the group --probably not what you want. A Perl script could help clear up any duplicates. Then you could add a UNIQUE key to keep 'em out. my $0.02. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transaction support
Hello, I experiencing a problem I hope someone here can help with: I have several C-coded clients running and performing inserts and updates on a database. I would like to make sure that the -entire- client session is atomic. i.e. if a client dies in the middle of the computation (not uncommon) then all of the updates are rolled back. I take it I can do that by using InnoDB tables and transactions the problems: - the number of updates and inserts per client is large ~ 10,000 or so inserts/updates on many tables. I need ALL of them to be one single atomic operation. is there any limit on the size of a transaction. any way to increase such a limit? - if the client dies in the middle of the computation it obviously won't be able to issue an explicit rollback. what happens in this instance. is possible to set things up so that an automatic rollback takes place. - this might be tricky. but during most inserts my C program retrieves row ids (using mysql_insert_id() function. this id is then used in subsequent inserts. would that still work with transactions. i.e. without committing an insert to a table, what would mysql_insert_id return? - would performance with such large transactions be substantially degraded? this is rather important issue for me. I'd really appreciate any help in this regard. many thanks in advance Murad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() using group, and duplicates problems...
The DISTINCT is almost usless when done on a GROUP BY clause. Also DISTINCT operates on the entire row and not just on the field. ie It will give you DISTINCT measurement, sum(val), pcfver, hour, release. And not just DISTINCT measurement results. Serge. On Wed, 26 Mar 2003 12:00:24 -0600 (CST) Don Read [EMAIL PROTECTED] wrote: On 26-Mar-2003 Black, Kelly W [PCS] wrote: Don Thanks. I will have to change them and try that. I had been bouncing back and forth between decimal and numeric but wasn't sure which was working better. I still think it might have to do with my sql query. Here's an example as you previously mentioned... SELECT DISTINCT measurement, sum(val), pcfver, hour, release FROM pcf WHERE release = curdate()-1 group by measurement, hour; The -sql runs fine, but there are hours in which I receive duplicated input data. I have filtered as much as I can, but was really needing help from the database driver to eliminate the rest. Thanks for your help. Couple of problems I see in the query: 1. curdate()-1 don't do what you think it do: mysql select curdate(), curdate()-27; ++--+ | curdate() | curdate()-27 | ++--+ | 2003-03-26 | 20030299 | ++--+ 1 row in set (0.00 sec) --use DATE_SUB(curdate(), INTERVAL 1 DAY) instead. 2. If you have duplicates that is going to hammer your results. The DISTINCT function filters the result set _after_ they're selected (and SUM'ed). If you have duplicate entries, the sum(val) will total both rows in the group --probably not what you want. A Perl script could help clear up any duplicates. Then you could add a UNIQUE key to keep 'em out. my $0.02. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- 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 4.0 Questions....
Hi, I am using MySQL 4.0 (with Embedded Server) on a Mac OS X (Darwin) machine. I am using the C API of MySQL in my programs. I have a few questions regarding this. 1) When I compile my programs with the g++ 3.1 compiler, i get the following warnings: ld: warning multiple definitions of symbol _qsort /usr/local/lib/mysql//libmysqld.a(libmysys_mf_qsort.o) definition of _qsort in section (__TEXT,__text) /usr/lib/libSystem.dylib(qsort.So) definition of _qsort ld: warning multiple definitions of symbol _regcomp /usr/local/lib/mysql//libmysqld.a(libregex_regcomp.o) definition of _regcomp in section (__TEXT,__text) /usr/lib/libSystem.dylib(regcomp.So) definition of _regcomp I believe the files in the directory /usr/lib/ are not mysql files i don't want to delete them as well... Is there any solution for this??? I am not sure whether these warnings will cause any serious problems... 2) When I am using an Embedded Server, will i be able to connect to a normal mysql server running on a different machine by specifying the appropriate arguments in the mysql_real_connect() function? 3) I also have another question. The maximum size of a table in MySQL is determined by the type of the machine. If the size of a table goes beyond this limit, what will happen? (will MySQL take care of creating another table and dumping the excess data into that or the sytem will give an error message) I would really appreciate if anyone can help me on these questions... thanks, Babu... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.12 rpm's
This may indicate incredible ignorance but all I can find there are tar.gz files which untar into the complete distribution but no rpm's are present. Thomas Spahni wrote: On Wed, 26 Mar 2003, Paul wrote: Can anyone tell me where I can get 4.0.12 server and client rpm's? www.mysql.com Regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0 Questions...
Hi, I am using MySQL 4.0 (with Embedded Server) on a Mac OS X (Darwin) machine. I am using the C API of MySQL in my programs. I have a few questions regarding this. 1) When I compile my programs with the g++ 3.1 compiler, i get the following warnings: ld: warning multiple definitions of symbol _qsort /usr/local/lib/mysql//libmysqld.a(libmysys_mf_qsort.o) definition of _qsort in section (__TEXT,__text) /usr/lib/libSystem.dylib(qsort.So) definition of _qsort ld: warning multiple definitions of symbol _regcomp /usr/local/lib/mysql//libmysqld.a(libregex_regcomp.o) definition of _regcomp in section (__TEXT,__text) /usr/lib/libSystem.dylib(regcomp.So) definition of _regcomp I believe the files in the directory /usr/lib/ are not mysql files i don't want to delete them as well... Is there any solution for this??? I am not sure whether these warnings will cause any serious problems... 2) When I am using an Embedded Server, will i be able to connect to a normal mysql server running on a different machine by specifying the appropriate arguments in the mysql_real_connect() function? 3) I also have another question. The maximum size of a table in MySQL is determined by the type of the machine. If the size of a table goes beyond this limit, what will happen? (will MySQL take care of creating another table and dumping the excess data into that or the sytem will give an error message) I would really appreciate if anyone can help me on these questions... thanks, Babu... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql under unix in batch mode
I prefer the alternate form of this command. It's easier to read: mysql --host=localhost --user=root --password=mysql did.query did.out -ms -Original Message- From: Dobromir Velev [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 4:22 AM To: Marianadin, Didier; [EMAIL PROTECTED] Subject: Re: mysql under unix in batch mode Hi, most probably there should not be a space after -p. Try this mysql -h localhost -uroot -pmysql did.query did.out Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ - Original Message - From: Marianadin, Didier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 13:12 Subject: mysql under unix in batch mode Hi, I want to execute a simple query in batch mode in a mysql database. my OS : Unix (solaris 2.6) when I run this command : mysql -h localhost -u root -p mysql did.query did.out I'm always asked password even if I've already specified it in the previous command line (mysql) Enter password: How to avoid it ? Didier ([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: 4.0.12 rpm's
On Wed, Mar 26, 2003 at 10:30:32AM -0800, Paul wrote: This may indicate incredible ignorance but all I can find there are tar.gz files which untar into the complete distribution but no rpm's are present. The RPM downloads for 4.0.12 can be found at http://www.mysql.com/downloads/mysql-4.0.html#Linux_x86_RPM (This is the second group of downloads on the page.) Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.12 rpm's
Thank you, Jim. I clearly just didn't scroll down far enough! Jim Winstead wrote: On Wed, Mar 26, 2003 at 10:30:32AM -0800, Paul wrote: This may indicate incredible ignorance but all I can find there are tar.gz files which untar into the complete distribution but no rpm's are present. The RPM downloads for 4.0.12 can be found at http://www.mysql.com/downloads/mysql-4.0.html#Linux_x86_RPM (This is the second group of downloads on the page.) Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data entered in PHP not appearing in mySQL
When I enter data into my web page and active my PHP script. The page tells me that the data has been updated to my database, but when I check the database no data is entered. A row is created but no data is entered. Can any help?
RE: Data entered in PHP not appearing in mySQL
On 26-Mar-2003 BRYANT, LANCE wrote: When I enter data into my web page and active my PHP script. The page tells me that the data has been updated to my database, but when I check the database no data is entered. A row is created but no data is entered. Can any help? I can't see yer code in my crystal ball but the Magic eight-ball sez: 'declare your globals.' Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL and Websphere
Does anyone know if we can use MYSQL to store CMP beans in websphere? If It works, can you tell me how i can configure websphere 5 to use it ? I use the regular (MysqlDataSource) from com.mysql.jdbc.jdbc2. Within WSAD5, I have created a user-defined JDBC provider that uses com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper . The error i get is listed below (idem with mysql-connector-java-2.0.14-bin.jar ) . [26/03/03 19:04:47:435 CET] 41a1cd7 FreePool E J2CA0046E: La méthode createManagedConnctionWithMCWrapper a intercepté une exception lors de la création de ManagedConnection pour la ressource jdbc/storedb, lançant ResourceAllocationException. Exception d'origine : com.ibm.ws.exception.WsException: DSRA8101E: DataSource class cannot be used as one-phase: ClassCastException: com.mysql.jdbc.jdbc2.optional.MysqlDataSource at com.ibm.ws.rsadapter.exceptions.DataStoreAdapterException.init(DataStoreAdapterException.java:222) at com.ibm.ws.rsadapter.exceptions.DataStoreAdapterException.init(DataStoreAdapterException.java:172) Thanks _ Trouvez l'âme soeur sur MSN Rencontres ! http://g.msn.fr/FR1000/9551 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query not returning what I expect....
On 26-Mar-2003 LaBranche, Kevin wrote: The following query returns 0 for sCode instead of a string like XXX:XX. I am modifying an existing app from SQL Server 7 so that it can also run with a MySQL backend The existing query in SQL Server 7 works fine SELECT sDAMIONCode, (sORI + ':' + sDamionCode) as sCode FROM tblORI Any hints would be appreciated. MySQL uses CONCAT(sORI, ':', sDamionCode) AS sCode ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data entered in PHP not appearing in mySQL
Here is a copy of the php script that I'm using. I can create tables and query info just not add data to the database. ?php //open the connection $conn = mysql_connect(onyx,doghead,dogleg); // pick the database to use mysql_select_db(mydb,$conn); //create the SQL statement $sql = INSERT INTO testTable values ('', '$testField'); //execute the SQL statement $result = mysql_query($sql, $conn) or die(mysql_error()); //echo the result identifier echo $result; ? -Original Message- From: Don Read [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:11 PM To: BRYANT, LANCE Cc: [EMAIL PROTECTED] Subject: RE: Data entered in PHP not appearing in mySQL On 26-Mar-2003 BRYANT, LANCE wrote: When I enter data into my web page and active my PHP script. The page tells me that the data has been updated to my database, but when I check the database no data is entered. A row is created but no data is entered. Can any help? I can't see yer code in my crystal ball but the Magic eight-ball sez: 'declare your globals.' Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alias Error in a select statement
Description: Hello, When I run the following query: Select Can as MiCantidad, MiCantidad * 2 from Stocks limit 10 I get the following error: 'The column 'MiCantidad' in field list is unknown' So, Can I reference to a column by its alias in a select statement? Thanks in advance, Rafa How-To-Repeat: Select Can as MiCantidad, MiCantidad * 2 from stocks limit 10 Fix: - Synopsis:optimizer bug in the index used by mysql/Innodb in the search Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.12 Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ Try AOL and get 1045 hours FREE for 45 days! http://free.aol.com/tryaolfree/index.adp?375380 Get AOL Instant Messenger 5.1 for FREE! Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promos=380455 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query not returning what I expect....
LaBranche, Kevin wrote: The following query returns 0 for sCode instead of a string like XXX:XX. I am modifying an existing app from SQL Server 7 so that it can also run with a MySQL backend The existing query in SQL Server 7 works fine SELECT sDAMIONCode, (sORI + ':' + sDamionCode) as sCode FROM tblORI Any hints would be appreciated. MySql uses '+' only for numeric addition. For string concatenation, try the CONCAT function. Your values are being converted to integers, and become 0, for the expression you were using. Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: granting privileges using wildcards
On Wed, 26 Mar 2003 13:14:09 -0600 Black, Kelly W [PCS] wrote: I think this might do what you want, but then you will be required to log in with the -p syntax... GRANT SELECT on *.* TO yourlogin@'%' IDENTIFIED BY somepassword; FLUSH PRIVILEGES; mysql -u youruserid -p -h hostname dbasename Well, as far as I know SQL and MySQL this will grant select option to user 'yourlogin' from any host to any database and any table. What I wanted is that the user will have select privileges on on tables 'table_*' from database db. Basically, is something like the following possible? GRANT SELECT ON db.table_% TO user@'%' IDENTIFIED BY 'pass'; Thanx -- Mitko pgp0.pgp Description: PGP signature
Re: Bug Report, timestamp columns
On 26 Mar 2003 at 9:23, Alejandro Paz wrote: As you can see the colum `b' is updated, too. Note, you have to insert a delay of almost 1 second between the first select and the update, because the column `b' takes the current time!. Only happens with timestamp columns not with datetime ones. You seem to be surprised that b is updated. Have you read the documentation for TIMESTAMP? | The TIMESTAMP column type provides a type that you can use to | automatically mark INSERT or UPDATE operations with the current | date and time. If you have multiple TIMESTAMP columns, only the | first one is updated automatically. [explanation continues] http://www.mysql.com/doc/en/DATETIME.html The strange thing about your example is not the updating of b but the odd value assigned to c, which seems to be different from NOW(), but you say nothing about that. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data entered in PHP not appearing in mySQL
Do a print $testField; first. I think what might be happing is that you have register globals turned off (as you should it's a security problem). Try: $sql = INSERT INTO testTable values ('', '${_REQUEST['testField']}'); and see if that solves your problem. Serge. On Wed, 26 Mar 2003 14:28:21 -0500 BRYANT, LANCE [EMAIL PROTECTED] wrote: Here is a copy of the php script that I'm using. I can create tables and query info just not add data to the database. ?php //open the connection $conn = mysql_connect(onyx,doghead,dogleg); // pick the database to use mysql_select_db(mydb,$conn); //create the SQL statement $sql = INSERT INTO testTable values ('', '$testField'); //execute the SQL statement $result = mysql_query($sql, $conn) or die(mysql_error()); //echo the result identifier echo $result; ? -Original Message- From: Don Read [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:11 PM To: BRYANT, LANCE Cc: [EMAIL PROTECTED] Subject: RE: Data entered in PHP not appearing in mySQL On 26-Mar-2003 BRYANT, LANCE wrote: When I enter data into my web page and active my PHP script. The page tells me that the data has been updated to my database, but when I check the database no data is entered. A row is created but no data is entered. Can any help? I can't see yer code in my crystal ball but the Magic eight-ball sez: 'declare your globals.' Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- 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: Alias Error in a select statement
[EMAIL PROTECTED] writes: Description: Hello, When I run the following query: Select Can as MiCantidad, MiCantidad * 2 from Stocks limit 10 I get the following error: 'The column 'MiCantidad' in field list is unknown' So, Can I reference to a column by its alias in a select statement? Thanks in advance, Rafa How-To-Repeat: Select Can as MiCantidad, MiCantidad * 2 from stocks limit 10 Hi! This is not a bug. You can not reference an alias in the select list. You can do it freely in WHERE, ON , GROUP BY and other clauses. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data entered in PHP not appearing in mySQL
On 26-Mar-2003 BRYANT, LANCE wrote: Here is a copy of the php script that I'm using. I can create tables and query info just not add data to the database. ?php //open the connection $conn = mysql_connect(onyx,doghead,dogleg); // pick the database to use mysql_select_db(mydb,$conn); //create the SQL statement $sql = INSERT INTO testTable values ('', '$testField'); echo \n!-- Debug:\n $sql \n--; //execute the SQL statement $result = mysql_query($sql, $conn) or die(mysql_error()); //echo the result identifier echo $result; ? then View + Source. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: The table is full
Dyego, - Original Message - From: Dyego Souza do Carmo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:33 PM Subject: Error: The table is full i'm running the command: update contratante,pessoa set contratante.endereco = pessoa.endereco ,contratante.cidade = pessoa.cidade ,contratante.bairro = pessoa.bairro ,contratante.estado = pessoa.estado ,contratante.telefone = pessoa.telefone ,contratante.cep = pessoa.cep where (contratante.codpes = pessoa.codpes); The table contratante have 148867 rows The table pessoa have 119319 rows i can update the columns endereco,cidade,bairro,estado,telefone and cep of table contratante with the information of table pessoa... but I start the SQL STATEMENT and the error: The Table 'contratante' is full. Why ? i don't insert anything inside the table contratante,only update the columns with the pessoa information... an UPDATE generates undo log into the tablespace. We have to know how to roll back the UPDATE if it fails. That is why we have the undo log. ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento Regards, Heikki - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 221602060 $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sum() using group, and duplicates problems...
Thanks again Serge and Don, Don not to sound like an idiot but what would you recommend on adding a unique key. I understand the idea, but didn't want to corrupt tables trying to get it right. Serge, I knew DISTINCT operated on whole lines, I was counting on that but as you can see, I didn't know about the sum *before* the distinction... Thanks guys, I will try these and see if I can fix it. Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Serge Paquin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 10:18 AM To: [EMAIL PROTECTED] Subject: Re: sum() using group, and duplicates problems... The DISTINCT is almost usless when done on a GROUP BY clause. Also DISTINCT operates on the entire row and not just on the field. ie It will give you DISTINCT measurement, sum(val), pcfver, hour, release. And not just DISTINCT measurement results. Serge. On Wed, 26 Mar 2003 12:00:24 -0600 (CST) Don Read [EMAIL PROTECTED] wrote: On 26-Mar-2003 Black, Kelly W [PCS] wrote: Don Thanks. I will have to change them and try that. I had been bouncing back and forth between decimal and numeric but wasn't sure which was working better. I still think it might have to do with my sql query. Here's an example as you previously mentioned... SELECT DISTINCT measurement, sum(val), pcfver, hour, release FROM pcf WHERE release = curdate()-1 group by measurement, hour; The -sql runs fine, but there are hours in which I receive duplicated input data. I have filtered as much as I can, but was really needing help from the database driver to eliminate the rest. Thanks for your help. Couple of problems I see in the query: 1. curdate()-1 don't do what you think it do: mysql select curdate(), curdate()-27; ++--+ | curdate() | curdate()-27 | ++--+ | 2003-03-26 | 20030299 | ++--+ 1 row in set (0.00 sec) --use DATE_SUB(curdate(), INTERVAL 1 DAY) instead. 2. If you have duplicates that is going to hammer your results. The DISTINCT function filters the result set _after_ they're selected (and SUM'ed). If you have duplicate entries, the sum(val) will total both rows in the group --probably not what you want. A Perl script could help clear up any duplicates. Then you could add a UNIQUE key to keep 'em out. my $0.02. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- 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: DATE function question
I believe it's day without the s. Not sure if it makes a difference, but that's how it's listed in the manual. date_add([thedatefield],Interval 15 day) Mark -Original Message- From: Serge Paquin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 10:35 AM To: [EMAIL PROTECTED] Subject: Re: DATE function question I believe the following will work: date_add([thedatefield],Interval 15 days) I am almost sure I got the syntax right off the top of my head. Check mysql.com for date functions if the above doesn't work. Serge. On Wed, 26 Mar 2003 10:16:18 -0500 Tom Ray [EMAIL PROTECTED] wrote: I have a small question about the DATE function. This is what I want to do: - User enters information into form and submits it to database - When info is added to database the current date is stored ala 2003-03-26 - Now I want to take that date 2003-03-26 and tack on either 15 or 30 days on to it so the date in another column reads either 2003-04-10 or 2003-04-25 I just can't seem to figure out how to do that math right so I get the result I'm looking for. Any thoughts or ideas? 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]
full-text search versus like/instr
i probably have some misconceptions about full-text search but i was hoping that i'd be able to search for something like '*com* *tion*', i.e., find rows containing BOTH of the partial words like 'company duration', 'accompanying action', 'compositions'. yes, Virginia, full-text search is not partial-text search. except perhaps for single-word searches in boolean mode. in which case LIKE or INSTR will work just as well. or are there advantages in using boolean mode full-text instead of LIKE/INSTR for single-word partial searches? thanks, --meg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC connection from Windows app to Linux backend?
How would I connect my Windows app to a linux MySQL DB. Would I just modify the DSN I am using to point to the correct server? Thanks, Kevin -Original Message- From: LaBranche, Kevin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 12:18 PM To: '[EMAIL PROTECTED]' Subject: Query not returning what I expect The following query returns 0 for sCode instead of a string like XXX:XX. I am modifying an existing app from SQL Server 7 so that it can also run with a MySQL backend The existing query in SQL Server 7 works fine SELECT sDAMIONCode, (sORI + ':' + sDamionCode) as sCode FROM tblORI Any hints would be appreciated. Kevin
Re: Problem starting 4.0.12 server
Yes, probably. I've never used a mysql rpm, so I don't know how they're packaged. /var/lib/mysql is an odd place to stick that socket though. I've always seen it in /tmp or someplace similar (and world readable). errno = 13 -- Permission denied Make sure that directory is owned by mysql, ie $ chown mysql database dir $ chmod 700 database dir It seems rather odd that an rpm would stick anything in /ul/mysql. Did you move the stuff there and create a symbolic link to it? That won't necessarily perserve permissions and ownership. If you're having that much trouble with the rpm, a source build may or may not be what you're looking for. I'd go that route, but any of my co-workers can attest to the fact that I'm an obsessive compiler. -jeff On Wed, 2003-03-26 at 15:49, Saul wrote: From: Jeff Kilpatrick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Organization: Date: 25 Mar 2003 21:08:56 -0600 Does the mysql user have permission to write to /var/lib/mysql? -jeff Well, it didn't. But, shouldn't it have been set that way after the rpm package was installed? Now, it says: Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 030326 16:39:58 Aborting datadir is set to /var/lib/mysql which is a symbolic link to /u1/mysql. For what it's worth, I've been having a lot of problems with mysql. I had mysql 3.23 installed, and was testing a message board (phpBB2) for use with mysql. When I went to make the board production, I used mysqladmin to drop the database I had been using for testing, and then to create a new database (different name) for production. When I tried to run the phpBB2 installer against the new database, it failed. I couldn't figure out what happened; following the same installation instructions, I was getting weird error after weird error. So, I figured I would start from scratch. I figured as long as I was doing that, I would start with mysql 4... and have been having problem, after problem, after problem. I worked for over a week from the sources and got nowhere and then I saw that this release finally hit production and am starting all over again... and here I am unable to run mysql... Does the mysql user have permission to write to /var/lib/mysql? -jeff On Tue, 2003-03-25 at 20:31, [EMAIL PROTECTED] wrote: Description: Just installed the rpm files for Linux x86 downloaded from the web. After installing rpm files, server won't start. The error (from the log) is: 030325 21:23:41 mysqld started 030325 21:23:41 Can't start server : Bind on unix socket: Permission denied 030325 21:23:41 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 030325 21:23:41 Aborting 030325 21:23:41 /usr/sbin/mysqld: Shutdown Complete 030325 21:23:41 mysqld ended I've checked, and /var/lib/mysql/mysql.sock doesn't exist. Shouldn't the server create it? How-To-Repeat: Fix: Submitter-Id: submitter ID Originator: Saul Organization: Saul Jaffe [EMAIL PROTECTED] MySQL support: [none | licence | email support | extended email support ] Synopsis: Can't start server Severity: serious Priority: high Category: mysql Class:support Release: mysql-4.0.12 (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux sflovers.org 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Aug 13 2001 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Aug 13 2001 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info'
Re: granting privileges using wildcards
Is there a reason for not using tables_priv table for this purpose? Mihail - Original Message - From: Dimitar Haralanov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:29 PM Subject: Re: granting privileges using wildcards On Wed, 26 Mar 2003 13:14:09 -0600 Black, Kelly W [PCS] wrote: I think this might do what you want, but then you will be required to log in with the -p syntax... GRANT SELECT on *.* TO yourlogin@'%' IDENTIFIED BY somepassword; FLUSH PRIVILEGES; mysql -u youruserid -p -h hostname dbasename Well, as far as I know SQL and MySQL this will grant select option to user 'yourlogin' from any host to any database and any table. What I wanted is that the user will have select privileges on on tables 'table_*' from database db. Basically, is something like the following possible? GRANT SELECT ON db.table_% TO user@'%' IDENTIFIED BY 'pass'; Thanx -- Mitko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem starting 4.0.12 server
From: Jeff Kilpatrick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Organization: Date: 25 Mar 2003 21:08:56 -0600 Does the mysql user have permission to write to /var/lib/mysql? -jeff Well, it didn't. But, shouldn't it have been set that way after the rpm package was installed? Now, it says: Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 030326 16:39:58 Aborting datadir is set to /var/lib/mysql which is a symbolic link to /u1/mysql. For what it's worth, I've been having a lot of problems with mysql. I had mysql 3.23 installed, and was testing a message board (phpBB2) for use with mysql. When I went to make the board production, I used mysqladmin to drop the database I had been using for testing, and then to create a new database (different name) for production. When I tried to run the phpBB2 installer against the new database, it failed. I couldn't figure out what happened; following the same installation instructions, I was getting weird error after weird error. So, I figured I would start from scratch. I figured as long as I was doing that, I would start with mysql 4... and have been having problem, after problem, after problem. I worked for over a week from the sources and got nowhere and then I saw that this release finally hit production and am starting all over again... and here I am unable to run mysql... Does the mysql user have permission to write to /var/lib/mysql? -jeff On Tue, 2003-03-25 at 20:31, [EMAIL PROTECTED] wrote: Description: Just installed the rpm files for Linux x86 downloaded from the web. After installing rpm files, server won't start. The error (from the log) is: 030325 21:23:41 mysqld started 030325 21:23:41 Can't start server : Bind on unix socket: Permission denied 030325 21:23:41 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 030325 21:23:41 Aborting 030325 21:23:41 /usr/sbin/mysqld: Shutdown Complete 030325 21:23:41 mysqld ended I've checked, and /var/lib/mysql/mysql.sock doesn't exist. Shouldn't the server create it? How-To-Repeat: Fix: Submitter-Id: submitter ID Originator: Saul Organization: Saul Jaffe [EMAIL PROTECTED] MySQL support: [none | licence | email support | extended email support ] Synopsis: Can't start server Severity: serious Priority: high Category: mysql Class: support Release:mysql-4.0.12 (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux sflovers.org 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Aug 13 2001 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Aug 13 2001 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' -- Saul Jaffe Mouch Potato [EMAIL PROTECTED] To a touch typist, having the space bar workonly 30% of the time, is damned frustrating. So, insert spaces as needed to make this message make sense. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE function question
Yup I think that's write. I had a feeling I had something small wrong :) On Wed, 26 Mar 2003 13:50:12 -0500 Mark Armendariz [EMAIL PROTECTED] wrote: I believe it's day without the s. Not sure if it makes a difference, but that's how it's listed in the manual. date_add([thedatefield],Interval 15 day) Mark -Original Message- From: Serge Paquin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 10:35 AM To: [EMAIL PROTECTED] Subject: Re: DATE function question I believe the following will work: date_add([thedatefield],Interval 15 days) I am almost sure I got the syntax right off the top of my head. Check mysql.com for date functions if the above doesn't work. Serge. On Wed, 26 Mar 2003 10:16:18 -0500 Tom Ray [EMAIL PROTECTED] wrote: I have a small question about the DATE function. This is what I want to do: - User enters information into form and submits it to database - When info is added to database the current date is stored ala 2003-03-26 - Now I want to take that date 2003-03-26 and tack on either 15 or 30 days on to it so the date in another column reads either 2003-04-10 or 2003-04-25 I just can't seem to figure out how to do that math right so I get the result I'm looking for. Any thoughts or ideas? 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem starting 4.0.12 server
From: Jeff Kilpatrick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Organization: Date: 26 Mar 2003 16:03:38 -0600 Yes, probably. I've never used a mysql rpm, so I don't know how they're packaged. /var/lib/mysql is an odd place to stick that socket though. I've always seen it in /tmp or someplace similar (and world readable). That appears to be the default location. errno = 13 -- Permission denied Make sure that directory is owned by mysql, ie $ chown mysql database dir $ chmod 700 database dir Actually, it was 755... and owned by mysql, group mysql. It seems rather odd that an rpm would stick anything in /ul/mysql. Did you move the stuff there and create a symbolic link to it? That won't necessarily perserve permissions and ownership. I had created /u1/mysql and put a link to it when I installed 3.23. As I said, I had tested with that and everything worked fine. All I did was, after blowing away the databases, and uninstalling 3.23, was to install 4.0.12. I started with the sources and had problems. When the rpms came out, I thought the prebuilt versions would solve my problem as the prebuilt version worked before. If you're having that much trouble with the rpm, a source build may or may not be what you're looking for. I'd go that route, but any of my co-workers can attest to the fact that I'm an obsessive compiler. Well, I could... except that the documentation, as far as I'm concerned, leaves a lot to be desired. -jeff On Wed, 2003-03-26 at 15:49, Saul wrote: From: Jeff Kilpatrick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Organization: Date: 25 Mar 2003 21:08:56 -0600 Does the mysql user have permission to write to /var/lib/mysql? -jeff Well, it didn't. But, shouldn't it have been set that way after the rpm package was installed? Now, it says: Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 030326 16:39:58 Aborting datadir is set to /var/lib/mysql which is a symbolic link to /u1/mysql. For what it's worth, I've been having a lot of problems with mysql. I had mysql 3.23 installed, and was testing a message board (phpBB2) for use with mysql. When I went to make the board production, I used mysqladmin to drop the database I had been using for testing, and then to create a new database (different name) for production. When I tried to run the phpBB2 installer against the new database, it failed. I couldn't figure out what happened; following the same installation instructions, I was getting weird error after weird error. So, I figured I would start from scratch. I figured as long as I was doing that, I would start with mysql 4... and have been having problem, after problem, after problem. I worked for over a week from the sources and got nowhere and then I saw that this release finally hit production and am starting all over again... and here I am unable to run mysql... Does the mysql user have permission to write to /var/lib/mysql? -jeff On Tue, 2003-03-25 at 20:31, [EMAIL PROTECTED] wrote: Description: Just installed the rpm files for Linux x86 downloaded from the web. After installing rpm files, server won't start. The error (from the log) is: 030325 21:23:41 mysqld started 030325 21:23:41 Can't start server : Bind on unix socket: Permission denied 030325 21:23:41 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 030325 21:23:41 Aborting 030325 21:23:41 /usr/sbin/mysqld: Shutdown Complete 030325 21:23:41 mysqld ended I've checked, and /var/lib/mysql/mysql.sock doesn't exist. Shouldn't the server create it? How-To-Repeat: Fix: Submitter-Id:submitter ID Originator: Saul Organization: Saul Jaffe [EMAIL PROTECTED] MySQL support: [none | licence | email support | extended email support ] Synopsis:Can't start server Severity:serious Priority:high Category:mysql Class: support Release: mysql-4.0.12 (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux sflovers.org 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='-O6
Re: granting privileges using wildcards
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dimitar -- ...and then Dimitar Haralanov said... % ... % Basically, is something like the following possible? % % GRANT SELECT ON db.table_% TO user@'%' IDENTIFIED BY 'pass'; AFAIK it is not. See Benjamin's quite thorough response to my question in the thread problems with GRANT, user, databases. % % Thanx % -- Mitko HTH HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+giV0Gb7uCXufRwARAoGeAJ42Dozq0c7MtIO8T2eOzIq/ddYIugCcDvDC 8D8iyUCXr+PvK94mjthAZMA= =4XH/ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: granting privileges using wildcards
I think this might do what you want, but then you will be required to log in with the -p syntax... GRANT SELECT on *.* TO yourlogin@'%' IDENTIFIED BY somepassword; FLUSH PRIVILEGES; mysql -u youruserid -p -h hostname dbasename Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Dimitar Haralanov [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 11:03 AM To: [EMAIL PROTECTED] Subject: granting privileges using wildcards Hi, I have been trying to find information on the following question but have been unable to do so: Is it possible to grant options to a user using wildcards. For example: let's say that I have a database named 'db', and a user 'admin' who has full privileges. The user 'admin' will add and delete tables to 'db' but any table that is added/deleted will have the following name format 'table_[A-z0-9]'. What I would like to be able to do is grant select privileges to any and all of the tables 'table_*' to a user 'non_admin'. Is this possible and if it is, how can I do that? Thank you for your help! -- Mitko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alias Error in a select statement
[EMAIL PROTECTED] writes: Description: Hello, When I run the following query: Select Can as MiCantidad, MiCantidad * 2 from Stocks limit 10 I get the following error: 'The column 'MiCantidad' in field list is unknown' So, Can I reference to a column by its alias in a select statement? Thanks in advance, Rafa How-To-Repeat: Select Can as MiCantidad, MiCantidad * 2 from stocks limit 10 Hi! This is not a bug. You can not reference an alias in the select list. You can do it freely in WHERE, ON , GROUP BY and other clauses. Not quite... Not in a WHERE clause. Well, table aliases, yes; column aliases, no. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb_thread_concurrency and hyperthreading
So I'm setting up a fancy new machine with Xeons doing hyperthreading. What this means is that there are 2 physical processors, but as far as linux is concerned, there are 4. Does anybody know whether thread_concurrency should be 2*(Number of Physical Processors) or 2*(Number of Virtual Processors). I'm leaning towards the virtual (which comes out to 8) but I am open to suggestions. mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Beginner question - Preventing Duplicate Entries
Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I dont know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
beginners question - Not Makine Duplicate Entrys
Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I dont know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: granting privileges using wildcards
On Wed, 26 Mar 2003 14:37:33 -0800 Jennifer Goodie wrote: To answer the original question, I have tried a lot of different ways, but the only solution I have found is granting on the entire database or specifing each table in the tables_priv table. I go with the second option because although it is more work I like only allowing as much access as needed. The wild cards just don't seem to work in this instance. Has anybody had any success with it? This solution works, of course, but has one unwanted pre-requisite. I have to be the root MySQL user (or a user that has privileges to modify that table) to modify table_priv. The code that I am working on will be running on a Apache server and I do not want to have the web server login to MySQL using a user with that high privileges. It is a security issue!! :) I guess, I will have to work arround this!! Thanx -- Mitko pgp0.pgp Description: PGP signature
Re: Problem starting 4.0.12 server
From: Jeff Kilbride [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Date: Wed, 26 Mar 2003 15:44:49 -0800 Make sure that all the files inside your database directories are owned by mysql: chown -R mysql. database dir The dot at the end of mysql above sets the group to mysql also. The -R means set the permissions recursively. Each time I install a binary distribution and run the scripts/mysql_install_db script, I have to do this or I get the error you've gotten. The mysql daemon needs to be able to read and write these files. Been a unix person for years. Know this... didn't help. Deleted the link. Recreated the link. Then it worked. Don't know why. Now, I just get cannot connect to database anytime trying to connect from inside php... may have to rebuild php... Already rebuilt the Perl modules... --jeff - Original Message - From: Saul [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:10 PM Subject: Re: Problem starting 4.0.12 server From: Jeff Kilpatrick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Organization: Date: 26 Mar 2003 16:03:38 -0600 Yes, probably. I've never used a mysql rpm, so I don't know how they're packaged. /var/lib/mysql is an odd place to stick that socket though. I've always seen it in /tmp or someplace similar (and world readable). That appears to be the default location. errno = 13 -- Permission denied Make sure that directory is owned by mysql, ie $ chown mysql database dir $ chmod 700 database dir Actually, it was 755... and owned by mysql, group mysql. It seems rather odd that an rpm would stick anything in /ul/mysql. Did you move the stuff there and create a symbolic link to it? That won't necessarily perserve permissions and ownership. I had created /u1/mysql and put a link to it when I installed 3.23. As I said, I had tested with that and everything worked fine. All I did was, after blowing away the databases, and uninstalling 3.23, was to install 4.0.12. I started with the sources and had problems. When the rpms came out, I thought the prebuilt versions would solve my problem as the prebuilt version worked before. If you're having that much trouble with the rpm, a source build may or may not be what you're looking for. I'd go that route, but any of my co-workers can attest to the fact that I'm an obsessive compiler. Well, I could... except that the documentation, as far as I'm concerned, leaves a lot to be desired. -jeff On Wed, 2003-03-26 at 15:49, Saul wrote: From: Jeff Kilpatrick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Organization: Date: 25 Mar 2003 21:08:56 -0600 Does the mysql user have permission to write to /var/lib/mysql? -jeff Well, it didn't. But, shouldn't it have been set that way after the rpm package was installed? Now, it says: Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 030326 16:39:58 Aborting datadir is set to /var/lib/mysql which is a symbolic link to /u1/mysql. For what it's worth, I've been having a lot of problems with mysql. I had mysql 3.23 installed, and was testing a message board (phpBB2) for use with mysql. When I went to make the board production, I used mysqladmin to drop the database I had been using for testing, and then to create a new database (different name) for production. When I tried to run the phpBB2 installer against the new database, it failed. I couldn't figure out what happened; following the same installation instructions, I was getting weird error after weird error. So, I figured I would start from scratch. I figured as long as I was doing that, I would start with mysql 4... and have been having problem, after problem, after problem. I worked for over a week from the sources and got nowhere and then I saw that this release finally hit production and am starting all over again... and here I am unable to run mysql... Does the mysql user have permission to write to /var/lib/mysql? -jeff On Tue, 2003-03-25 at 20:31, [EMAIL PROTECTED] wrote: Description: Just installed the rpm files for Linux x86 downloaded from the web. After installing rpm
RE: beginners question - Not Makine Duplicate Entrys
Set a primary key / unique key on a column (s) in myTable to make that row unique. Goto mysql.com and type primary key in the search dialog. There is a wealth of knowledge there. Or use google site:mysql.com Primary Key syntax -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 3:35 PM To: 'mysql users' Subject: beginners question - Not Makine Duplicate Entrys Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- 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: Problem starting 4.0.12 server
Make sure that all the files inside your database directories are owned by mysql: chown -R mysql. database dir The dot at the end of mysql above sets the group to mysql also. The -R means set the permissions recursively. Each time I install a binary distribution and run the scripts/mysql_install_db script, I have to do this or I get the error you've gotten. The mysql daemon needs to be able to read and write these files. --jeff - Original Message - From: Saul [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:10 PM Subject: Re: Problem starting 4.0.12 server From: Jeff Kilpatrick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Organization: Date: 26 Mar 2003 16:03:38 -0600 Yes, probably. I've never used a mysql rpm, so I don't know how they're packaged. /var/lib/mysql is an odd place to stick that socket though. I've always seen it in /tmp or someplace similar (and world readable). That appears to be the default location. errno = 13 -- Permission denied Make sure that directory is owned by mysql, ie $ chown mysql database dir $ chmod 700 database dir Actually, it was 755... and owned by mysql, group mysql. It seems rather odd that an rpm would stick anything in /ul/mysql. Did you move the stuff there and create a symbolic link to it? That won't necessarily perserve permissions and ownership. I had created /u1/mysql and put a link to it when I installed 3.23. As I said, I had tested with that and everything worked fine. All I did was, after blowing away the databases, and uninstalling 3.23, was to install 4.0.12. I started with the sources and had problems. When the rpms came out, I thought the prebuilt versions would solve my problem as the prebuilt version worked before. If you're having that much trouble with the rpm, a source build may or may not be what you're looking for. I'd go that route, but any of my co-workers can attest to the fact that I'm an obsessive compiler. Well, I could... except that the documentation, as far as I'm concerned, leaves a lot to be desired. -jeff On Wed, 2003-03-26 at 15:49, Saul wrote: From: Jeff Kilpatrick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Organization: Date: 25 Mar 2003 21:08:56 -0600 Does the mysql user have permission to write to /var/lib/mysql? -jeff Well, it didn't. But, shouldn't it have been set that way after the rpm package was installed? Now, it says: Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 030326 16:39:58 Aborting datadir is set to /var/lib/mysql which is a symbolic link to /u1/mysql. For what it's worth, I've been having a lot of problems with mysql. I had mysql 3.23 installed, and was testing a message board (phpBB2) for use with mysql. When I went to make the board production, I used mysqladmin to drop the database I had been using for testing, and then to create a new database (different name) for production. When I tried to run the phpBB2 installer against the new database, it failed. I couldn't figure out what happened; following the same installation instructions, I was getting weird error after weird error. So, I figured I would start from scratch. I figured as long as I was doing that, I would start with mysql 4... and have been having problem, after problem, after problem. I worked for over a week from the sources and got nowhere and then I saw that this release finally hit production and am starting all over again... and here I am unable to run mysql... Does the mysql user have permission to write to /var/lib/mysql? -jeff On Tue, 2003-03-25 at 20:31, [EMAIL PROTECTED] wrote: Description: Just installed the rpm files for Linux x86 downloaded from the web. After installing rpm files, server won't start. The error (from the log) is: 030325 21:23:41 mysqld started 030325 21:23:41 Can't start server : Bind on unix socket: Permission denied 030325 21:23:41 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 030325 21:23:41 Aborting 030325 21:23:41 /usr/sbin/mysqld: Shutdown Complete 030325 21:23:41 mysqld ended I've checked, and /var/lib/mysql/mysql.sock doesn't exist. Shouldn't the server create it? How-To-Repeat: Fix: Submitter-Id: submitter ID Originator: Saul Organization: Saul Jaffe [EMAIL PROTECTED] MySQL support: [none | licence | email support | extended email support ] Synopsis: Can't start server Severity:
RE: Beginner question - Preventing Duplicate Entries
If you wanted to use MyISAM tables and peform an initial select to determine whether you should insert, you could lock the table you would be selecting/insert from. 'LOCK TABLES table_name WRITE' Don't forget to unlock the table when you are done 'UNLOCK TABLES', otherwise you will likely have deadlocks. ~Wynne -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:04 PM To: 'mysql users' Subject: Beginner question - Preventing Duplicate Entries Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- 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: Beginner question - Preventing Duplicate Entries
Add a UNIQUE INDEX to the table you are inserting to. You will then get an error if you try to insert a second time with the same values. Alternativly you could use perform a select and then an insert within the same transaction, determining whether something exists in the db before inserting. This of course would require transactions supported by InnoDB tables and not supported by MyISAM. ~Wynne -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:04 PM To: 'mysql users' Subject: Beginner question - Preventing Duplicate Entries Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- 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]
Foreign key ... ON UPDATE CASEADE
Hello I have been use mysql ver 3.23.53, also I'm using on DELETE CASECADE. It seems working fine. I believe UPDATE CASECADE is working from version 4.0.8. - I have following problem (purchaseOrder_items::orderNum reference purchaseOrder_items; ::orderNum) select * from purchaseOrder; +--+-++---++ | orderNum | orderSupplierID | orderDate | orderNote | orderTotal | +--+-++---++ | 123 | k1 | 2003-03-26 | testing |250 | +--+-++---++ 1 row in set (0.00 sec) mysql select * from purchaseOrder_items; +--++---+--+---+ | orderNum | itemID | itemList | itemCost | itemDelivDate | +--++---+--+---+ | 123 | book1 | equipment | 100 | 2003-03-26| | 123 | book2 | equp | 150 | 2003-03-26| +--++---+--+---+ 2 rows in set (0.00 sec) mysql update purchaseOrder_items set orderNum=567 where orderNum=123; ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql mysql mysql update purchaseOrder set orderNum=567 where orderNum=123; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails -- I can NOT edit the orderNum. It seems the only way to edit the orderNum is, delete the children (purchaseOrder_items) or move to a tem table, then edit the parent orderNum and add back the children with the new orderNum, This is to many transactions. Is there any better way to edit foreign key values Of course I can switch to the a upper mysql version(4.0.8), if that is the only solution, which one is recommendable?? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: granting privileges using wildcards
That would grant the user select on everything in every database no matter what host they are coming from. He wants to only grant on specific tables, and did not mention anything about allowing from all hosts. To answer the original question, I have tried a lot of different ways, but the only solution I have found is granting on the entire database or specifing each table in the tables_priv table. I go with the second option because although it is more work I like only allowing as much access as needed. The wild cards just don't seem to work in this instance. Has anybody had any success with it? -Original Message- From: Black, Kelly W [PCS] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 11:14 AM To: 'Dimitar Haralanov'; [EMAIL PROTECTED] Subject: RE: granting privileges using wildcards I think this might do what you want, but then you will be required to log in with the -p syntax... GRANT SELECT on *.* TO yourlogin@'%' IDENTIFIED BY somepassword; FLUSH PRIVILEGES; mysql -u youruserid -p -h hostname dbasename Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Dimitar Haralanov [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 11:03 AM To: [EMAIL PROTECTED] Subject: granting privileges using wildcards Hi, I have been trying to find information on the following question but have been unable to do so: Is it possible to grant options to a user using wildcards. For example: let's say that I have a database named 'db', and a user 'admin' who has full privileges. The user 'admin' will add and delete tables to 'db' but any table that is added/deleted will have the following name format 'table_[A-z0-9]'. What I would like to be able to do is grant select privileges to any and all of the tables 'table_*' to a user 'non_admin'. Is this possible and if it is, how can I do that? Thank you for your help! -- Mitko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Request on source
To Creators- In my attempt to obtain 1 working example of this DB: I am compiling all of the code. I have a request for Monty with the creators in theit function declarations: e.g. int strcasecmp(s1, s2) const char *s1, *s2; please enclose the const char *s1, *s2 declaration within the argument and please keep on same line as int strcasecmp(const char *s1,const char *s2) as it stands now if another source translation unit references strcasecmp there will be An unresolved external reference Tack! Martin - Original Message - From: Wynne Crisman [EMAIL PROTECTED] To: 'Wileynet' [EMAIL PROTECTED]; 'mysql users' [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 4:47 PM Subject: RE: Beginner question - Preventing Duplicate Entries If you wanted to use MyISAM tables and peform an initial select to determine whether you should insert, you could lock the table you would be selecting/insert from. 'LOCK TABLES table_name WRITE' Don't forget to unlock the table when you are done 'UNLOCK TABLES', otherwise you will likely have deadlocks. ~Wynne -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:04 PM To: 'mysql users' Subject: Beginner question - Preventing Duplicate Entries Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- 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: MySQL Daemon wont start
If you had installed RedHat's RPM, then you should be able to start MySQL with /etc/rc.d/init.d/mysql start Regards Richard KHOO Guan Chen On Wed, 26 Mar 2003, Toto Gamez wrote: i installed mysql-3.23.41-1 on my RH7.2 box but when i run safe_mysqld, mysql daemon started but dies i tried to check the log and says: 030326 14:35:46 mysqld started 030326 14:35:47 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 030326 14:35:47 mysqld ended how do I correct this -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL Database
i got this mesage when creating a database for twig, I just followed the instruction in twig manual on how to create a database but resulted to this [EMAIL PROTECTED] mysql]# mysql -u root mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 3.23.41 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE DATABASE maildb; Query OK, 1 row affected (0.00 sec) mysql GRANT ALL PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02; ERROR 1064: You have an error in your SQL syntax near 'PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02' at line 1
Transaction State handling in Mysql.
Hi all, I want to execute a set of Insert,Update and Delete if any one of the statement fails then I need to rollback all the statements. How do we achieve the same in MySQL. Thanks in advance. Ramanan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Controlling replication of GRANT/REVOKE queries?
My replication was going fine until the slave attempted to execute a REVOKE query referring to a user that didn't exist on the slave. That caused an error and brought replication to a halt. I'm using replicate-wild-do-table to set the databases I want to replicate. If I'm not replicating the mysql database, it seems that the slave shouldn't be attempting to execute GRANT and REVOKE queries it receives from the master. Is there no way to prevent this? I'm using version 4.0.10. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Controlling replication of GRANT/REVOKE queries?
On Wed, Mar 26, 2003 at 06:06:02PM -0500, Keith C. Ivey wrote: My replication was going fine until the slave attempted to execute a REVOKE query referring to a user that didn't exist on the slave. That caused an error and brought replication to a halt. I'm using replicate-wild-do-table to set the databases I want to replicate. If I'm not replicating the mysql database, it seems that the slave shouldn't be attempting to execute GRANT and REVOKE queries it receives from the master. Is there no way to prevent this? Nope. It's a known bug. http://bugs.mysql.com/bug.php?id=180 Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 51 days, processed 1,774,522,580 queries (396/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transaction State handling in Mysql.
Hello Ramanan- First off, make sure you create tables of type InnoDB, Berkeley, or Gemini. [My]ISAM tables don't do transactions. The simplest way to do a transaction is mysql BEGIN; mysql # successful edit mysql COMMIT; or mysql BEGIN; mysql # failed edit, or dropped connection implies mysql ROLLBACK; To create non-isam tables, append TYPE = InnoDB or similar to create statements. You can convert current tables using ALTER TABLE, but each type must be compiled in and setup in my.cnf One caveat: be sure all the tables affected are of one of the above types or you won't get a full rollback, leaving you in a crazy state with some badly updated tables (right guys? I think I read that in the docs a couple years ago). -jeff On Wed, 2003-03-26 at 22:33, [EMAIL PROTECTED] wrote: Hi all, I want to execute a set of Insert,Update and Delete if any one of the statement fails then I need to rollback all the statements. How do we achieve the same in MySQL. Thanks in advance. Ramanan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Database - followup
Your spelling of PRIVILEGES is also spelled wrong =) specifying GRANT ALL PRIVILEGES on *.* to a user will make that user a member of the superuser group such as mysql, just without the GRANT OPTIONS, meaning that user's login wouldn't be able to GRANT other users. if you want to specify the database just do a mydb.* meaning all tables within the mydb database. then, mysqlflush privileges; i hope that helps. Respectfully yours, Sherwin T. Ang Systems Administrator Tridel Technologies Incorporated 7F Hanston Building Emerald Avenue, Ortigas Center Pasig City 1605 Philippines Phone: 6345140 Local 1024 Web: http://www.tridel.net Email: [EMAIL PROTECTED] - Original Message - From: Toto Gamez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 2:11 PM Subject: MYSQL Database i got this mesage when creating a database for twig, I just followed the instruction in twig manual on how to create a database but resulted to this [EMAIL PROTECTED] mysql]# mysql -u root mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 3.23.41 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE DATABASE maildb; Query OK, 1 row affected (0.00 sec) mysql GRANT ALL PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02; ERROR 1064: You have an error in your SQL syntax near 'PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Database
Do it like this: mysqlGRANT ALL PRIVELEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'mikaela02'; You forgot the hostname which is the @hostname is for Respectfully yours, Sherwin T. Ang Systems Administrator Tridel Technologies Incorporated 7F Hanston Building Emerald Avenue, Ortigas Center Pasig City 1605 Philippines Phone: 6345140 Local 1024 Web: http://www.tridel.net Email: [EMAIL PROTECTED] - Original Message - From: Toto Gamez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 2:11 PM Subject: MYSQL Database i got this mesage when creating a database for twig, I just followed the instruction in twig manual on how to create a database but resulted to this [EMAIL PROTECTED] mysql]# mysql -u root mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 3.23.41 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE DATABASE maildb; Query OK, 1 row affected (0.00 sec) mysql GRANT ALL PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02; ERROR 1064: You have an error in your SQL syntax near 'PRIVELEGES ON *.* TO toto IDENTIFIED BY mikaela02' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transaction Support with MyISAM
Dear Friends, The last help I got was truly worth a zillion. 1. How can i have commit/rollback functions using MyIsam database which is default database engine for MySql? i am using vb 2. is there any intelligent program (not ODBC/JDBC drivers anyway) available that can be run on a server provide a bridge between Myql client? it becomes necessary to have some stored procedure support using these tools. -Thanks -Nitin __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database sychronize statistic
Hi all, Do you have any information on the time and CPU resources required during the full sync / incremental sync between the master and slave mysql server? Thank you. Best regards, Trevor Luo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help, Insert not working.
Can anyone explain why this isn't working? It will not input the info into the database. $db = mysql_connect($serverhost, $serveruser, $serverpass); mysql_select_db($database_name); The variables in the above two rows are correct - I can do selects and it works fine. This is the data to be inserted and they are there echo BR.$entered_email; echo BR.$entered_passwd; echo BR.$name; Nothing happens. mysql_query (INSERT INTO userdata (email, passwd, firstname) VALUES ('$entered_email','$entered_passwd','$name')); TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Help, Insert not working.
I got it - stupid me has a unique field that I wasn't using in the test script I was using - so every I tried to add an entry this field was a duplicate and as such did not add the record. Is there a way to put something in so mysql would tell me this - 2 hours pissing around for an oversight. TIA - Original Message - From: C. Reeve [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 1:47 AM Subject: Help, Insert not working. Can anyone explain why this isn't working? It will not input the info into the database. $db = mysql_connect($serverhost, $serveruser, $serverpass); mysql_select_db($database_name); The variables in the above two rows are correct - I can do selects and it works fine. This is the data to be inserted and they are there echo BR.$entered_email; echo BR.$entered_passwd; echo BR.$name; Nothing happens. mysql_query (INSERT INTO userdata (email, passwd, firstname, alias) VALUES ('$entered_email','$entered_passwd','$name','Hello')); TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Passing data between the web to an Intranet
Steve, Hopefully I am correctly interpreting what you want to do. While I don't have anything of this kind in operation, I did come close to implementing it as a solution to a problem that I eventually found another way around. My proposed solution was to have a script set up on the web server that, when browsed to, would output a list of records from a MySQL database. This would be a 'hidden' script not linked to by any other pages so that a casual browser of the site would not accidentally come across it. A script on the internal intranet could then be cron'd to browse to that page, slurp up the output of the remote page and store it in MySQL locally. The local script could be triggered manually each time an update is done, or there could be, perhaps, a database entry on the remote server that is set to '1' whenever data is changed. The '0'/'1' is output first and the local script could then test that first and either proceed with data capture or break the connection if nothing has changed. Hope this is useful! Peter Monk IT Department United Water International Adelaide, Australia -Original Message- From: Steve Jackson [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 March 2003 5:59 PM To: MySQL General Mailing list Cc: PHP General Subject: Passing data between the web to an Intranet Has anyone ever simply tried to pass information direct from the web into a secure Intranet mysql DB rather than to a web DB? I am thinking down one of three lines. 1) I use PHP to send an email with the data in it and update our internal DB manually (the worst case) 2) I use PHP which takes data, sends it as an email, puts it into a form which then automatically updates our internal network (interesting idea but I have no clue if it can be done ;o) 3) I use some sort of synchronisation so every time the web DB is updated our internal one is also updated (ideal). I would welcome any other ideas or solutions. Does mysql have any replication or synchronisation functions? I am told Oracle has but haven't seen this or heard of this with mysql. Cheers, Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 -- 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]