Re: How to SUM every row for SubTotal?
but it can't work if multiple tables. how it can use for multiple tables? - Original Message - From: ISC Edwin Cruz [EMAIL PROTECTED] To: 'The Nice Spider' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, November 23, 2005 7:13 AM Subject: RE: How to SUM every row for SubTotal? mysql select * from table5; ++--+ | code | qty | ++--+ |100 | 30 | |200 | 20 | |300 | 25 | ++--+ 3 rows in set (0.00 sec) mysql set @a=0; Query OK, 0 rows affected (0.05 sec) mysql select code,qty,@a:[EMAIL PROTECTED] as subtotal from table5; ++--++ | code | qty | subtotal | ++--++ |100 | 30 | 30 | |200 | 20 | 50 | |300 | 25 | 75 | ++--++ 3 rows in set (0.00 sec) mysql I hope that it helps you Regards! -Mensaje original- De: The Nice Spider [mailto:[EMAIL PROTECTED] Enviado el: Martes, 22 de Noviembre de 2005 05:27 p.m. Para: mysql@lists.mysql.com Asunto: How to SUM every row for SubTotal? If I have table like: CODE QTY 100 30 200 20 300 25 and I want a result like: CODE QTY SUBTOTAL 100 30 30 200 20 50 (this come from 30+20) 300 25 75 (this come from 50+25) How to write query like that? - Yahoo! FareChase - Search multiple travel sites in one click. -- 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: chroot in MySQL 5.0
Hello. I was only testing MySQL 5.0.16 in chroot environment, so my configuration isn't perfect and should be improved, but mysqld runs in it: [EMAIL PROTECTED] mysqls]# /home/gleb/mysqls/chroot/mysqls/bin/mysqld --defaults-file=/home/gleb/mysqls/chroot/mysqls/my.cnf [EMAIL PROTECTED] mysqls]$ ls chroot/ bin etc home lib mysqls tmp usr var [EMAIL PROTECTED] mysqls]$ ls chroot/bin/ bash ls sh [EMAIL PROTECTED] mysqls]$ ls chroot/etc/ chroot/home/ chroot/tmp/ chroot/usr/ chroot/var/ chroot/etc/: group passwd chroot/home/: gleb chroot/tmp/: chroot/usr/: lib local chroot/var/: [EMAIL PROTECTED] mysqls]$ ls chroot/home/gleb/ chroot/usr/lib/ chroot/usr/local/ chroot/home/gleb/: chroot/usr/lib/: libnss_compat.so libnss_files.so libnss_nisplus.so libnss_dns.so libnss_hesiod.so libnss_nis.so chroot/usr/local/: mysql [EMAIL PROTECTED] mysqls]$ ls chroot/lib/ ld-linux.so.2 libnss_dns.so.2 libnss_nis.so.2 libc.so.6 libnss_files-2.3.5.solibnss_tcb.so.2 libdl.so.2 libnss_files.so libnss_winbind.so libhistory.so.4 libnss_files.so.2libnss_winbind.so.2 libhistory.so.4.3 libnss_hesiod-2.3.5.so libnss_wins.so libnsl-2.3.5.so libnss_hesiod.so libnss_wins.so.2 libnsl.so.1 libnss_hesiod.so.2 libpthread.so.0 libnss_compat-2.3.5.so libnss_nis-2.3.5.so libreadline.so.4 libnss_compat.solibnss_nisplus-2.3.5.so libreadline.so.4.3 libnss_compat.so.2 libnss_nisplus.solibrt.so.1 libnss_dns-2.3.5.so libnss_nisplus.so.2 libtinfo.so.5 libnss_dns.so libnss_nis.so In mysqls directory I have unpacked mysql-debug-5.0.16-linux-i686-glibc23 distribution. My configuration file: [client] #password = your_password port= 3752 socket = /mysqls/tmp/mysql.sock.gleb.i default_character_set=utf8 # Here follows entries for some specific programs character-sets-dir= /mysqls/share/mysql/charsets # The MySQL server [mysqld] character-sets-dir= /mysqls/share/mysql/charsets #init_connect='set @lchar=1;' #ft_stopword_file=/mysqls/ftopfile.txt #default_character_set=utf8 #default-collation=cp1251_bin chroot=/home/gleb/mysqls/chroot default_time_zone='+10:00' log_slow_queries=/mysqls/logs/mysql.log-slow.i long_query_time=1 max_connections=100 #myisam_data_pointer_size=8 #old_passwords #log_warnings=2 set-variable=user=gleb #lower_case_table_names=1 default_character_set=latin2 port= 3752 socket = /mysqls/tmp/mysql.sock.gleb.i skip-locking pid-file= /mysqls/tmp/mysql.pid.gleb.i log = /mysqls/logs/mysql.log.i #log= OFF log-bin = /mysqls/logs/log_i/log_i log-error = /mysqls/logs/mysql.log-error.i datadir =/mysqls/data #key_buffer = 100M #max_allowed_packet = 50M table_cache = 4 #sort_buffer_size = 64M #myisam_sort_buffer_size = 64M #read_buffer_size = 10M #read_rnd_buffer_size = 10M net_buffer_length = 2K thread_stack = 64K wait_timeout=1000 net_read_timeout = slave_net_timeout = 2555 query_cache_size = 5MB interactive_timeout=50 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (using the enable-named-pipe option) will render mysqld useless! # #skip-networking #skip-innodb server-id = 1 #replicate-rewrite-db=test-test2; # Uncomment the following if you want to log updates #log-bin # Uncomment the following if you are NOT using BDB tables #skip-bdb innodb_file_per_table innodb_data_home_dir=/mysqls/data innodb_data_file_path = ibdata1:10M;ibdata2:50M; #innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_group_home_dir =/mysqls/data #innodb_log_arch_dir = /usr/local/mysql/data/ innodb_log_arch_dir =/mysqls/data # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /usr/local/mysql/data/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/data/ #innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M
Re: moving the datadir
Hello. # mysql -u=root What is 'SELECT CURRENT_USER();' reporting for you? Rajesh Olafson [EMAIL PROTECTED] wrote: hanks Gleb. I get the exact same results when I connect with: # mysql -u=root --- Maybe a different issue but, for some reason I can't connect with # mysql --user=root ^^^ ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) but I can with: # mysql -user=root ^^^ MySQL v4.1.10a-standard. Thanks for your time. I'm only a sporadic user of MySQL - so some of this maybe a case of RTM. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Trouble setting root password
Hello. I have also tried mysqld_safe --skip-grant-tables --user=root Don't run mysqld under root (even more - fresh versions should automatically skip this option). What exactly have you done to restore root password? Please provide exact SQL statements. What user is 'Access denied' error message reporting. Are you using old passwords? BTW: your version of MySQL is old enough, I recommend you to upgrade to the latest release. Logg, Connie A. wrote: I tried to go into mysql with root and the password I had set, and = access was refused. I finally gave up and decided to reset the root password using the = techniques described on http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html This is a linux machine running mysql Your MySQL connection id is 5 to = server version: 4.1.7-standard I have tried both the mysqld_safe --init-file=3D/home/iepm/v3src/minit = which has the password setting statement in it And this does not work...I cannot get into mysql with the password. I have also tried mysqld_safe --skip-grant-tables --user=3Droot And tried setting the password and doing flush privileges. This also does not work. I am at my wits end as to how to do this, which I have been able to do = before. Any insight and assistance would be appreciated. Thank you, Connie=20 Connie Logg, Network Analyst Stanford Linear Accelerator Center ph: 650-926-2879=20 Happiness is found along the way, not at the end of the road, and 'IF' = is the middle word in life. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Can't switch databases 5.0.15-0 RedHat ES4
Hi Walt, all! walt wrote: Is this something new with mysql 5, a bug, or something I'm missing? I'm not able to switch databases. A known bug (#14504 and others) in handling the separator at the end of a use statement, see http://bugs.mysql.com/bug.php?id=14504 mysql CREATE DATABASE FOO; Query OK, 1 row affected (0.00 sec) [[...]] mysql use FOO; ERROR 1049 (42000): Unknown database 'FOO;' mysql show databases; ++ | Database | ++ | information_schema | | FOO| | mysql | | test | ++ 4 rows in set (0.06 sec) Do without the semicolon (for now): use FOO HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to limit MySQL child processes?
Hello. MySQL has not only per-connection threads. See: http://dev.mysql.com/doc/internals/en/threads.html Maxim Nechaev wrote: After my MySQL server start i see this processes: [EMAIL PROTECTED]:/ps -HC mysqld -o pid,command PID COMMAND 3116 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql 3117 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 3118 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m 3119 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m 3120 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m 3121 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m 3122 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m 3123 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m 3124 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m 3125 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/m If i understand, processes with pids 3118-3125 is a child processes, that waiting client connections. I don't need so many client connections at the same time. It is possible to limit their count? I try to set this options in my.cnf but without result: max_connections = 1 max_user_connections = 1 max_delayed_threads = 1 Thanks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: UPDATE and INDEX updates
Hello. don't think I will find the answer to this question in the normal manuals but rather from someone / place that deals with the internals of the system. Mike MySQL internals are available at: http://dev.mysql.com/doc/internals/en/ Mike OK wrote: I did read the manuals (three including MySQL reference manual and DuBois ver 5) as well as Google searches. I am using Myisam tables (installed by default) and from what I can tell by the from the manual the indexes would be B-tree but I would be willing to work with another type if necessary. I don't think I will find the answer to this question in the normal manuals but rather from someone / place that deals with the internals of the system. Mike -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Tips for better performance
Marko, Do you have an index deleted + name on the accounts table? How many rows have deleted=0? Can you apply any more filter in the users table (like date)? You are requesting all rows. How many rows do you have in the users table? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot get database connection
Have you tried to put the port 3306 after the host? I assume you are in
the localhost.
Not sure if this is the place to ask but hopefully someone can help me
here. I am setting an application system and have successfully setup
MySQL 5.0.15 and Tomcat 5.5.12. However, I cannot seem to get a
connection object from my servlet and for the life of me, I can't figure
out why. Here is what I have done:
Downloaded mysql-connector-java-3.1.11-bin.jar into
$CATALINA_HOME/common/lib
Added mysql-connector-java-3.1.11-bin.jar to my classpath
and I created the following servlet, TestSQL:
import java.sql.*;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.naming.*;
public class TestSQL extends HttpServlet {
private Connection conn = null;
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
response.setContentType(text/html);
PrintWriter out = response.getWriter();
try {
if (conn == null) {
Class.forName(com.mysql.jdbc.Driver).newInstance();
connW =
DriverManager.getConnection(jdbc:mysql://localhost/hostname, id,
password);
}
} catch (Exception e) {
e.printStackTrace();
}
if (conn != null) {
out.println(Connection read successful);
} else {
out.println(Cannot get connection);
}
}
}
I keep getting Cannot get connection message in my servlet. I tried
connecting to the database (locally) using mysql client with id just
to make sure the account works and it does. I setup a similar system
before and was able to connect to the database successfully so I can't
figure out what's wrong.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Master of Applied Computer Science
Faculty of Computer Science
Dalhousie University
Halifax, Nova Scotia
Canada
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot get database connection
Xiaobo Chen wrote: Have you tried to put the port 3306 after the host? I assume you are in the localhost. Yes, I am trying to connect in localhost. I did 'nmap localhost' and port 3306 is open and I can connect to mysqld with mysql client. I will try putting 3306 after the host when I get in the office tommorrow but I doubt that is the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot get database connection
I am not sure if you're trying to connect from outside of your office newwork. If you try 'telnet' using 3306 (forgot the syntax, check the Mysql site)outside your office network and if you couldn't get through,(otherwise you will get some garbage characters) then you can't connect to the database from outside. I had once had such connection issue. If that will be your case, I guess you have to ask admin for help, it's not mysql at all. If your database is on a XP box, you need to create a service and open the 3306 port there (assuming you have firewall on it, otherwise you shouldn't have this issue.) Good luck. Xiaobo Chen wrote: Have you tried to put the port 3306 after the host? I assume you are in the localhost. Yes, I am trying to connect in localhost. I did 'nmap localhost' and port 3306 is open and I can connect to mysqld with mysql client. I will try putting 3306 after the host when I get in the office tommorrow but I doubt that is the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Master of Applied Computer Science Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tips for better performance
Do you have an index deleted + name on the accounts table? How many rows have deleted=0? that depends because SugarCRM doesn't delete anything from DB just marks item as deleted using this field. Can you apply any more filter in the users table (like date)? You are requesting all rows. no, because purpose of this query is to list all contacts, and using LIMIT contacts are then paginated on web site. How many rows do you have in the users table? We are not sure how many user will be in our DB, but my asumption is that there should not be more than 40 - 50, but don't take my word on it... BTW, we managed to solve most of our problem by adding lots of indexes, because this db was not indexed at all !!! So, we will post the results soon when we tweak everything. We are still open to reccomendations about tweaking my.cnf file because that is next step after we optimize tables. As i mentioned we are using Double Xeon processors with 2 GB RAM, and two SCSI HDDs in RAID0 mode. Will it be enough to use only my-huge.cnf file or we can do some more tweaks? And, thank you all for your help on this issue, we appreciate it very much Regards, Marko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot get database connection
Xiaobo Chen wrote: I am not sure if you're trying to connect from outside of your office newwork. If you try 'telnet' using 3306 (forgot the syntax, check the Mysql site)outside your office network and if you couldn't get through,(otherwise you will get some garbage characters) then you can't connect to the database from outside. I had once had such connection issue. If that will be your case, I guess you have to ask admin for help, it's not mysql at all. If your database is on a XP box, you need to create a service and open the 3306 port there (assuming you have firewall on it, otherwise you shouldn't have this issue.) Good luck. As far as the servlet is concerned, it is connecting locally in localhost. But yes, I can also connect remotely using a MySQL Gui such as SQLYog. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL with InnoDB on a readonly filesystem.
Hello, Is it possible to run MySQL with InnoDB enabled on a readonly filesystem. We only intend to run some select query's on this server. This is the errormessage from InnoDB Nov 23 13:36:30 aliao mysqld[16073]: InnoDB: Error number 30 means 'Read-only file system'. Nov 23 13:36:30 aliao mysqld[16073]: InnoDB: Some operating system error numbers are described at Nov 23 13:36:30 aliao mysqld[16073]: InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html Nov 23 13:36:30 aliao mysqld[16073]: InnoDB: File name /mnt/dbslave/latest-snapshot/data/mysql/data/ibdata1 Nov 23 13:36:30 aliao mysqld[16073]: InnoDB: File operation call: 'open'. Nov 23 13:36:30 aliao mysqld[16073]: InnoDB: Cannot continue operation. Why does InnoDB open this file read/write, is there an option avaliable for opening this file read-only? Regards, Ralph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tips for better performance
Marko Knezevic [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Here are my tables and queries i am running on them with index explanations. Hope this will help. ACCOUNTS TABLE: +-+--+--+-+-+ | Field | Type | Null | Key | Default +-+--+--+-+-+ | id | varchar(36) | | PRI | | assigned_user_id| varchar(36) | YES | MUL | NULL | deleted | tinyint(1) | | | 0 +-+--+--+-+-+ USERS TABLE: +---+--+--+-+-+ | Field | Type | Null | Key | Default | +---+--+--+-+-+ | id| varchar(36) | | PRI | | +---+--+--+-+-+ FIRST: Typically, you need indexes on fields that are specified in your JOIN/WHERE clause: accounts.assigned_user_id users.id accounts.deleted We added those indexes and gained performance improvements. Thanx. Now we are adding more indexes and examine queries. SECOND: Why are your id fields all VARCHAR(36)? These are huge keys! I would recommend using an INT as the id (that allows over 4 billion unique id's). If you have some internal id that is 36 characters long, have two id fields -- one for the relational-key-id (INT), and then your long string id for your clients to use. Well, this is feature of SugarCRM (http://www.sugarcrm.com) they use varchar because they are using UUID() as ID, i don't know why, but i presume that they want to have every ID in their DB to be unique, so that's explanation for varchar. Regards Marko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tips for better performance
that depends because SugarCRM doesn't delete anything from DB just marks item as deleted using this field. Do you have an index for deleted column? It should help. Can you apply any more filter in the users table (like date)? You are requesting all rows. no, because purpose of this query is to list all contacts, and using LIMIT contacts are then paginated on web site. Pay attention that a ORDER BY name would be executed in all rows, regardless the LIMIT clause. How many rows do you have in the users table? We are not sure how many user will be in our DB, but my asumption is that there should not be more than 40 - 50, but don't take my word on it... Hmmm, so you should do better job in the accounts table. If this query is used frequently, perhaps filtering by only one user should help. Will it be enough to use only my-huge.cnf file or we can do some more tweaks? I guess so. The bellow documentation has helped me a lot, although it´s for 3.23 version: http://www.tnt.uni-hannover.de/print/plain/soft/database/MySQL/Docs/manual_Performance.html Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting charset
For Archivum My destination was: Changing default latin1 charset to latin2 and the default collation to latin2_hungarian_ci. I tried to configure accross my.cnf, but all of my effort failed. I installed mysql-administrator, use startup paramters in. Change mysqldump . Took changes and it works. Joe - Yahoo! Model Search - Could you be the next catwalk superstar? Check out the competition now
Re: UPDATE and INDEX updates
Thanks for the point to the internals. I will keep this page bookmarked. I read everything there regarding indexes and did not find the answer I was looking for. I think what I need falls under the UPDATE statement (no listing there). Specifically, what triggers the action for index re-builds. Is it the row changed flag or is there a column changed flag ??? Mike - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: November 23, 2005 5:51 AM Subject: Re: UPDATE and INDEX updates Hello. don't think I will find the answer to this question in the normal manuals but rather from someone / place that deals with the internals of the system. Mike MySQL internals are available at: http://dev.mysql.com/doc/internals/en/ Mike OK wrote: I did read the manuals (three including MySQL reference manual and DuBois ver 5) as well as Google searches. I am using Myisam tables (installed by default) and from what I can tell by the from the manual the indexes would be B-tree but I would be willing to work with another type if necessary. I don't think I will find the answer to this question in the normal manuals but rather from someone / place that deals with the internals of the system. Mike -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 2005-11-21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT SELECT Problem
Hi, The following query worked fine: INSERT INTO Allocations(Project_ID, User_ID) SELECT P.Project_ID, U.User_ID FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) However I want to add a column to the INSERT part of the query and the value to be inserted must always be 'Yes'. Is there a way to do this with just with one query? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT SELECT Problem
Hi, Try setting the default value of the column to 'Yes' that should work or INSERT INTO Allocations(Project_ID, User_ID, field) SELECT P.Project_ID, U.User_ID, 'Yes' FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) Met vriendelijke groet, Almar van Pel -Oorspronkelijk bericht- Van: Shaun [mailto:[EMAIL PROTECTED] Verzonden: woensdag 23 november 2005 15:22 Aan: mysql@lists.mysql.com Onderwerp: INSERT SELECT Problem Hi, The following query worked fine: INSERT INTO Allocations(Project_ID, User_ID) SELECT P.Project_ID, U.User_ID FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) However I want to add a column to the INSERT part of the query and the value to be inserted must always be 'Yes'. Is there a way to do this with just with one query? Thanks for your help. -- 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: INSERT SELECT Problem
Hi Shaun, You could use the following statement: INSERT INTO Allocations(Project_ID, User_ID, YES_COLUMN) SELECT P.Project_ID, U.User_ID, 'Yes' FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) Please replace YES_COLUMN with the column name that stores the 'Yes' values. Best regards, Diego Wald - Original Message - From: Shaun [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 23, 2005 11:21 AM Subject: INSERT SELECT Problem Hi, The following query worked fine: INSERT INTO Allocations(Project_ID, User_ID) SELECT P.Project_ID, U.User_ID FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) However I want to add a column to the INSERT part of the query and the value to be inserted must always be 'Yes'. Is there a way to do this with just with one query? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Información de NOD32 1.1298 (20051123) __ Este mensaje ha sido analizado con NOD32 antivirus system http://www.nod32.com ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL with InnoDB on a readonly filesystem.
Hi Ralph, Is it possible to run MySQL with InnoDB enabled on a readonly filesystem. We only intend to run some select query's on this server. I guess I can ask the obvious question. Why are you trying to use InnoDB, if your data/filesystem is read only? Why not use MyISAM instead? Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Control Center
On 11/22/05, Jim Winstead [EMAIL PROTECTED] wrote: On Tue, Nov 22, 2005 at 04:50:42PM -0800, Jon Drukman wrote: What happened to MySQL Control Center (aka mycc or mysqlcc)? The dev.mysql.com site redirects to the Query Browser page. QB is a poor substitute for mycc. It looks like neither of them has had active development much lately but at least mycc, even in its beta stage, is fairly useful. Development of MySQL Control Center was halted a couple of years ago. The new GUI tools (Query Browser, Administrator, etc) are still under active development. The Administrator can be downloaded here: http://dev.mysql.com/downloads/administrator/index.html The Query Browser can be downloaded here: http://dev.mysql.com/downloads/query-browser/1.1.html -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving the datadir
Hi Gleb, Here's the output: [EMAIL PROTECTED]:/usr/local/mysql/bin: ./mysql -u=root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 143 to server version: 4.1.10a-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SELECT CURRENT_USER(); ++ | CURRENT_USER() | ++ | @localhost | ++ 1 row in set (0.01 sec) mysql Thanks, ~RO On 11/22/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. # mysql -u=root What is 'SELECT CURRENT_USER();' reporting for you? Rajesh Olafson [EMAIL PROTECTED] wrote: hanks Gleb. I get the exact same results when I connect with: # mysql -u=root --- Maybe a different issue but, for some reason I can't connect with # mysql --user=root ^^^ ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) but I can with: # mysql -user=root ^^^ MySQL v4.1.10a-standard. Thanks for your time. I'm only a sporadic user of MySQL - so some of this maybe a case of RTM. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
SELECT/JOIN performance on temporary tables depends on timing of index creation
In MySQL 4.1.11, if you create two temporary tables with non-unique indices as part of the CREATE TEMPORARY TABLE statement, then insert several hundred (or thousand) rows, a subsequent SELECT using a JOIN between the two tables is substantially slower than if you create the indices after inserting the rows into the temporary tables. When I run a SHOW INDEX for these tables, the only difference is the Cardinality field, where it is NULL if the index was created prior to any inserts and is a number if the index was created after the inserts. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Allan Miller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-test-run -- func_compress failed with out of memory error...
Hi!
I am in the process of upgrading the machine with everything
to a newer versions. This time, I'm getting rid of the IBM DB2 as it
won't be there. So, I downloaded the MySQL 64 bits for the AIX and
extracted it there. The AIX server use 64 bits kernel and Enhanced
Journal File System (JFS2). It also have 1 GB of RAM and 35 GB Hard
Disk. I remembered that running this test is require to make sure MySQL
work properly and can handle the workload with the machine. So, I typed
the mysql-test-run command and here's what I got.
--snip-
fulltext [ pass ]
fulltext2 [ pass ]
fulltext_cache [ pass ]
fulltext_distinct [ pass ]
fulltext_left_join [ pass ]
fulltext_multi [ pass ]
fulltext_order_by [ pass ]
fulltext_update[ pass ]
fulltext_var [ pass ]
func_compress [ fail ]
Errors are (from /usr/local/mysql/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 48: query 'select compress(repeat('aa',
IF('', 10, 1000))) is null' failed: 5: Out of memory (Needed
12024 bytes)
(the last lines may be the most important ones)
Aborting: func_compress failed in default mode. To continue, re-run with
'--force'.
Ending Tests
Shutting-down MySQL daemon
Master shutdown finished
Slave shutdown finished
--snip-
I'm a little baffled because the server have 1 GB of RAM. Something is
wrong. So, what's up with that?
Thanks,
Scott
Re: chroot in MySQL 5.0
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Gleb, Thanks for the answer. I was hoping to use the mysql.server script that ships with MySQL to start the database, which it seems You are not doing. I think the script is the core of my problem. I guess i'll have to give up on the script, though, if i can't get it to work otherwise. (Unless someone else has any ideas . . . ) - Gleb Paharenko wrote: Hello. I was only testing MySQL 5.0.16 in chroot environment, so my configuration isn't perfect and should be improved, but mysqld runs in it: - -- GPG key / Schlüssel -- http://simultan.dyndns.org/~arjones/gpgkey.txt Encrypt everything. / Alles verschlüsseln. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFDhLDwoI7tqy5bNGMRAwvdAJ9yjMlWu5c5lnaWVG/CQ8XZSD02hQCfSWLp pS2A/+UWW11rcd+HYZJE9nw= =J0zh -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
urgent : PLEASE HELP - problems with back up and restore
Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak
Re: urgent : PLEASE HELP - problems with back up and restore
Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
mysql 4.1.14 suddenly does not see some data files!
Hi; I have been running this server, 4.1.14 on Linux FC 4 for while, no problems. Yesterday I fiddled with my.cnf via the Administrator console to, tried some graphs, and also turned on binary logs. Later in the day, (don't think it was immediate after the server process restart, but maybe), the JBoss App Server started exceptioning out with jdbc errors from the server, which simply stated, can't find file xyz, can't find file abc,. Also, the admin console, does not list these files in the catalog. Now the queer thing: ALL files are actually in the directory /var/lib/mysql/whatever, but most ARE listed in the admin console, while some are not. And again, physically they are all in the SAME db dir. This phenomenon is repeated in two databases/catalogs. Your advice is appreciated. Thank you. -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: urgent : PLEASE HELP - problems with back up and restore
Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn't help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn’t help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
Thanks again, I will try this and read the manual again. _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:30 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn't help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Mysql compile error
I'm getting ready to do a roll out next week on converting an application from MySQL to MSSQL. I'm installing the perl drivers on the production machines today. The perl DBD driver requires mysql to be installed. Everything's been going fine on all the machines until I got to the last one. On that one I am getting this error when compiling mysql: Making all in isam make[2]: Entering directory `/usr/src/mysql-4.1.14/isam' source='open.c' object='open.o' libtool=no \ depfile='.deps/open.Po' tmpdepfile='.deps/open.TPo' \ depmode=gcc /bin/sh ../depcomp \ gcc -DHAVE_CONFIG_H -I. -I. -I.. -I../include -I../include -O3 -DDBUG_OFF-c open.c open.c: In function `nisam_open': open.c:136: `F_TO_EOF' undeclared (first use in this function) open.c:136: (Each undeclared identifier is reported only once open.c:136: for each function it appears in.) make[2]: *** [open.o] Error 1 make[2]: Leaving directory `/usr/src/mysql-4.1.14/isam' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-4.1.14' make: *** [all] Error 2 Here is what I used for the ./configure command: ./configure --without-server \ --prefix=/usr/local/mysql \ --exec-prefix=/usr/local/mysql \ --with-named-curses-libs=/usr/lib/libncurses.so.4 \ --enable-thread-safe-client \ --with-extra-character-sets=complex Any ideas on what's wrong? I am installing verion 4.1.14, and the server is running RedHat 6.2. Thanks, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT/JOIN performance on temporary tables depends on timing of index creation
Hello. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Check whether the problem still exists if you run ANALYZE table after inserts in case you have already created indexes. FORCE_INDEX in the SELECT clause should help as well. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html Allan Miller wrote: In MySQL 4.1.11, if you create two temporary tables with non-unique indices as part of the CREATE TEMPORARY TABLE statement, then insert several hundred (or thousand) rows, a subsequent SELECT using a JOIN between the two tables is substantially slower than if you create the indices after inserting the rows into the temporary tables. When I run a SHOW INDEX for these tables, the only difference is the Cardinality field, where it is NULL if the index was created prior to any inserts and is a number if the index was created after the inserts. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Allan Miller -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: moving the datadir
Hello. | @localhost | Obviously it is not a root user. If you're unable to connect as a root have a look here: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Rajesh Olafson [EMAIL PROTECTED] wrote: Hi Gleb, Here's the output: [EMAIL PROTECTED]:/usr/local/mysql/bin: ./mysql -u=root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 143 to server version: 4.1.10a-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SELECT CURRENT_USER(); ++ | CURRENT_USER() | ++ | @localhost | ++ 1 row in set (0.01 sec) mysql Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 compile error - more info
I thought I would get around the whole thing by just installing the RPM instead of compiling. So I downloaded MySQL-devel-4.1.14-0.i386.rpm and installed it. Then I went to install the DBD-mysql module and got this error during the compile: /usr/bin/ld: cannot find -lz collect2: ld returned 1 exit status An error occurred while linking the DBD::mysql driver. The error message seems to indicate that you don't have a libz.a, or a libz.so. This is typically resolved by: 1.) You may try to remove the -lz or -lgz flag from the libs list by using the --libs switch for perl Makefile.PL. 2.) On Red Hat Linux install libz-devel 3.) On other systems, please contact the mailing list [EMAIL PROTECTED] For further hints, see INSTALL.html, section Linker flags. make: *** [blib/arch/auto/DBD/mysql/mysql.so] Error 1 So I figured out how to run mysql_config --libs and get that value, then put it on the perl Makefile.PL line without the -lz: perl Makefile.PL --libs=-L/usr/lib/mysql [the -lz was right here] -lmysqlclient -lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv Then I was able to compile without error. So I installed the driver. But when I try to use it to connect to mysql I get this error: install_driver(mysql) failed: Can't load '/usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBD/mysql/mysql.so' for module DBD::mysql: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBD/mysql/mysql.so: undefined symbol: compress at /usr/lib/perl5/5.00503/i386-linux/DynaLoader.pm line 169. at (eval 1) line 3 Perhaps a required shared library or dll isn't installed where expected at /home/wms/test1.pl line 8 Any help appriciated. I apologize for this message being formatted badly, there's no obvious break between my comments and my pasted error messages. I hate Outlook with a passion but am forced to use it. -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql compile error - fixed
I fixed the whole problem by typing this one line: `ln -s libz.so.1.1.3 /usr/lib/libz.so` Linux is such a pain sometimes. -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT/JOIN performance on temporary tables depends on timing of index creation
Hi Gleb, Thanks for the quick response. Unfortunately, ANALYZE TABLE does not appear to affect the Cardinality field of a temporary table (it is still NULL, even with rows added). Only by creating the index itself after the insert seems to make the query faster. I also tried FORCE INDEX and it did not make things any faster. Do you have any idea why creating the index AFTER putting the data in the table affects the performance so dramatically? Allan Gleb Paharenko [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Check whether the problem still exists if you run ANALYZE table after inserts in case you have already created indexes. FORCE_INDEX in the SELECT clause should help as well. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html Allan Miller wrote: In MySQL 4.1.11, if you create two temporary tables with non-unique indices as part of the CREATE TEMPORARY TABLE statement, then insert several hundred (or thousand) rows, a subsequent SELECT using a JOIN between the two tables is substantially slower than if you create the indices after inserting the rows into the temporary tables. When I run a SHOW INDEX for these tables, the only difference is the Cardinality field, where it is NULL if the index was created prior to any inserts and is a number if the index was created after the inserts. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Allan Miller -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: urgent : PLEASE HELP - problems with back up and restore
Thanks Marko :) -Original Message- From: Marko Knezevic [mailto:[EMAIL PROTECTED] Sent: Thursday, November 24, 2005 12:29 AM To: MySQL list Subject: Re: urgent : PLEASE HELP - problems with back up and restore Dear Barak, Save yourself year or two of your life and try using MySQL Front not that command line tools. Its really nice tool for handling MySQL databases Regards Marko -- 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_num_rows
Dear All I have a problem with mysql_num_rows if I execute it on a result returned by mysql_query,,, and that result is empty then I will get an error saying that this result is not a valid mysql result,,, but i think it should return ZERO! I am using count(*) and checking if my SQL statements will return more than 0 results,, but because i am using InnoDB engine, it is very inefficient to use count(*) very much because it will search the whole table any Idea how to solve this? regards - Yahoo! FareChase - Search multiple travel sites in one click.
Server take 20s to connect
Dear Friends I've set up an mysql server with version 4.1.15 by compiling the .src.rpm on an linux machine running conectiva linux 10 with 1.5Gb of RAM on an Pentiun 4 2.88 Gz machine The app I have is running on windows connecting to this linux server by using direct ip address for mysql linux machine, but is taking more then 20s to connect. also no firewall active on linux server all tables are innodb bellow the server config # Example MySQL config file for very large systems. # # This is for a large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 384M #bdb_max_lock = 10 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 384M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M
Re: Server take 20s to connect
Hi Luiz, I've set up an mysql server with version 4.1.15 by compiling the .src.rpm on an linux machine running conectiva linux 10 with 1.5Gb of RAM on an Pentiun 4 2.88 Gz machine The app I have is running on windows connecting to this linux server by using direct ip address for mysql linux machine, but is taking more then 20s to connect. also no firewall active on linux server Sounds like the machine where your MySQL server is running has a broken DNS configuration, or the machine that purports to provide reverse DNS mappings for your client machine is broken. Try using the 'host' command to determine where the problem is. Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dotproject
Have somebody tryed to install dotproject 2.x with MySQL 5.0.x??( The stable release) ++ | ISC Edwin Cruz [EMAIL PROTECTED]| | IT Manager | | Transportes Medel Rogero SA de CV | | Desk: 01 (449) 910 30 90 x3054| | MX Mobile: 01 (449) 111 29 03 | | Skype: softr8 | ++
Re: Mysql compile error
Hi Ryan, Hmm, --without-server doesn't seem to work. Not sure what are you doing wrong, but as a workaround you could define F_TO_EOF to zero in open.c. Quoting sql/my_global.h: #define F_TO_EOF 0L /* Param to lockf() to lock rest of file */ And for linux it is zero (it's different for windows). The define should solve at least this particular compile failure. Petr On Wed, 2005-11-23 at 13:40 -0600, Ryan Stille wrote: cut Making all in isam make[2]: Entering directory `/usr/src/mysql-4.1.14/isam' source='open.c' object='open.o' libtool=no \ depfile='.deps/open.Po' tmpdepfile='.deps/open.TPo' \ depmode=gcc /bin/sh ../depcomp \ gcc -DHAVE_CONFIG_H -I. -I. -I.. -I../include -I../include -O3 -DDBUG_OFF-c open.c open.c: In function `nisam_open': open.c:136: `F_TO_EOF' undeclared (first use in this function) open.c:136: (Each undeclared identifier is reported only once open.c:136: for each function it appears in.) make[2]: *** [open.o] Error 1 make[2]: Leaving directory `/usr/src/mysql-4.1.14/isam' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-4.1.14' make: *** [all] Error 2 -- Petr Chardin, Software Developer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible ways to sort numbers stored in a varchar column?
Is the only way to sort numbers stored in a varchar column to use lpad? Are there any other columntypes that allow both characters and numbers that can sort numbers correct? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: dotproject
I've tryed and I only get a lot of sql errors, looking into the source code I sow that the problem is with JOIN clause. Any ideas abaut how to install it? -Mensaje original- De: ISC Edwin Cruz [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 23 de Noviembre de 2005 05:06 p.m. Para: mysql@lists.mysql.com Asunto: dotproject Have somebody tryed to install dotproject 2.x with MySQL 5.0.x??( The stable release) ++ | ISC Edwin Cruz [EMAIL PROTECTED]| | IT Manager | | Transportes Medel Rogero SA de CV | | Desk: 01 (449) 910 30 90 x3054| | MX Mobile: 01 (449) 111 29 03 | | Skype: softr8 | ++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN not working on 5.0.16 - urgent help
Hi All,
We have upgraded from 4.1.0 to 5.0.16 and notice all left joins not
working. It works fine on 4.1 but 5.0.16 gives us an error:
How to reproduce:
CREATE TABLE `user_master` (
`user_id` int(5) unsigned NOT NULL auto_increment,
`department_id` int(5) default NULL,
`role_id` int(5) unsigned default NULL,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`)
);
CREATE TABLE `role_master` (
`role_id` int(5) unsigned NOT NULL auto_increment,
`role_name` varchar(50) NOT NULL,
PRIMARY KEY (`role_id`),
UNIQUE KEY `role_id` (`role_id`),
KEY `role_id_2` (`role_id`)
);
CREATE TABLE `department_master` (
`department_id` int(5) unsigned NOT NULL auto_increment,
`department_name` varchar(50) NOT NULL,
PRIMARY KEY (`department_id`),
UNIQUE KEY `department_id` (`department_id`),
KEY `department_id_2` (`department_id`)
);
INSERT INTO role_master(role_name) VALUES('Administrator');
INSERT INTO department_master(department_name) VALUES('ITS');
INSERT INTO user_master(department_id,role_id,username)
VALUES('1','1','Joey');
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
1054 - Unknown column 'um.department_id' in 'on clause'
Query:
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id
=
Has the left join syntax changed?
--
Terence Le Grange
Senior IS Executive - ITS
Sunway University College
Email: [EMAIL PROTECTED]
Phone: (+603) 7491 8623 ext. 8078
Website: http://www.sunway.edu.my
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN not working on 5.0.16 - urgent help
On 24/11/2005 2:22 p.m., Terence wrote: SELECT um.username,rm.role_name,dm.department_name FROM user_master um, role_master rm LEFT JOIN department_master dm ON um.department_id = dm.department_id WHERE um.role_id = rm.role_id; 1054 - Unknown column 'um.department_id' in 'on clause' Query: SELECT um.username,rm.role_name,dm.department_name FROM user_master um, role_master rm LEFT JOIN department_master dm ON um.department_id = dm.department_id WHERE um.role_id = rm.role_id = Has the left join syntax changed? Yeah this caught me out too. The precedence of JOINs has changed to more strictly follow the SQL standard. In 5.0, MySQL is now interpreting your query as: SELECT ... FROM user_master um, (role_master rm LEFT JOIN department_master dm ON um.department_id = dm.department_id) ... And the table um doesn't exist in the join between rm and dm. The solution is to put the FROM tables in parentheses, like: SELECT ... FROM (user_master um, role_master rm) LEFT JOIN department_master dm ON um.department_id = dm.department_id) ... You can read more about this here: http://dev.mysql.com/doc/refman/5.0/en/join.html -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot get database connection
Hassan Schroeder wrote:
Gobi wrote:
MySQL 5.0.15 and Tomcat 5.5.12. However, I cannot seem to get a
connection object from my servlet and for the life of me, I can't figure
out why. Here is what I have done:
and I created the following servlet, TestSQL:
It's possible the two items below are transcription errors --
private Connection conn = null;
try {
if (conn == null) {
Class.forName(com.mysql.jdbc.Driver).newInstance();
connW =
? conn != connW :-)
DriverManager.getConnection(jdbc:mysql://localhost/hostname, id,
Shouldn't the hostname above be databasename?
However, correcting those two items, your code works fine on my own
(slightly backlevel: TC 5.5.9 + MySQL 4.1.13+Connector/J 3.1.6) dev
system.
Thanks. Actually, I figured it out last night. It was the hostname
when it should have been the database name.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN not working on 5.0.16 - urgent help
Terence,
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
J
Your query joins rm to dm on a um column = a dm column. Recent 5.0 releases
rightly object to syntactical weirdness like that. You need something
like...
... FROM role_master rm
INNER JOIN user_master um USING (role_id)
LEFT JOIN department_master USING (department_id) ...
PB
-
Terence wrote:
Hi All,
We have upgraded from 4.1.0 to 5.0.16 and notice all left joins not
working. It works fine on 4.1 but 5.0.16 gives us an error:
How to reproduce:
CREATE TABLE `user_master` (
`user_id` int(5) unsigned NOT NULL auto_increment,
`department_id` int(5) default NULL,
`role_id` int(5) unsigned default NULL,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`)
);
CREATE TABLE `role_master` (
`role_id` int(5) unsigned NOT NULL auto_increment,
`role_name` varchar(50) NOT NULL,
PRIMARY KEY (`role_id`),
UNIQUE KEY `role_id` (`role_id`),
KEY `role_id_2` (`role_id`)
);
CREATE TABLE `department_master` (
`department_id` int(5) unsigned NOT NULL auto_increment,
`department_name` varchar(50) NOT NULL,
PRIMARY KEY (`department_id`),
UNIQUE KEY `department_id` (`department_id`),
KEY `department_id_2` (`department_id`)
);
INSERT INTO role_master(role_name) VALUES('Administrator');
INSERT INTO department_master(department_name) VALUES('ITS');
INSERT INTO user_master(department_id,role_id,username)
VALUES('1','1','Joey');
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
1054 - Unknown column 'um.department_id' in 'on clause'
Query:
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id
=
Has the left join syntax changed?
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.6/179 - Release Date: 11/23/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1 password problem
I have a PHP application that stores member-access passwords in a char column. When the passwords were stored, they were written to the database using the PASSWORD() function. Each hashed password is 16 characters long. When a member logs in, the plain text password submitted is run through the PASSWORD() function and compared to the stored hash. After MySQL was upgraded to 4.1.11, the PASSWORD() function now creates a 41 character hash, which of course doesn't match the 16 character hash. I understand it's possible to restart the MySQL server with the --old-passwords option so that PASSWORD() will create the old style short hash, but I'm looking for a way to convert the short hash values into comparable long hash values. (I don't want to ask 1200+ registered users to reset their passwords.) Apparently the upgrade procedure can successfully convert short-to-long hash values for MySQL user passwords (user in the sense of a MySQL user accessing the database itself), so surely there's a way to convert short hashed values to long hashed values for use within a PHP application. Any practical advice greatly appreciated. -- Lowell Allen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible ways to sort numbers stored in a varchar column?
Test USER [EMAIL PROTECTED] wrote on 11/23/2005 07:36:43 PM: Is the only way to sort numbers stored in a varchar column to use lpad? Are there any other columntypes that allow both characters and numbers that can sort numbers correct? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu You can try adding zero to the column. It's a function acting on a value so any chance of using an index is eliminated but you can get your columns as numbers that way. To answer your second question, no. Fields are either strings or numbers but never both. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
I saw both views and stored procedures in the dump file. What error do you get if you process the script with the source command within the MySQL CLI? mysql -uroot -pmypass (login welcome) mysql CREATE DATABASE IF NOT EXISTS bcm; mysql USE bcm; mysql source myback1.sql There is another option to drop/create the database. Your dump didn't have that so you need to do it manually in order to restore it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 07:18:02 PM: Wel I read the manual but didn’t find something that I really need to add I suspect it is something with the script itself. Now I’m using mysqldump -uroot -pmypass --routines -q -x bcmmyback1.sql but after i run mysqldump -uroot -pmypass --routines bcmmyback1.sql it now restore the tables but not the views or sp’s here is the backup file thanks Barak snipped out backup file From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:30 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn’t help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute agoI did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: LEFT JOIN not working on 5.0.16 - urgent help
Terence [EMAIL PROTECTED] wrote on 11/23/2005 08:22:30 PM:
Hi All,
We have upgraded from 4.1.0 to 5.0.16 and notice all left joins not
working. It works fine on 4.1 but 5.0.16 gives us an error:
How to reproduce:
CREATE TABLE `user_master` (
`user_id` int(5) unsigned NOT NULL auto_increment,
`department_id` int(5) default NULL,
`role_id` int(5) unsigned default NULL,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`)
);
CREATE TABLE `role_master` (
`role_id` int(5) unsigned NOT NULL auto_increment,
`role_name` varchar(50) NOT NULL,
PRIMARY KEY (`role_id`),
UNIQUE KEY `role_id` (`role_id`),
KEY `role_id_2` (`role_id`)
);
CREATE TABLE `department_master` (
`department_id` int(5) unsigned NOT NULL auto_increment,
`department_name` varchar(50) NOT NULL,
PRIMARY KEY (`department_id`),
UNIQUE KEY `department_id` (`department_id`),
KEY `department_id_2` (`department_id`)
);
INSERT INTO role_master(role_name) VALUES('Administrator');
INSERT INTO department_master(department_name) VALUES('ITS');
INSERT INTO user_master(department_id,role_id,username)
VALUES('1','1','Joey');
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
1054 - Unknown column 'um.department_id' in 'on clause'
Query:
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id
=
Has the left join syntax changed?
--
Terence Le Grange
Senior IS Executive - ITS
Sunway University College
Email: [EMAIL PROTECTED]
Phone: (+603) 7491 8623 ext. 8078
Website: http://www.sunway.edu.my
The SQL didn't change, the query engine was debugged. That is the error
you should have been getting all along but weren't. Please check the
manual for a full explanation but the short version is that crappy
comma-delimited method of making a CROSS JOIN has been demoted in
evaluation priority (where it should be). Three options:
a) swap the order you list the tables so that user_master appears next to
the LEFT JOIN
b) use parentheses to reprioritize the joins so that user_master CROSS
JOINs to role_master BEFORE you LEFT JOIN to department_master
c) quit using the comma. Use CROSS JOIN instead.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
select command
I have a command : SELECT * FROM reg_one WHERE 'varName' like 'varTmp' % This is made within dreamweaver mx. varName and varTmp is a textselect. Does anyone can help how to make reg_one Becomes to like varName or varTmp ? Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
