Update multiple tables strange behaviour
Hi all, I'm facing a strange behaviour with an UPDATE statement. I have a table like: +-+--+ | item_id | rank | +-+--+ |2812 |2 | | 13050 |4 | | 13051 |3 | | 13052 |1 | +-+--+ And I want to switch items 2812 and 13052 rank (i.e. assign rank 2 to 13052 and assign rank 1 to 2812). I tried 2 update queries and only one works as expected even if the 2 queries looks very close: * First query: UPDATE item i1, item i2 SET i2.rank = i1.rank, i1.rank = 1 WHERE i1.item_id = 2812 AND i2.item_id = 13052; Query OK, 1 row affected (0.00 sec) Rows matched: 2 Changed: 1 Warnings: 0 +-+--+ | item_id | rank | +-+--+ |2812 |1 | | 13050 |4 | | 13051 |3 | | 13052 |1 | +-+--+ - Doesn't work: change only one row. * Second query UPDATE item i1, item i2 SET i1.rank = i2.rank, i2.rank = 2 WHERE i1.item_id = 13052 AND i2.item_id = 2812; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 +-+--+ | item_id | rank | +-+--+ |2812 |2 | | 13050 |4 | | 13051 |3 | | 13052 |1 | +-+--+ - It works. Can someone explain to me what happen ? Cheers, Manuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: incompatible libmysqld.a
Thank you for your answers. gcc (GCC) 4.1.1 20060724 (prerelease) (4.1.1-3mdk) mysql Ver 14.12 Distrib 5.0.24a, for mandriva-linux-gnu (i586) using readline 5.1 GNU C Library development release version 2.4, I think MySql 5.0 requires glibc 2.3. I do not know if v2.4 is a problem. This computer is a laptop Acer Aspire 5633WLMi, Intel Core Duo processor. Thank you again. 2007/2/15, Colin Charles [EMAIL PROTECTED]: Carlos Soria wrote: Hello, I am working with MySQL 5.0.24 on Mandriva-Linux 2007. I can't compile book example test1_libmysqld due to an incompatibility gcc -g -W -Wall -I/usr/include/mysql -D_THREAD_SAFE -D_REENTRANT -static test1_libmysqld.c -L/usr/lib/mysql -lmysqld -lz -lm -lcrypt -lpthread -o test1_libmysqld /usr/bin/ld: skipping incompatible /usr/lib/mysql/libmysqld.a when searching for -lmysqld /usr/bin/ld: cannot find -lmysqld collect2: ld returned 1 exit status make: *** [test1_libmysqld] Error 1 What is libmysqld incompatible with? Whats your configure options? Please provide more details. Also, what version of gcc is this? thanks -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/
Re: InnoDB fixed file size, how much is left?
For what you described... you will not get a fixed size... If you have set file_per_table flag in my.cnf you might want to know that the .ibd files in the database directory are by default auto-extending... so those files WILL grow... along with your data... The shared tablespaces that you talked about (10 * 100MB) are still used by InnoDB for transactions and foreign keys reference (at least these two things)... Although I don't get why would you use 10 files of 100MB... why not 20 of 50MB... unless they are on different disks and partitions... I don't understand... I would personally go with at most 2 files arround 500MB... keeping in mind that you have file_per_table on !!! How big transactions are you expecting... how many clients are you expecting ? Answers to these questions can help you tweak the server... -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I detect if MySql is already installed?
Linux : $ rpm -qa |grep -i mysql Regards, Juan On 2/14/07, Zsolt Csillag [EMAIL PROTECTED] wrote: Hello, I would like to create a setup program for my demo version that uses MySql database. I need to detect if the target computer has already got the MySql installed because I don't want to ruin an existing database. Is there any method to detect it? Thank you in advance -- Zsolt Csillag
Re: How can I detect if MySql is already installed?
Hi Zsolt if there is no firewall in between, you may check to see if port 3306 is responding. suomi Zsolt Csillag wrote: Hello, I would like to create a setup program for my demo version that uses MySql database. I need to detect if the target computer has already got the MySql installed because I don't want to ruin an existing database. Is there any method to detect it? Thank you in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I detect if MySql is already installed?
On Thursday 15 February 2007 11:12:11 Juan Eduardo Moreno wrote: Linux : $ rpm -qa |grep -i mysql Regards, Juan On 2/14/07, Zsolt Csillag [EMAIL PROTECTED] wrote: Hello, I would like to create a setup program for my demo version that uses MySql database. I need to detect if the target computer has already got the MySql installed because I don't want to ruin an existing database. That only works for the limited case of * System supports RPM * Admin installed MySQL as an RPM Without knowing what platform you intend your demo to run on, giving an accurate answer will be hard. Furthermore, it's very possible to have multiple installs of MySQL on a server without conflict, so done correctly, you won't trash any other MySQL installs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL_CALC_FOUND_ROWS using ODBC driver
Hi, Nuno Oliveira wrote: When I set the RS.Source to the first SELECT statement and open it, it run OK but I need to close the RS and open it again using the second SELECT statement. After any of this operations I get a Recordset-RecordsCount = 1 That is actually a correct figure, SELECT FOUND_ROWS() returns exactly one record *CONTAINING* the number of found rows. So you have to access the resultset to get the actual number of rows. SELECT SQL_CALC_FOUND_ROWS doesn't make much sense without a where clause by the way. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB fixed file size, how much is left?
For what you described... you will not get a fixed size... If you have set file_per_table flag in my.cnf you might want to know that the .ibd files in the database directory are by default auto-extending... so those files WILL grow... along with your data... The shared tablespaces that you talked about (10 * 100MB) are still used by InnoDB for transactions and foreign keys reference (at least these two things)... Although I don't get why would you use 10 files of 100MB... why not 20 of 50MB... unless they are on different disks and partitions... I don't understand... I would personally go with at most 2 files arround 500MB... keeping in mind that you have file_per_table on !!! How big transactions are you expecting... how many clients are you expecting ? Answers to these questions can help you tweak the server... For the shared tables I'm just playing around with the ideology. In prod I would like to have a couple GB of transactional space. The transactions will be small but there will probably be 200+/s. I expect that the transaction logs will be on a separate disk instance (probably a raid 5 disk) in production. I had realized after the fact that I had use the per_table. When I figured that out I was able to see the sizes of course. In dev we're running about 1000/s (or at leasts that's the max insert rate on the xen instances we have -- not a really fast machine). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL_CALC_FOUND_ROWS using ODBC driver
Nils Meyer wrote: SELECT SQL_CALC_FOUND_ROWS doesn't make much sense without a where clause by the way. LIMIT, not where. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL_CALC_FOUND_ROWS using ODBC driver
On Thu, Feb 15, 2007 at 01:04:26AM +, Nuno Oliveira wrote: Hi, I've used PHP with MySQL and I was able to count the rows of a table by using the following code: SELECT SQL_CALC_FOUND_ROWS * FROM MyTableName; SELECT FOUND_ROWS(); However, I would like to do the same thing using Visual Basic 6... When I set the RS.Source to the first SELECT statement and open it, it run OK but I need to close the RS and open it again using the second SELECT statement. After any of this operations I get a Recordset-RecordsCount = 1 How can I work this out? Since you are asking for the row count from the database and not the driver, you have to treat it like a normal result. So rs.Fields(1) should contain the row count. Jess -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I detect if MySql is already installed?
On 2/15/07, Duncan Hill [EMAIL PROTECTED] wrote: On Thursday 15 February 2007 11:12:11 Juan Eduardo Moreno wrote: Linux : $ rpm -qa |grep -i mysql Regards, Juan On 2/14/07, Zsolt Csillag [EMAIL PROTECTED] wrote: Hello, I would like to create a setup program for my demo version that uses MySql database. I need to detect if the target computer has already got the MySql installed because I don't want to ruin an existing database. Why do you need to detect if MySQL is already installed? You can simply but all files at another location (your program's folder) and write the my.ini/my.cnf file on the fly, changing the datadir and other options for it to work wherever your program will be located. You can also use sockets or named pipes instead of TCP/IP to ensure you won't block any port or mess with the network, starting your server without networking support. That only works for the limited case of * System supports RPM * Admin installed MySQL as an RPM Without knowing what platform you intend your demo to run on, giving an accurate answer will be hard. Furthermore, it's very possible to have multiple installs of MySQL on a server without conflict, so done correctly, you won't trash any other MySQL installs. Agreed completely. You simply can't bet someone is using a rpm/deb/portage based system and expect your consumers to deal with it... Also the OP never mentioned the OS, version of MySQL, etc, and that makes it VERY difficult to get an answer. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I detect if MySql is already installed?
If you have root access, do 'mysqladmin -u... -h... ping' - Original Message - From: Daniel da Veiga [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 15, 2007 1:08:49 PM (GMT-0500) Auto-Detected Subject: Re: How can I detect if MySql is already installed? On 2/15/07, Duncan Hill [EMAIL PROTECTED] wrote: On Thursday 15 February 2007 11:12:11 Juan Eduardo Moreno wrote: Linux : $ rpm -qa |grep -i mysql Regards, Juan On 2/14/07, Zsolt Csillag [EMAIL PROTECTED] wrote: Hello, I would like to create a setup program for my demo version that uses MySql database. I need to detect if the target computer has already got the MySql installed because I don't want to ruin an existing database. Why do you need to detect if MySQL is already installed? You can simply but all files at another location (your program's folder) and write the my.ini/my.cnf file on the fly, changing the datadir and other options for it to work wherever your program will be located. You can also use sockets or named pipes instead of TCP/IP to ensure you won't block any port or mess with the network, starting your server without networking support. That only works for the limited case of * System supports RPM * Admin installed MySQL as an RPM Without knowing what platform you intend your demo to run on, giving an accurate answer will be hard. Furthermore, it's very possible to have multiple installs of MySQL on a server without conflict, so done correctly, you won't trash any other MySQL installs. Agreed completely. You simply can't bet someone is using a rpm/deb/portage based system and expect your consumers to deal with it... Also the OP never mentioned the OS, version of MySQL, etc, and that makes it VERY difficult to get an answer. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I detect if MySql is already installed?
--- Rolando Edwards [EMAIL PROTECTED] wrote: If you have root access, do 'mysqladmin -u... -h... ping' - Original Message - From: Daniel da Veiga [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 15, 2007 1:08:49 PM (GMT-0500) Auto-Detected Subject: Re: How can I detect if MySql is already installed? On 2/15/07, Duncan Hill [EMAIL PROTECTED] wrote: On Thursday 15 February 2007 11:12:11 Juan Eduardo Moreno wrote: Linux : $ rpm -qa |grep -i mysql Regards, Juan On 2/14/07, Zsolt Csillag [EMAIL PROTECTED] wrote: Hello, I would like to create a setup program for my demo version that uses MySql database. I need to detect if the target computer has already got the MySql installed because I don't want to ruin an existing database. Why do you need to detect if MySQL is already installed? You can simply but all files at another location (your program's folder) and write the my.ini/my.cnf file on the fly, changing the datadir and other options for it to work wherever your program will be located. You can also use sockets or named pipes instead of TCP/IP to ensure you won't block any port or mess with the network, starting your server without networking support. That only works for the limited case of * System supports RPM * Admin installed MySQL as an RPM Without knowing what platform you intend your demo to run on, giving an accurate answer will be hard. Furthermore, it's very possible to have multiple installs of MySQL on a server without conflict, so done correctly, you won't trash any other MySQL installs. Agreed completely. You simply can't bet someone is using a rpm/deb/portage based system and expect your consumers to deal with it... Also the OP never mentioned the OS, version of MySQL, etc, and that makes it VERY difficult to get an answer. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] usally you can look in /var/lib/mysql and see all the db's already created. But if you don't see it, there is no garentee that mysql is not installed. What flavor of linux is on the server (debian, SuSe, Redhat) and what version (SELS9 Fedora Core 4, Etch?) To find out cat /etc/(name)-release MySQL-server-standard-5.0.18-0.sles9 MySQL-devel-standard-5.0.18-0.sles9 MySQL-client-standard-5.0.18-0.sles9 Also you are going to have to figure out if you use php if so you must check to see if it is installed as well, and compiled with mysql support. hope this helps -winn johnston We won't tell. Get more on shows you hate to love (and love to hate): Yahoo! TV's Guilty Pleasures list. http://tv.yahoo.com/collections/265 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
#2002 - The server is not responding
I haven't been into phpMyAdmin for a while, but when I tried today I got the error: |#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured) This error message didn't seem to tie in with anything on the MySQL site, which gives: | Error: |2002| (|CR_CONNECTION_ERROR|) Message: Can't connect to local MySQL server through socket '%s' (%d) | I only use MySQL every now and then, just for experimenting with programming projects, so it could be that phpMyAdmin hasn't been working for some time. I'm on a SUSE 10.0 box with an AMD64 dual core 4200+ processor. The server is up and running and is using the socket file: /var/lib/mysql/mysql.sock The permissions on that file are: srwxrwxrwx 1 mysql mysql 0 2007-02-15 19:24 /var/lib/mysql/mysql.sock mysql.log is completely empty. Am I missing something really obvious? I'm a decent programmer, but the world's worst sysadmin, I'm afraid. Thanks Peter | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Export Results?
Is there a way to export results of a query to a file? In other words, if I do a select * from somefile, is there a phrase like send output to somefile.txt, or something? I've searched the help file, and I guess I don't know what I'm looking for, if it even exists. I'm using MySQL version 5 Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Translation
I am currently working on a project to support using a MySQL database along with SQL Server 2000. The following query has me stumped: Select * from group_mstr gm,group_payers gp where gm.practice_id = '1' and gp.location_id = '2' and gp.practice_id =* gm.practice_id and gp.group_id =* gm.group_id order by gp.payer_id Does anyone know how to properly convert this to use ANSI joins instead? I converted others, but am having trouble this with one. In our test case, the group_payers table does not have a location id equal to 2. Yet, it still returns a row but populates all the group_payers columns are NULL. After our translation attempts returns no rows. (I am running the test cases in SQL Server 2000 first, to show our changes will still work with the current setup) Thanks, Adam
Re: Export Results?
Jesse wrote: Is there a way to export results of a query to a file? In other words, if I do a select * from somefile, is there a phrase like send output to somefile.txt, or something? I've searched the help file, and I guess I don't know what I'm looking for, if it even exists. I'm using MySQL version 5 You can use SELECT INTO OUTFILE: The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file See: http://dev.mysql.com/doc/refman/5.0/en/select.html for more details. Alternatively, you can run mysql command line client with the 'e' flag and redirect standard output to a file: mysql -e 'SELECT * FROM test;' sql_output -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Translation
In the last episode (Feb 15), Trimeloni, Adam said: I am currently working on a project to support using a MySQL database along with SQL Server 2000. The following query has me stumped: Select * from group_mstr gm,group_payers gp where gm.practice_id = '1' and gp.location_id = '2' and gp.practice_id =* gm.practice_id and gp.group_id =* gm.group_id order by gp.payer_id I bet =* is shorthand for an outer join (not sure if it's left or right). You should be able to do the same in mysql with SELECT * FROM group_mstr gm LEFT JOIN group_payers gp ON ( gp.practice_id = gm.practice_id AND gp.group_id = gm.group_id ) WHERE gm.practice_id = '1' AND gp.location_id = '2' ORDER BY gp.payer_id Since the column names are the same in both tables, you can even shorten it a bit and use SELECT * FROM group_mstr gm LEFT JOIN group_payers gp USING ( practice_id, group_id ) WHERE gm.practice_id = '1' AND gp.location_id = '2' ORDER BY gp.payer_id Does anyone know how to properly convert this to use ANSI joins instead? I converted others, but am having trouble this with one. In our test case, the group_payers table does not have a location id equal to 2. Yet, it still returns a row but populates all the group_payers columns are NULL. After our translation attempts returns no rows. (I am running the test cases in SQL Server 2000 first, to show our changes will still work with the current setup) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error with mysqldump
When my server was running MySQL 4.1 I used mysqldump for backup with no errors - Redhat Linux 9. In fact when the server crashed and I had to restore the whole box I used the backups from mysqldump to restore all the databases and tables including the users. However, the server is now running MySQL 5.0 on Redhat Linux 9. When I try to run mysqldump I encounter errors as shown below: [mysqldump -p -u eusers mysql] -- -- Dumping data for table `columns_priv` -- /*!4 ALTER TABLE `columns_priv` DISABLE KEYS */; LOCK TABLES `columns_priv` WRITE; UNLOCK TABLES; /*!4 ALTER TABLE `columns_priv` ENABLE KEYS */; mysqldump: mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'columns\_priv'': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGERS LIKE 'columns\_priv'' at line 1 (1064) Does anyone know what is happening here? What am I doing wrong? -- Peter ___ Life is not measured by the number of breaths we take, but by the moments that take our breath away. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export Results?
Thanks, Exactly what I was looking for. It helps to know what you are looking for when browsing the help file. Jesse - Original Message - From: Chris White [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, February 15, 2007 4:34 PM Subject: Re: Export Results? Jesse wrote: Is there a way to export results of a query to a file? In other words, if I do a select * from somefile, is there a phrase like send output to somefile.txt, or something? I've searched the help file, and I guess I don't know what I'm looking for, if it even exists. I'm using MySQL version 5 You can use SELECT INTO OUTFILE: The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file See: http://dev.mysql.com/doc/refman/5.0/en/select.html for more details. Alternatively, you can run mysql command line client with the 'e' flag and redirect standard output to a file: mysql -e 'SELECT * FROM test;' sql_output -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Translation
Select * from group_mstr gm,group_payers gp where gm.practice_id = '1' and gp.location_id = '2' and gp.practice_id =* gm.practice_id and gp.group_id =* gm.group_id order by gp.payer_id I bet =* is shorthand for an outer join (not sure if it's left or right). You should be able to do the same in mysql with Isn't that the Oracle syntax for join? I didn't think that was supported in SQL 2000 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Translation
I should have mentioned it is a short hand for a join. *= is a left outer join. =* is a right outer join. Adam Trimeloni Project Leader Quality Systems [EMAIL PROTECTED] (949) 255-2600 -Original Message- From: Gary W. Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, February 15, 2007 2:19 PM To: Dan Nelson; Trimeloni, Adam Cc: mysql@lists.mysql.com Subject: RE: SQL Translation Select * from group_mstr gm,group_payers gp where gm.practice_id = '1' and gp.location_id = '2' and gp.practice_id =* gm.practice_id and gp.group_id =* gm.group_id order by gp.payer_id I bet =* is shorthand for an outer join (not sure if it's left or right). You should be able to do the same in mysql with Isn't that the Oracle syntax for join? I didn't think that was supported in SQL 2000 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: #2002 - The server is not responding
I haven't been into phpMyAdmin for a while, but when I tried today I got the error: |#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured) This error message didn't seem to tie in with anything on the MySQL site, which gives: Error: |2002| (|CR_CONNECTION_ERROR|) Message: Can't connect to local MySQL server through socket '%s' (%d) I only use MySQL every now and then, just for experimenting with programming projects, so it could be that phpMyAdmin hasn't been working for some time. I'm on a SUSE 10.0 box with an AMD64 dual core 4200+ processor. The server is up and running and is using the socket file: /var/lib/mysql/mysql.sock The permissions on that file are: srwxrwxrwx 1 mysql mysql 0 2007-02-15 19:24 /var/lib/mysql/mysql.sock mysql.log is completely empty. Am I missing something really obvious? I'm a decent programmer, but the world's worst sysadmin, I'm afraid. Thanks Peter Hi Peter, Please check phpMyAdmin Configuration file ( for Port addr ) . Thanx Onur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary table lifespan
I'm writing a perl script in which I need to save some session data. My first attempt is to use a temporary table to store some data. However, I'm a little unclear as to the lifespan of the temporary table. My understanding is they last until the session ends, or a DELETE TABLE is issued. My question is 'what is a session'? For example, in my perl script I create and populate the table in one subroutine, but need to access it from another in the same script. But it doesn't appear to live through the transition from one subroutine to another. Anyone have an idea whether this can even be done, or does calling another subroutine end the 'session', thus killing the table? -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan
Amer Neely wrote: I'm writing a perl script in which I need to save some session data. My first attempt is to use a temporary table to store some data. However, I'm a little unclear as to the lifespan of the temporary table. My understanding is they last until the session ends, or a DELETE TABLE is issued. My question is 'what is a session'? For example, in my perl script I create and populate the table in one subroutine, but need to access it from another in the same script. But it doesn't appear to live through the transition from one subroutine to another. Anyone have an idea whether this can even be done, or does calling another subroutine end the 'session', thus killing the table? Session isn't exactly the right word for it. The temporary tables hang around for the lifetime of the database connection. So if you open a database connection, make a tmp table, close the DB connection, and open another one ( inside the same subroutine ), your tmp table will be gone. Also, if you open 2 DB connections, and make a tmp table from one, the other connection can't see it. So just keep your DB connection ( or database handle in Perl speak ) live and you should be able to see the table. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan
Amer Neely wrote: Daniel Kasak wrote: Amer Neely wrote: I'm writing a perl script in which I need to save some session data. My first attempt is to use a temporary table to store some data. However, I'm a little unclear as to the lifespan of the temporary table. My understanding is they last until the session ends, or a DELETE TABLE is issued. My question is 'what is a session'? For example, in my perl script I create and populate the table in one subroutine, but need to access it from another in the same script. But it doesn't appear to live through the transition from one subroutine to another. Anyone have an idea whether this can even be done, or does calling another subroutine end the 'session', thus killing the table? Session isn't exactly the right word for it. The temporary tables hang around for the lifetime of the database connection. So if you open a database connection, make a tmp table, close the DB connection, and open another one ( inside the same subroutine ), your tmp table will be gone. Also, if you open 2 DB connections, and make a tmp table from one, the other connection can't see it. So just keep your DB connection ( or database handle in Perl speak ) live and you should be able to see the table. OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan
Amer Neely wrote: OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. It's hard to say without more details. You can send the script if you want. Also keep in mind that since you're using tmp tables which are invisible to all other connections, you don't need to much around with creating a unique table name - you can just use a generic one ( eg tmp_purchase_order or something ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan
Daniel Kasak wrote: Amer Neely wrote: OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. It's hard to say without more details. You can send the script if you want. Also keep in mind that since you're using tmp tables which are invisible to all other connections, you don't need to much around with creating a unique table name - you can just use a generic one ( eg tmp_purchase_order or something ). I'll try something like that tomorrow. Thanks for the responses. I'll post my results. -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Numeric sorting within a string
I'm wondering if there is a way to do a numeric sort when the number exists in a string. More clearly, take for example: Radius 1200 Radius 1500 Radius 1800 Radius 300 Radius 600 Radius 900 Being that character wise 1 is before 3, I'm wondering if there's a way through the database to achieve: Radius 300 Radius 600 Radius 900 Radius 1200 Radius 1500 Radius 1800 If not I'll try and handle it through PHP instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways replication
Hi, Most of the documents talk about the normal replication and dual master. None of them talk about the conflict handling. Does it mean that we need to handle the conflict ourselves? As I know, MSSQL has a way to implement 2 ways replication. Any drawback if using MSSQL for this kind of replication? Any comparison about database replication? On 2/14/07, Rilawich Ango [EMAIL PROTECTED] wrote: Thanks for the links. It seems helpful for me. I will read it then. BTW, does anyone implement multi-master replication successfully? I have read a lot of document and they all don't recommend to do it. On 2/13/07, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Dear all, We can view interesting tipic into : http://www.onlamp.com/lpt/a/6549 Regards, Juan Eduardo On 2/12/07, Atle Veka [EMAIL PROTECTED] wrote: 2 way replication, also referred to as dual master replication, has been available for quite some time. However implementation can be tricky. Look for the Dual master section in chapter 7 of the High Performance MySQL book: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html If you need more than 2 masters, then all bets are off.. Atle On Mon, 12 Feb 2007, Rilawich Ango wrote: Hi all, I know it is an old question and I have read from the mysql website about the topic. Until now, mysql still doesn't support 2 ways replication, quoted from mysql website. As I have multiple location and each location will have a DB. Most of all need to read and write to the database. In my case, 2 ways replication is the most direct way to do it. Any other solution is suitable for me to implement if 2 ways replication is not a good way? Anyone has successfully implement 2 ways replication? Any suggestion? 1PC-read/update-DB(a) --- 2 ways replication -- DB(b)-read/update-PC2 ango -- 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: Numeric sorting within a string
This is totally possible... Chris say your table is test, and the column is test_col.. use the following, and if the number is always at the end.. and always has a space in front of it this will work: SELECT test_col, SUBSTRING_INDEX(test_col, '', 1) as test_col_str, SUBSTRING_INDEX(test_col, ' ', -1) + 0 as test_col_num FROM test ORDER BY test_col_str, test_col_num; good luck, --bemansell On 2/15/07, Chris White [EMAIL PROTECTED] wrote: I'm wondering if there is a way to do a numeric sort when the number exists in a string. More clearly, take for example: Radius 1200 Radius 1500 Radius 1800 Radius 300 Radius 600 Radius 900 Being that character wise 1 is before 3, I'm wondering if there's a way through the database to achieve: Radius 300 Radius 600 Radius 900 Radius 1200 Radius 1500 Radius 1800 If not I'll try and handle it through PHP instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan - SOLVED
Amer Neely wrote: Daniel Kasak wrote: Amer Neely wrote: OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. It's hard to say without more details. You can send the script if you want. Also keep in mind that since you're using tmp tables which are invisible to all other connections, you don't need to much around with creating a unique table name - you can just use a generic one ( eg tmp_purchase_order or something ). I'll try something like that tomorrow. Thanks for the responses. I'll post my results. After failing to get the temporary table method working, I ended up just creating a table, then dropping it when I was done. It seems that a temporary table will get deleted when a script calls itself and jumps to a function inside. That must constitute a disconnection. -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
