Need help in Procedure
Hi All, This is my procedure, i am using cursor to access data one row by one. The cursor is looping twice, though i have only one row data in the DB table(playersloginfo). Can any body tell me why cursor is looping twice and how can i control it, And please explain me what is this declare continue handler for sqlstate '02000' . I will be glad if anybody answer to me. create procedure activity1() begin declare done int default 0; declare pack text; declare name, logout, login varchar(50); set pack=''; declare cur1 cursor for select user_name, concat(logindate, ' ', logintime), concat(logoutdate, ' ',logouttime) from playersloginfo; declare continue handler for sqlstate '02000' set done = 1; open cur1; repeat fetch cur1 into name, login, logout; set pack = concat(pack, name, ',', login, ',', logout,';'); until done end repeat; select pack; close cur1; end;|
problem with Procedure and Cursor
Hi all, I am getting problem with this Procedure, i am reading the values using a cursor, and appending them to variable. but i am getting null as output. Can anybody please tell where is the wrong. create procedure activity1() begin declare done int default 0; declare pack text; declare name, logout, login varchar(50); set pack=''; declare cur1 cursor for select user_name, concat(logindate, ' ', logintime), concat(logoutdate, ' ',logouttime) from playersloginfo; declare continue handler for sqlstate '02000' set done = 1; open cur1; repeat fetch cur1 into name, login, logout; set pack = concat(pack, name, ',', login, ',', logout,';'); until done end repeat; select pack; close cur1; end;|
regional keyboards
Hi All, What data will be inserted if some users use regional keyboards(like Chineese or French characters supported). Is my validation conditations will work for those? I mean recently i observed that some registered users for our application violated validation rules. Some users give single characters as passwords(min 6 need to give) and they did not given email id(email should not be a blank field). What can be the reasone?. The data will be directly inserted to mysql database.
processlist
Hi All, I have some doudts in db connections. Please clarify if u know. I am using MySQL 5 version, while my program(Java Program with threads) is running, some times i won't get DB connection with in expected time. When ever i type show processlist on mysql prompt, it is showing 180+ connections estableshed but almost all are in sleep stage only. Is these many opened connections are delaying a new connection? if yes is there any process to close these sleeping connections?
PREPARE STMT?
Hello everybody, While i am going through the MySQL documentation i found these lines SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; EXECUTE STMT USING @a; can anybody please explain me what is these part mean and also PREPARE STMT.
Query help for Select
Hi All, I need help in this procedure, i am limiting the result to single row by using 'limit' in select statement. But i am using a variable here, mysql is throwing error as i am using variable, please give some suggestions. create procedure Report_login_Activity2() begin declare count,i int DEFAULT 0; set i = 0; select count(*) into count from playersloginfo; while(i = count) do select user_name, concat(logindate, ' ', logintime), concat(logoutdate, ' ', logouttime), TIMEDIFF(concat(logoutdate,' ', logouttime), concat(logindate,' ', logintime)) from playersloginfo *limit i,1;* end while; end;
Re: need a help in Query
Thank you very much Jorn. Your suggestion helped me.
need a select query
Hi All, I need a select query, with which i can reach to a particular row directly. I mean if a table have 100 rows inserted, we can use select * from table1 limit 10; with this query i will have 10 rows, but my requirement is only 10th row only should come as a result. If u have any solution for this please share with me. Thank you. regards, Bala Raju M.
Re: need a select query
Thank you Guys, your answer helpful to me.
need a help in Query
Hi Fello MySQL users, I am Bala Raju, i have a problem to extract data by writing query, i need u r help. Please help me. I have two tables, i am giving the tables as attachement, firs table is playersloginfo and second one is playerhanddetails. playersloginfo : This table stores all the loginsession details of the players, who visited the game. playerhanddetails: This table stores all the games the player played during a session. I need to extract the data from both these tables, i need to display the player's logintime, logouttime and the totaltime per a session(these are available in 1 table) and number of games the player played(from 2 table). Please give some ideas to write this queries. (I tried in this way to extract data, i simple selected all the columns and used time difference function, but to find number of games by a player, i need to use second table(playerhanddetails). I am searching second table by conditions of user_name, and date between (logindate, logoutdate) and time between (logintime, logouttime)) playersloginfo: -- ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | user_name | varchar(20) | YES | | NULL| | | ip | varchar(16) | YES | | NULL| | | action | varchar(15) | YES | | NULL| | | logindate | date| YES | | NULL| | | logoutdate | date| YES | | NULL| | | logintime | time| YES | | NULL| | | logouttime | time| YES | | NULL| | | status | varchar(15) | YES | | NULL| | ++-+--+-+-+---+ playerhanddetails : - ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | playername | varchar(20) | YES | | NULL| | | handnumber | bigint(20) | YES | | NULL| | | date | date| YES | | NULL| | | time | time| YES | | NULL| | ++-+--+-+-+---+
Re: need a help in Query
Hi Jorn, Thank you for reply, of course i can merge the columns and change the datatype. But buddy that is not problem here, the problem is extract the data. Did u able to understand my language, if not i will explain you again. Please read the mail again and tell some ways from u r experience. I am running out of solutions, as i am working on this from last two days.
replacing characters
Hi I have to move the date to one DB to another by live, so i wrote a Java program which will read the data from Sourse DB, and make the data as a Strings which nothing but insert statements with data. I simply add these as a batch and fire them to destination. Everything is fine, if simple data is present. But the problem is if i have any Single Quotes or Back Slashes(\) in the data the insert statement fails. any ideas to run this smooth though there are Sinle Qoutes and Back Slashes in the data. Thank You. Regards, Bala Raju M.
need auto increment value
Hello Everybody, i want to read the latest value of a autoincrement column from a java program. How can i do this? i want to do this in a single query insertion, is it possible?
help needed to mount data from /var/lib/mysql
Hi Everybody, I need small help from you. In my Linux box i have limitation of Size in partition. I have only 5 GB space for /var. MySQL is installed in this partition only. I want to give another path like /home, where i have 120GB of space. Somebody told me that we need to change the data path of MySQL, but he is not sure of that. Can anybody knows abt this?
Re: help needed to mount data from /var/lib/mysql
Hi All, Thank you for u r reply. But i am unable to find my.cnf, is i need to create this file.
Re: help needed to mount data from /var/lib/mysql
I am unable to start server after shifting to new location. i tried to start 'mysqld' but it was failed. A blank mysql.sock file is creating. Entries of log files are also not reporting any problem.
Re: help needed to mount data from /var/lib/mysql
if i search for any process running i am getting following thing. [EMAIL PROTECTED] mysql]# ps -ef | grep mysqld root 18389 1 0 13:09 pts/300:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid mysql18422 18389 0 13:09 pts/300:00:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/home/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/home/mysql/mysql.sock root 18725 15850 0 13:47 pts/300:00:00 grep mysqld i am new to this concept please help me.
comunication between Oracle and MYSQL
Hi All, I need some suggestions from you. I need a comunication between Oracle database with MySQL. In my application there is a situation is arising, where i need to take some data to MySql from a table which is in Oracle database (i am planning to maintain that data in MySQL also). And from MySQL my application will use it. This whole thing should be happen online.That is once some new data was inserted to Oracle table, that should update in MySQL table also. It is totally new situation for me and i am totally confused. Please help me folks. Thanks in advance. regards, Bala Raju Mandala.
replication problem if network delay
Hi All, I need small help from you. I am using Replication, i am using this technique to give live support to Support team. But my Server and Slave are not in LAN. So some times i am getting network delay. Can anybody tell me a good suggestion to over come this. regards, balaraju
Re: concurrency problem
Hi All, Thank you for u r valuable suggestions. I am trying to implement Sequence. As i should not use AUTOINCREMENT, and Locking a table will slower my application. Thank you once again.
concurrency problem
Hi Comunity, I am facing concurrency problem. The scenario is, I have a table, the primary key of that table say 'uid' is a unique number, which is used by my application. So every time i fire a query, i get max(uid) i read this ResultSet from my Java application. I increament this uid to +1. This resulted 'uid' will be updated to the Table. This table is used by more than one thread at any given time. So if some threads fire SQL command at same time, they are getting same uid and result is a duplicate values Exception. I cannot use AUTOINCREMENT. Please anybody have any solution for this?
i want to speed up data retriving at sql side
Hi All, I need some suggestions from you. I need to program a webbased tool, where i use Tomcat as Webserver Mysql as database. In database i have tables which have rows 300,000 to 500,000. I written but the pages are loading very slowly. What i can do in perspictive of database, so that data can be loaded faster. thankyou in advance. regards, Bala Raju M.
what is ibdata1
Hello All, I need some explanations about these files. 1. ibdata1 2. ib_logfile0 3. localhost-bin.index 4. localhost-relay-bin.01 5. master.info 6. relay-log.info is these are standard names. I mean these file names are reserved for mysql? and can be found, where mysql installed. regards, bala
Re: what is ibdata1
Thank you Duncan.
Re: Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
If u need this in urgent, i think my suggestion may help you. try to do replication in this way Branch-A will be update in Branch-B and Branch-B will update in Branch-C . Branch-E(which got total data of A,B,C,D) will update in Main Office.
Re: aha! replication breaking due to duplicate queries
Hi, yes, it is happening when ever some duplicates are loading replication is not moving further. I mean in my experirnce, i stoped my slave from replication for a while, and i forget the exact location where i stoped it. I resetted my binarylog to appropriate file, and position to 0, and started the slave. It is(client) generating a duplicate error message, and io thread is listening but sql thread is stoped. Any other way to solve this problem?
Re: Let's make a difference
Hi, I am conveying my apologies for this mail. I did not read this mail completly and pressed next button, which resulted a mail to all recipeints of my adressbook. I won't repeat this again. bala
Let's make a difference
I'm doing a little something for the earth, and I thought I'd invite you to join me: http://friends.earthscreen.com/?r=ESF0R3gGdhFSBmcNCyIHi=gmail[EMAIL PROTECTED]p=2z=1tc=11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help in updating old binary files to database slave
Hi Comunity, I need some help from you. In replication i want to update old binary files, but server is not updating old files. It is updatin only current using binarylog only. The scenario is, my master is updating log-5 file(say) and slave is reading data from it, and updating itself to current changes. but connection is lost between master and slave. After the connection is established the new log-6 file is update by master but at slave side its only log-5. slave is not updating log-5 though connection is ok. what i have to do so that the old binary file have to be updated in slave. regards, bala
Re: Need help in recreating .MYD files
*Ok Daniel,* ** * Thank you.* ** *regards,* *bala* **
Re: Need help in recreating .MYD files
Hi Dilip, I got two binary logs in Server. I don't know how to find server uptime? mysql show master logs; +--+ | Log_name | +--+ | localhost-bin.08 | | localhost-bin.09 | +--+ 2 rows in set (0.00 sec) if i ask for structure it is giving error message mysql desc userdetails; ERROR 1016 (HY000): Can't open file: 'userdetails.MYI' (errno: 145) if i create .MYD file using vi editor, MySql giving following message mysql desc userdetails; ERROR 1105 (HY000): File './everest/userdetails.MYD' not found (Errcode: 13) but the file is exist. tell me how i can get the data. regards, bala
Re: Need help in recreating .MYD files
Thank you Daniel for reply. Just in curiocity i want ask u, how u r taking daily backups, just using 'mysqldump' or using any Tools. regards, bala
Need help in recreating .MYD files
Dear Comunity, I need your help. I accidently deleted some '.MYD' files. I want to restore them, without stopping the running server. how i can do this. i am using Linux OS, i tried to create file using --- vi tablename.MYD(a blank file) but it is not accepted by MySql. regards, bala
Re: Need help in recreating .MYD files
Hi Dilip, it means i loosed the data, correct Dilip. is there any other way to gain that data, any binary logs etc? regards, bala
Replication between Mysql 4.1.8-standard and MySql 5.0
Hi Comunity, I have a problem while configuring 'Replication'. My Master Database is MySql 4.1.8-standard version, and Slave is 5.0. I want to set Replication between them. Problem here is in MySql 4.1.8-standard version the 'user' table fields are Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv File_priv, Grant_priv, References_priv, Index_priv, Alter_priv. To enable replication as we know, we need to Grant permission to Slave mechine with appropriate permissions.I am unable to understand which field i have to Enable?, so that Replication can start. please help me! regards, bala
Re: commit problem
Thank you again Prassad, and Dilip.
commit problem
Hi, I have a problem with 'commit'. My database contain non transaction tables. When ever i do something wrong on DB, i am unable to rollback. I am setting set sutocommit off, but the result is same, no rollback is happening. Please help me! regards, bala
Re: commit problem
Thank you Prassad, and Dilip for your reply. Prasad, i wiil be pleased if u answer to my query, how i can convert my tables to Innodb? Thank You, regards, bala
mysqldumps from java program
Hi Everybody, I need a suggestion regarding mysqldump. My problem is my application is creatiing around 500Mb of data per day. As i want my application run 24*7*365. I need a mechanisem where i can move, previous day's data to another location(i.e) at any given time i just want to store one or two days data only in my current DB. So i planned to make this by using mysqldump, as u know it will create files which we can upload where ever we need. Is this is a good idea, or we have another better mechanisem? I am planning to automate this using Java. Is we have any prebiuild tools for this?
Re: mysqldumps from java program
Thank you guys, let me try in this way, i will come back if i get any problems. On 4/19/06, balaraju mandala [EMAIL PROTECTED] wrote: Hi Everybody, I need a suggestion regarding mysqldump. My problem is my application is creatiing around 500Mb of data per day. As i want my application run 24*7*365. I need a mechanisem where i can move, previous day's data to another location( i.e) at any given time i just want to store one or two days data only in my current DB. So i planned to make this by using mysqldump, as u know it will create files which we can upload where ever we need. Is this is a good idea, or we have another better mechanisem? I am planning to automate this using Java. Is we have any prebiuild tools for this?
Re: upgrading problem
Yes Eric You are correct. Thankyou for your reply. It helped me. Bala
upgrading problem
Hi Comunity, I am getting problem while i am upgrading to MySql ver 4 to MySql ver 5. I planned first to install MySQL-shared-compat-5.0.19-0.rhel4.i386.rpm, but i am getting following error. Preparing...### [100%] 1:MySQL-shared-compat### [100%] error: unpacking of archive failed on file /usr/lib/libmysqlclient.so;44361c01: cpio: symlink failed - Permission denied i am unable to understand the problem. Please help me.
Re: need to select correct package
I am planning to upgrade mysql ver 4 to mysql ver 5 but i am getting this errors [EMAIL PROTECTED] trinity]$rpm -Uvh MySQL-server-standard-5.0.19-0.rhel4.i386.rpm warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: libmysqlclient.so.14 is needed by (installed) mod_auth_mysql- 2.6.1-2.2.i386 libmysqlclient.so.14 is needed by (installed) cyrus-sasl-sql-2.1.19-5.EL4.i386 libmysqlclient.so.14 is needed by (installed) dovecot-0.99.11-2.EL4.1.i386 libmysqlclient.so.14 is needed by (installed) perl-DBD-MySQL-2.9004-3.1.i386 libmysqlclient.so.14 is needed by (installed) php-mysql-4.3.9-3.8.i386 Please can anybody help me
need to select correct package
Hi Comunity, I have some probleme for selecting correct package of mysql software from download section in the site. I am confused which Linux version i have to use, as there are different packages. please help me.
Re: need to select correct package
Hi DK, The user -a output is : Linux wizon.secureserver.net 2.6.9-11.EL #1 Fri May 20 18:17:57 EDT 2005 i686 i6 I didn't understand what is secound command is. On 4/6/06, Dominik Klein [EMAIL PROTECTED] wrote: please read what i wrote and do so - I cannot help you without this info as I do not know any Linux Enter prise ver4 please post your replies to the mysql mailing list, not to my email-address balaraju mandala schrieb: Hi DK, I just want use MySql for personal use. But i am confused which Linux package i have to download as there are different packages. I have a system(Pentium4-HT) loaded with Linux Enter prise ver4 or i can load Linux Enter prise ver3.
Re: need to select correct package
Thank You, Thank you very much for your help. Sorry for mailing personally,