MySQL Forums - QA

I need your help very much (no replies)

Gabby Takash — Sat, 21 Nov 2009 14:26:38 +0000

Dear Database Support Professional,

I am Database Administrator and I am writing to ask for your help in responding to a 10-12 minute survey for a degree requirement on relational database support professionals in the USA. Please note that the respondent is completely anonymous. If I don’t get this portion of my degree completed by the end of this semester, then I would have to wait for another year which entails additional costs and time on me. By completing the survey, you will be contributing to research in our field and also there is the satisfaction of having helped a fellow professional and a fellow human being attain his goals. I believe a good turn always bring another in some way in our lives. I therefore kindly beg for your help to a fellow database professional by completing this survey. The link to the survey is provided below.

http://www.surveymonkey.com/s.aspx?sm=a1JqU7GVt1g6rCkIPyL23w_3d_3d

Thanks you very much
Gabriel

A truly terrific software test management tool (no replies)

Sean Stewart — Sat, 21 Nov 2009 04:56:47 +0000

Hello Everyone,

Zephyr 2.6 is a terrific tool for QAs. Can anyone suggest any other system that has tight integration with two popular issue tracking systems (JIRA and Bugzilla), a flexible licensing option and above all an affordable test management system? Well I can vouch for it as I’ve been using it for some time. It has never given me a chance to complain and all my releases were on time and on budget. Trust me, Zephyr 2.6 never lets you down. Get more details here: http://www.getzephyr.com/

corrupted entry (no replies)

Schuyler Bos — Fri, 20 Nov 2009 15:28:58 +0000

Hi, I have a MySQL database version 4.1.22, that has an entry in it causing an issue. I had a statement using SELECT * that bugged when a person injected hmtl tags into input boxes using a bot. I closed the open ended part of the site so this couldn't happen but SELECT * causes the connection to be lost whenever I use the query in that manner. if I select specific columns out of the row I can get information to display, I'm assuming that's because only one of the columns in the bad row is causing it. I narrowed down a row I know is not working since I tried to select it out individually and it fails. I can delete other records expect the bad record. It'll disconnect if I try to delete it. I could find out the specific column/columns that are bad by selecting each column out of the row until it gives me the disconnect if that information is necessary. If I can't delete this record using a query from the command line even, is there any other way to get rid of this record?

InnoDB lock up in 5.4.3 (no replies)

mike grafton — Thu, 19 Nov 2009 02:45:59 +0000

Moved to InnoDB forum...

unable to load its own output (no replies)

stephen bond — Wed, 18 Nov 2009 21:54:46 +0000

select ... into outfile

produces a text file with '\\N' in place of the nulls.
when trying to read back using 'load data infile' it stubbornly refuses to accept the null values and even explicit escaped by '\\' or '\\\\' don't help.

I discovered it was confused by missing vals in the last column only and adding lines terminated by '\r\n' made it recognize the nulls fine.

A bit strange.

suggestion: if select ... into outfile is called without options then load data infile should also work without options on the file created.

Issue with database being 'hacked' (no replies)

James Coffman — Wed, 18 Nov 2009 16:21:04 +0000

Hello all,
Recently my companys website was hacked after we switched to a new hosting company. The string that was used to hack the database was:

ss&item=-1%20union%20all%20select%201,2,concat(username,char(58),password),4,5,6%20from%20users--

Now I did not write the code for the website, only thing I have done was changed some code and redesigned it. Does anyone have any insight on this issue and/or know of somewhere that I can find a fix to this? This command is displaying the usernames and password hashes for all accounts. I am not extremely new to using MySQL but I am fairly new to it. Any help would be greatly appreciated and thank you in advance

multiple keywords search (no replies)

vino kumar — Tue, 17 Nov 2009 14:07:19 +0000

anyone give me idea for my question?
my question is, im using text filed :country, state,city gender, agefrom, ageTo.

suppose i will give country and state field then output will display. suppose i will give any of three fileld. so user may chance to give watever they want to search.

i need query for these pls help ..

problem: SELECT returning everytime empty resultset (no replies)

Gaya Rime — Mon, 16 Nov 2009 19:26:42 +0000

Hello,

Today we had a very strange problem that occured on a customer's production system using Linux (Fedora 4) + MySQL 4.1.

The MySQL server started to returns empty results set for any SELECT statements we entered (a single statement SELECT * FROM mytable was returning data - but now returns nothing). No error in the MySQL client and no error in the log files.

Let me add that the database (isam db) is quite big ~2gb of data (+2gb of indexes),
but everything was working fine up to that point...

finally we restarted the whole server and the system works well again.

do you have any idea about what can cause such behaviour ?
thanks for helping

gaya

Mysql Table Corruption (1 reply)

Kadir Sert — Mon, 23 Nov 2009 10:01:42 +0000

Hi,

I have MySql Server 5.0.27 and occasionally a table is corrupted. Every time the table corrupted I repair the table in extended mode. It is repaired at that time, but it's not a permanent solution. Corruption is still occurring.

The table has 1 million rows and occupy 3GB disk space. I think it is not big for Mysql. Have you any idea to solve this problem? Any help appreciated.

NEED HELP (no replies)

donald nwaokobia — Wed, 11 Nov 2009 09:26:12 +0000

i,
my comapny runs an application with mysql as backend.. Of recent every evening when you try to login it gives u the error 'unable to connect to server please check your connection'. If you shut down and restart hours later, it runs perfectly. if u ping d server when it generates the error, you get a reply and if you input a wrong username/password, it tells you invalid username or password.
Has anyone got an idea of what is happening..
thanks

MySQL 5.1.40 don´t accept datetime 21/10/2001 00:00:00 (no replies)

Carlos Junior — Tue, 10 Nov 2009 12:43:58 +0000

Hello,

When i try insert an data in the table 'check' the mysql throws an exception with the message:

Incorrect datetime value: '2001-10-21 00:00:01' for column 'descount_date' at row 1.

The field descount_date is a datetime, and in my sql insert command is described below:

insert into check (description, decount_date) values ('sample', '2001-10-21 00:00:01');

Bug on Sum aggregate function (no replies)

Christopher Pelayo — Sun, 08 Nov 2009 12:50:52 +0000

Hello Everyone!

I have a particular view created in mysql which return about 3 columns in all here are the actual data that it returns with the query that I used to return this values

"SELECT invoice, task_no, computation FROM payments p where invoice='0600985' order by task;"

invoice | task_no |computation
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 364.93
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 741.12
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 12
------------------------------------------------------------
0600985 | 1556e08b-9610-11de-ad92-5e47499a2b08 | 216
------------------------------------------------------------
0600985 | 1556e08b-9610-11de-ad92-5e47499a2b08 | 494.32
------------------------------------------------------------
0600985 | 1556e08b-9610-11de-ad92-5e47499a2b08 | 1296
------------------------------------------------------------
0600985 | 2f0da371-960c-11de-ad92-5e47499a2b08 | 2.16
------------------------------------------------------------
0600985 | 2f0da371-960c-11de-ad92-5e47499a2b08 | 65.69
------------------------------------------------------------
0600985 | 2f0da371-960c-11de-ad92-5e47499a2b08 | 132.62
------------------------------------------------------------
0600985 | 21e6d02c-960c-11de-ad92-5e47499a2b08 | 123.16
------------------------------------------------------------
0600985 | 21e6d02c-960c-11de-ad92-5e47499a2b08 | 250.09

The data above are having same invoice and some are having same task_no what I did was I group them by task_no before performing sum aggregate function and it return the following results:

SELECT task_no, invoice, sum(computation) as computation FROM payments group by task_no having invoice='0600985';

invoice | task_no |computation
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 1288.63
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 2006.32
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 373.25
------------------------------------------------------------
0600985 | 1556e08b-9610-11de-ad92-5e47499a2b08 | 200.47

I've tried to make a manual computation for this one as I have observed the first row returns a wrong computation of data. Getting the sum of the values 364.93, 741.12 and 12 when being computed should be returning a result of 1118.05 getting their sum. I would like also to show how the view is being generated here it is:

"CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `payments` AS select `job_order`.`invoice_i_no` AS `invoice`, task_assign.ta_id as task_no, t_desc as task, `formula`.`f_name` AS `fieldname`,`computations`.`VAL` AS `computation`,`computations`.`formula_f_id` AS `fieldid`,`formula`.`FTYPE` AS `fieldtype` from ((`computations` left join `job_order` on((`computations`.`job_order_jo_id` = `job_order`.`jo_id`))) left join `formula` on((`computations`.`formula_f_id` = `formula`.`f_id`))) left join task_assign on job_order.task_assign_ta_id = task_assign.ta_id where `formula`.`FTYPE` = 'T';"

I can't tell whether I actually made a mistake in my query but I also tried doing another query and get the following results:

"SELECT invoice, task_no, sum(computation) as computation FROM payments p where invoice='0600985' and task_no='01613d90-9610-11de-ad92-5e47499a2b08' order by task;"

invoice |task_no |computation
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 1118.05

I'm actually using the "innodb" engine for all the tables that are included in the join statement of my query.

Hope anyone from this group would be able to help me. Thanks in advance!;)

Bug on Sum aggregate function (no replies)

Christopher Pelayo — Sun, 08 Nov 2009 12:43:48 +0000

Hello Everyone!

I have a particular view created in mysql which return about 3 columns in all here are the actual data that it returns with the query that I used to return this values

"SELECT invoice, task_no, computation FROM payments p where invoice='0600985' order by task;"

invoice | task_no|computation
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 364.93
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 741.12
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 12
------------------------------------------------------------
0600985 | 1556e08b-9610-11de-ad92-5e47499a2b08 | 216
------------------------------------------------------------
0600985 | 1556e08b-9610-11de-ad92-5e47499a2b08 | 494.32
------------------------------------------------------------
0600985 | 1556e08b-9610-11de-ad92-5e47499a2b08 | 1296
------------------------------------------------------------
0600985 | 2f0da371-960c-11de-ad92-5e47499a2b08 | 2.16
------------------------------------------------------------
0600985 | 2f0da371-960c-11de-ad92-5e47499a2b08 | 65.69
------------------------------------------------------------
0600985 | 2f0da371-960c-11de-ad92-5e47499a2b08 | 132.62
------------------------------------------------------------
0600985 | 21e6d02c-960c-11de-ad92-5e47499a2b08 | 123.16
------------------------------------------------------------
0600985 | 21e6d02c-960c-11de-ad92-5e47499a2b08 | 250.09

The data above are having same invoice and some are having same task_no what is did was I group them by task_no before performing sum aggregate function and it return the following results:

SELECT task_no, invoice, sum(computation) as computation FROM payments group by task_no having invoice='0600985';

invoice | task_no |computation
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 1288.63
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 2006.32
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 373.25
------------------------------------------------------------
0600985 | 1556e08b-9610-11de-ad92-5e47499a2b08 | 200.47

I've tried to make a manual computation for this one as I have observed the first row returns a wrong computation of data. Getting the sum of the values 364.93, 741.12 and 12 when being computed should be returning a result of 1118.05 getting their sum. I would like also to show how the view is being generated here it is:

"CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `payments` AS select `job_order`.`invoice_i_no` AS `invoice`, task_assign.ta_id as task_no, t_desc as task, `formula`.`f_name` AS `fieldname`,`computations`.`VAL` AS `computation`,`computations`.`formula_f_id` AS `fieldid`,`formula`.`FTYPE` AS `fieldtype` from ((`computations` left join `job_order` on((`computations`.`job_order_jo_id` = `job_order`.`jo_id`))) left join `formula` on((`computations`.`formula_f_id` = `formula`.`f_id`))) left join task_assign on job_order.task_assign_ta_id = task_assign.ta_id where `formula`.`FTYPE` = 'T';"

I can't tell whether I actually made a mistake in my query but I also tried doing another query and get the following results:

"SELECT invoice, task_no, sum(computation) as computation FROM payments p where invoice='0600985' and task_no='01613d90-9610-11de-ad92-5e47499a2b08' order by task;"

invoice |task_no |computation
------------------------------------------------------------
0600985 | 01613d90-9610-11de-ad92-5e47499a2b08 | 1118.05

As you can observed the last query already returned the correct result that I expect to get. Please help me to solve this problem. I'm actually using the "innodb" engine for all the tables that are included in the join statement of my query.

MySQL Appears to be in Deadlocked State (no replies)

Richard Fox — Wed, 04 Nov 2009 16:33:25 +0000

Hello,

I am running into a deadlock condition that I cannot diagnose. I am using an InnoDB engine with auto commit. Specifically, I am running an application which attempts to do an insert into a table. I keep getting the following error (after a 50 second timeout):

"java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction"

I printed the prepared statement via "toString" and attempted to run this command in the mysql application, I get a similar error:

"ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction"

I looked through the app and found no database locks being set. However, SELECT statements do work.

This leads to my first question: With the row level locking that InnoDB provides, why lock the entire table?

Running "show full processlist" yields the following results:

mysql> show full processlist;
+-----+---------+-----------------+---------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+---------+-----------------+---------+---------+------+-------+-----------------------+
| 1 | ipunity | localhost:37081 | ipunity | Sleep | 548 | | NULL |
| 2 | ipunity | localhost:37082 | ipunity | Sleep | 248 | | NULL |
| 3 | ipunity | localhost:37083 | ipunity | Sleep | 248 | | NULL |
| 4 | ipunity | localhost:37086 | ipunity | Sleep | 7 | | NULL |
| 8 | cb | localhost:37110 | cb | Sleep | 196 | | NULL |
| 9 | cb | localhost:37112 | cb | Sleep | 196 | | NULL |
| 10 | cb | localhost:37113 | cb | Sleep | 16 | | NULL |
| 11 | cb | localhost:37114 | cb | Sleep | 5302 | | NULL |
| 12 | cb | localhost:37115 | cb | Sleep | 5302 | | NULL |
| 13 | cb | localhost:37118 | cb | Sleep | 4696 | | NULL |
| 14 | cb | localhost:37122 | cb | Sleep | 4696 | | NULL |
| 15 | cb | localhost:37123 | cb | Sleep | 4516 | | NULL |
| 16 | cb | localhost:37124 | cb | Sleep | 3796 | | NULL |
| 17 | cb | localhost:37125 | cb | Sleep | 3796 | | NULL |
| 18 | cb | localhost:37126 | cb | Sleep | 3616 | | NULL |
| 19 | cb | localhost:37128 | cb | Sleep | 2896 | | NULL |
| 20 | cb | localhost:37129 | cb | Sleep | 2896 | | NULL |
| 21 | cb | localhost:37131 | cb | Sleep | 2716 | | NULL |
| 22 | cb | localhost:37132 | cb | Sleep | 1996 | | NULL |
| 23 | cb | localhost:37133 | cb | Sleep | 1996 | | NULL |
| 24 | cb | localhost:37134 | cb | Sleep | 1096 | | NULL |
| 25 | cb | localhost:37135 | cb | Sleep | 1816 | | NULL |
| 26 | cb | localhost:37138 | cb | Sleep | 5415 | | NULL |
| 27 | cb | localhost:37139 | cb | Sleep | 1096 | | NULL |
| 28 | cb | localhost:37141 | cb | Sleep | 916 | | NULL |
| 29 | cb | localhost:37148 | cb | Sleep | 5412 | | NULL |
| 30 | cb | localhost:37149 | cb | Sleep | 5412 | | NULL |
| 31 | cb | localhost:37150 | cb | Sleep | 5412 | | NULL |
| 32 | cb | localhost:37151 | cb | Sleep | 5607 | | NULL |
| 33 | cb | localhost:37152 | cb | Sleep | 5607 | | NULL |
| 34 | cb | localhost:37153 | cb | Sleep | 5607 | | NULL |
| 35 | cb | localhost:37154 | cb | Sleep | 5607 | | NULL |
| 36 | cb | localhost:37155 | cb | Sleep | 5415 | | NULL |
| 37 | cb | localhost:37156 | cb | Sleep | 5415 | | NULL |
| 47 | cb | localhost:37233 | cb | Sleep | 881 | | NULL |
| 48 | cb | localhost:37254 | cb | Sleep | 5498 | | NULL |
| 49 | cb | localhost:37255 | cb | Sleep | 5495 | | NULL |
| 50 | cb | localhost:37256 | cb | Sleep | 5499 | | NULL |
| 53 | cb | localhost:37259 | cb | Sleep | 5493 | | NULL |
| 54 | cb | localhost:37260 | cb | Sleep | 5492 | | NULL |
| 55 | cb | localhost:37261 | cb | Sleep | 5499 | | NULL |
| 56 | cb | localhost:37262 | cb | Sleep | 5499 | | NULL |
| 57 | cb | localhost:37263 | cb | Sleep | 5498 | | NULL |
| 107 | root | localhost | cb | Sleep | 4569 | | NULL |
| 156 | cb | localhost:38461 | cb | Sleep | 3520 | | NULL |
| 157 | cb | localhost:38462 | cb | Sleep | 3520 | | NULL |
| 158 | cb | localhost:38463 | cb | Sleep | 3520 | | NULL |
| 159 | cb | localhost:38464 | cb | Sleep | 3520 | | NULL |
| 160 | cb | localhost:38465 | cb | Sleep | 3520 | | NULL |
| 161 | cb | localhost:38466 | cb | Sleep | 3520 | | NULL |
| 162 | cb | localhost:38467 | cb | Sleep | 3520 | | NULL |
| 163 | cb | localhost:38468 | cb | Sleep | 3520 | | NULL |
| 164 | cb | localhost:38469 | cb | Sleep | 3520 | | NULL |
| 165 | cb | localhost:38470 | cb | Sleep | 3520 | | NULL |
| 301 | root | localhost | cb | Query | 0 | NULL | show full processlist |
+-----+---------+-----------------+---------+---------+------+-------+-----------------------+
55 rows in set (0.00 sec)

From this it is evident that there is no locks by any of the aforementioned processes.

This leads me to my second question: Is the error message misleading? Or how can I tell if a table is locked?

I ran "show open tables" and got the following results:

mysql> show open tables;
+----------+-------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------------------------------+--------+-------------+
| cb | orgid_sequence | 0 | 0 |
| cb | staleuserpasswordsequence | 0 | 0 |
| cb | recurrentstreaminginfo | 0 | 0 |
| cb | slidepresentationbackup | 0 | 0 |
| cb | orgpilotnumberid_sequence | 0 | 0 |
| cb | operatorid_sequence | 0 | 0 |
| cb | inconfinfo | 0 | 0 |
| cb | recurrentreservation | 0 | 0 |
| ipunity | frameconfig | 0 | 0 |
| ipunity | healthmonitor_globalconfig | 0 | 0 |
| cb | userparams | 0 | 0 |
| ipunity | cluster_appcallstats | 0 | 0 |
| cb | streamingpartid_sequence | 0 | 0 |
| cb | streamingparticipant | 0 | 0 |
| cb | multimediaserver | 0 | 0 |
| cb | prereggrouppasscodes_seq | 0 | 0 |
| cb | memberid_sequence | 0 | 0 |
| mysql | proc | 0 | 0 |
| cb | numbercodeslist | 0 | 0 |
| cb | question | 0 | 0 |
| cb | orgparams | 0 | 0 |
| cb | orgpilotnumber | 0 | 0 |
| cb | dnisrecordings | 0 | 0 |
| cb | numberingplan | 0 | 0 |
| cb | mediaserverid_sequence | 0 | 0 |
| cb | recurrentattendeeid_sequence | 0 | 0 |
| cb | streamingsession | 0 | 0 |
| cb | numberingplanid_sequence | 0 | 0 |
| cb | prereggroupinfo_seq | 0 | 0 |
| cb | operatorsystemqueue | 0 | 0 |
| cb | streaminginfo | 0 | 0 |
| cb | recurstreaminginfoid_sequence | 0 | 0 |
| cb | participant | 0 | 0 |
| cb | prereggroupinfo | 0 | 0 |
| cb | pollid_sequence | 0 | 0 |
| cb | slidepresentation | 0 | 0 |
| cb | presentationid_sequence | 0 | 0 |
| cb | participantbackup | 0 | 0 |
| cb | recurrentattendeeinfo | 0 | 0 |
| cb | systemqueueid_sequence | 0 | 0 |
| cb | votingid_sequence | 0 | 0 |
| cb | inslideconf | 0 | 0 |
| cb | subsystemhostinfo | 0 | 0 |
| cb | numbercodelistid_sequence | 0 | 0 |
| cb | poll | 0 | 0 |
| cb | resourcetracker | 0 | 0 |
| cb | responseid_sequence | 0 | 0 |
| cb | operatorassistinfo | 0 | 0 |
| cb | questionid_sequence | 0 | 0 |
| cb | recurrentstreamingparticipant | 0 | 0 |
| cb | prereggrouppasscodes | 0 | 0 |
| cb | streaminginfoid_sequence | 0 | 0 |
| cb | systemmessageid_sequence | 0 | 0 |
| cb | confreservbackup | 0 | 0 |
| cb | recurstreamingpartid_sequence | 0 | 0 |
| cb | staleuserpasswords | 0 | 0 |
| cb | response | 0 | 0 |
| cb | confreservation | 0 | 0 |
| ipunity | mandatory_header_check | 0 | 0 |
| cb | systemmessages | 0 | 0 |
| cb | participantbackupid_sequence | 0 | 0 |
+----------+-------------------------------+--------+-------------+
61 rows in set (0.00 sec)

From this output, you can see that there are no open tables or locks. It appears that the error is misleading.

The table that I am using is defined as:

create table inconfinfo
(
conferenceid VARCHAR(32) NOT NULL,
reservnumport DECIMAL(38),
reservstarttime DATETIME,
confduration DECIMAL(38),
islockconf DECIMAL(38),
scpuconfid DECIMAL(38),
actstarttime DATETIME,
actendtime DATETIME,
meetingtype DECIMAL(38),
confvolume DECIMAL(38),
isrecordstate DECIMAL(38),
iscontwithoutmoderator DECIMAL(38),
meetingname VARCHAR(180),
meetingagenda VARCHAR(512),
isconfactive DECIMAL(38),
iswhisperused DECIMAL(38),
islectureused DECIMAL(38),
israisedhandused DECIMAL(38),
isdataloggedinused DECIMAL(38),
isrecordused DECIMAL(38),
islockedused DECIMAL(38),
isvolumechangeused DECIMAL(38),
confreservid DECIMAL(38) NOT NULL,
inconfbackupid DECIMAL(38),
orgid DECIMAL(38),
isrecurring DECIMAL(38),
initactivenumport DECIMAL(38),
maxpassnumport DECIMAL(38),
mediaserverid DECIMAL(38),
mediaserverlocalename VARCHAR(255),
mediaserverhostname VARCHAR(255),
namerecordingdirname VARCHAR(255),
conferencedirectory VARCHAR(256),
namerecordingenabled DECIMAL(38),
entryoption DECIMAL(38),
exitoption DECIMAL(38),
entrygenannfilename VARCHAR(256),
exitgenannfilename VARCHAR(256),
talkindicator NUMERIC,
refreshtime NUMERIC,
confmode DECIMAL(38),
videotemplate VARCHAR(256),
streamingsessionid DECIMAL(38),
streamingurl VARCHAR(256),
streamingserverip VARCHAR(256)
) TYPE=InnoDB;

The statement that keeps locking up is:
INSERT INTO INCONFINFO (INCONFINFO.CONFERENCEID, INCONFINFO.RESERVNUMPORT, INCONFINFO.RESERVSTARTTIME, INCONFINFO.CONFDURATION, INCONFINFO.ISLOCKCONF, INCONFINFO.ACTSTARTTIME, INCONFINFO.ACTENDTIME,INCONFINFO.MEETINGTYPE, INCONFINFO.CONFVOLUME, INCONFINFO.ISRECORDSTATE, INCONFINFO.ISCONTWITHOUTMODERATOR, INCONFINFO.CONFRESERVID, INCONFINFO.ORGID, INCONFINFO.MEETINGNAME, INCONFINFO.MEETINGAGENDA, INCONFINFO.ISRECURRING, INCONFINFO.INITACTIVENUMPORT, INCONFINFO.MAXPASSNUMPORT, INCONFINFO.NAMERECORDINGENABLED, INCONFINFO.CONFERENCEDIRECTORY, INCONFINFO.ENTRYOPTION, INCONFINFO.EXITOPTION, INCONFINFO.ENTRYGENANNFILENAME, INCONFINFO.EXITGENANNFILENAME, INCONFINFO.CONFMODE, INCONFINFO.VIDEOTEMPLATE) VALUES ( '101', 6, '2009-11-04 09:44:29', 720, 0, '2009-11-04 09:44:29', '2009-11-04 21:44:29', 2, 3, 0, 0, 2, 3, ' ', ' ', 0, -1, -1, 0, null, 0, 0, null, null, 1, '8_Region_Layout');

The command used to do insert (code snippet):

try
{
conn.inConfInsertStmt.setString(1, confid);
conn.inConfInsertStmt.setInt(2, numOfReservPorts);
conn.inConfInsertStmt.setTimestamp(3, reservStartTime);
conn.inConfInsertStmt.setInt(4, duration);
conn.inConfInsertStmt.setInt(5, lockConf);
// conn.inConfInsertStmt.setInt(6, scpuConfId);
conn.inConfInsertStmt.setTimestamp(6, confActStartTime);
conn.inConfInsertStmt.setTimestamp(7, confActEndTime);
conn.inConfInsertStmt.setInt(8, meetingType);
conn.inConfInsertStmt.setInt(9, confVolume);
conn.inConfInsertStmt.setInt(10, recordState);
conn.inConfInsertStmt.setInt(11, contWithOutMod);
conn.inConfInsertStmt.setLong(12, confReservId);
conn.inConfInsertStmt.setInt(13, orgId);
conn.inConfInsertStmt.setString(14, meetingName);
conn.inConfInsertStmt.setString(15, meetingAgenda);
conn.inConfInsertStmt.setInt(16, isRecurring);
conn.inConfInsertStmt.setInt(17, initActiveNumPort);
conn.inConfInsertStmt.setInt(18, maxPassNumPort);
conn.inConfInsertStmt.setInt(19, isRecordNameEnabled);
conn.inConfInsertStmt.setString(20, conferenceDir);
conn.inConfInsertStmt.setInt(21, entryOptions);
conn.inConfInsertStmt.setInt(22, exitOptions);
conn.inConfInsertStmt.setString(23, entryGenAnnFileName);
conn.inConfInsertStmt.setString(24, exitGenAnnFileName);
conn.inConfInsertStmt.setInt(25, confMode ? 1 : 0);
conn.inConfInsertStmt.setString(26, videoTemplateID);

int result = conn.inConfInsertStmt.executeUpdate();

.....

With inConfInsertStmt defined as:

this.inConfInsertStmt = prepareStatement(inConfInsertQuery);

protected static final String inConfInsertQuery =
"INSERT INTO INCONFINFO (INCONFINFO.CONFERENCEID, INCONFINFO.RESERVNUMPORT, "
+ "INCONFINFO.RESERVSTARTTIME, INCONFINFO.CONFDURATION, INCONFINFO.ISLOCKCONF, "
+ "INCONFINFO.ACTSTARTTIME, INCONFINFO.ACTENDTIME,"
+ "INCONFINFO.MEETINGTYPE, INCONFINFO.CONFVOLUME, "
+ "INCONFINFO.ISRECORDSTATE, INCONFINFO.ISCONTWITHOUTMODERATOR, "
+ "INCONFINFO.CONFRESERVID, INCONFINFO.ORGID, INCONFINFO.MEETINGNAME, INCONFINFO.MEETINGAGENDA, "
+ "INCONFINFO.ISRECURRING, INCONFINFO.INITACTIVENUMPORT, INCONFINFO.MAXPASSNUMPORT, "
+ "INCONFINFO.NAMERECORDINGENABLED, INCONFINFO.CONFERENCEDIRECTORY, "
+ "INCONFINFO.ENTRYOPTION, INCONFINFO.EXITOPTION, "
+ "INCONFINFO.ENTRYGENANNFILENAME, INCONFINFO.EXITGENANNFILENAME, INCONFINFO.CONFMODE, "
+ "INCONFINFO.VIDEOTEMPLATE) "
+ "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Thanks in Advance

ORDER BY RAND() in subqueries (no replies)

Jeremy D — Thu, 29 Oct 2009 21:06:31 +0000

I noticed when I used a sub query that has a ORDER BY RAND() in it, that the returned results are not random. Is this a bug or something undocumented (or not easily found) that mysql doesn't support?

My test query:
SELECT id_topic
FROM topics
WHERE id_topic IN (
SELECT id_topic
FROM topics
ORDER BY RAND()
);

My results always return:
1
2
3
4
etc..

The subquery ran alone returns random results:
412
19
871
121
etc..

Tables corrupted..repair taking long time. (no replies)

ranjit woonna — Thu, 29 Oct 2009 10:19:29 +0000

Hi,

Few tables in the database got corrupted which i found out from check table command.
I am using Mysql version 5.1.30 (Myisam tables) i have given repair table command its taking long time and its failing. Is there any other way to recover the table.

Regards,
Ranjit

stored procedure with parameter problem (no replies)

riyas kp — Mon, 26 Oct 2009 12:03:16 +0000

I had created following procedure.

DELIMITER ;;

DROP PROCEDURE IF EXISTS getAllPortfoliosDemo;;

CREATE PROCEDURE getAllPortfoliosDemo( IN keyid INT(10))
BEGIN
DECLARE whereString char(100);

IF (keyid > 0 ) THEN
SET whereString = CONCAT( ' WHERE pkid = ', keyid );
ELSE
SET whereString = ' WHERE 1 ';
END IF;

SELECT pkid, title FROM Portfolio whereString;

END ;;

this query returns no error. it works well without parameters.
for eg CALL getAllPortfoliosDemo(); works. but it didn't works with a parameter. for eg
CALL getAllPortfoliosDemo(5); didn't returns any row.

also i tried the following alternate query

DELIMITER ;;

DROP PROCEDURE IF EXISTS getAllPortfoliosDemo;;

CREATE PROCEDURE getAllPortfoliosDemo( IN keyid INT(10))
BEGIN
DECLARE whereString char(100) DEFAULT NULL;

IF (keyid > 0 ) THEN
SET whereString = CONCAT( ' AND pkid = ', keyid );
END IF;

SET @SQLstmt = CONCAT('SELECT pkid, title FROM Portfolio ', whereString) ;

PREPARE SQLbase FROM @SQLstmt;
EXECUTE SQLbase;
DEALLOCATE PREPARE SQLbase;

END ;;

this also didn't returns any result set. any one can a sagest a method. thanks in advance

[HELP] - Database corruption (1 reply)

Luis Azevedo — Mon, 26 Oct 2009 11:00:54 +0000

Hi

I had an power outage and after that it seems one of my databese as became corrupted. It shows an abnormal behaviour.

1 - any other DB on the same server shows no problem. I can open them and all the tables.

2 - when i try to acess my database (however i try - mysqladministrator or phpmyadmin) the mysql server on the server machin stops and i have to restart a mysql instance.

if i acess a table, from this database, directly (using php scripts or using phpmyadmin - by changing the url) i can acess it and see all my data.

i believe there was and corruption on the database when the power was cut.
I have tried searching topics on database corruption but i can find only help in table corruption, using a CHECK TABLE SQL statement, mysqlcheck or myisamchk not on the database itself.

Is there anything i can try to repair my database?

Logic behind rand() (1 reply)

Steve Kim — Thu, 22 Oct 2009 14:11:14 +0000

I'm using the rand() function to perform a random pick of a contest submission, so I need to document how the rand() function actually "calculates" a random number that can be used to pick out a random record through a query like the following:

SELECT *
FROM table
order by rand()
limit 1

Can anyone explain the logic for me?

Thank you

Logic behind rand() (no replies)

Steve Kim — Wed, 21 Oct 2009 16:21:44 +0000

Problem with site (no replies)

Jorge Lopes — Tue, 20 Oct 2009 13:55:09 +0000

Hello,

I'm having problems in my site, to access the site and then click on something within the page will open my page with white background to say "501 Method Not Implemented" and where the site is link http://kipolon.com/ld/prox/

A bit weird.. be virus?

Somebody help me sff.

Thanks

String used to test INT column in WHERE is truncated (no replies)

Allan Randall — Tue, 20 Oct 2009 12:13:47 +0000

I was being lazy when writing some SQL in a table that had an AUTO_INCREMENT column. When a sting say '12ab342' is used in a where clause to check the AUTO_INCREMENT column it is truncated to 12 and checked.

Eg.

table 'test' lets say:

id INT AUTO_INCREMENT
txt_id VARCHAR(10)
Engine: innoDB

+------+--------+
| id| txt_id|
+------+--------+
| 1| a23hsf3|
| 2| 1d3hsf3|
| 3| c23hsf3|
| 4| 423hsf3|
+------+--------+

Using this SQL statement the following results are returned
SELECT * FROM test WHERE id = '1d3hsf3' OR txt_id = '1d3hsf3';

+------+--------+
| id| txt_id|
+------+--------+
| 1| a23hsf3|
| 2| 1d3hsf3|
+------+--------+

Here I didn't bother checking the type of id and assumed that MySQL would fail on the first check of id (as its an auto increment integer) however it returned id 1 and 2!

likewise the test:
SELECT * FROM test WHERE id = '1d3hsf3';

returns:
+------+--------+
| id| txt_id|
+------+--------+
| 1| a23hsf3|
+------+--------+

Is this expected behaviour and why? I am using MySQL-server-community-5.1.37-0.rhel5.x86_64

I can fix the issue by type casting the id as a string in the query
eg. SELECT * FROM test WHERE CAST(id AS CHAR) = '1d3hsf3' OR txt_id = '1d3hsf3';
but then the index is not used.

Obviously my code before getting to the database side of things was quite sloppy in this case and I have fixed this now but I was surprised to get this result.

Any comments?

set an execute statement (no replies)

mik jid — Thu, 15 Oct 2009 14:11:05 +0000

using this:

SET @skip=1; SET @numrows=1;
PREPARE STMT FROM 'SELECT number FROM test LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

how do I SET an variable to this number? looked everywhere.

unable to write 'random state' (no replies)

hemant yadav — Thu, 15 Oct 2009 07:37:20 +0000

Hi every one
i am getting this type of message when setting server and client certificate.

C:\newcerts>openssl x509 -req -in
ey ca-key.pem -set_serial 01 > cl
Loading 'screen' into random stat
Signature ok
subject=/C=IN/ST=New York/L=New Y
ailAddress=info@smilemd.com
Getting CA Private Key
unable to write 'random state'

i want to configured ssl for Mysql on windows os. i have downloaded the Openssl and installed it and follow all the instruction which have been mention http://dev.mysql.com/doc/refman/5.1/en/secure-create-certs.html in this document for windows.

Please help me
Hemant Yadav
Software Engineer
http://www.smilemd.com

crazy slow where aa in (select ...) (1 reply)

stephen bond — Wed, 14 Oct 2009 17:04:05 +0000

I have a table with 19mln recs and need to get only recs that match a field in a list of about 200k. table fixacc has a single field with unique indexed values and was created just to save the subquery and improve speed.

SELECT b.`prod`, b.`acc`, b.`yearmon`, b.`bd`, b.`sd`, b.`fa`, b.`rd`, b.`matd`, b.`term`, b.`disc`, b.`payd`
FROM branch60f b where acc in (select acc from fixacc)
into outfile 'c:/pr1/nls/try1.csv' fields terminated by ','

it is still running on day 4 ...
I think it takes each record from the 19mln and then checks whether acc is in the fixacc table, so it will run 19mln queries.

Is that the way it should be done or I did sth bad?

Thank you all for looking.
Stephen

Connection via SSL (no replies)

ming cui — Mon, 12 Oct 2009 16:16:37 +0000

using openssl, I followed the section on the http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html

mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| have_ssl | Dissabled |
+---------------+-------------+

"If the value is DISABLED, the server supports SSL connections but was not started with the appropriate --ssl-xxx options"

So How to enable it?? what are the appropriate --ssl-xxx options?

5.4 defaults-extra-file deprecated/bug? (no replies)

Yvan Rodrigues — Fri, 09 Oct 2009 11:56:26 +0000

I'm posting this before reporting a bug. Please let me know if you agree that this is a bug.

I upgraded 5.4.1 to 5.4.2 on FreeBSD 7.2-RELEASE-p2 using ports via portupgrade. After the upgrade the rc.d/mysql-server script no longer works. It generates the error:

091009 7:38:34 [ERROR] /usr/local/libexec/mysqld: unknown variable 'defaults-extra-file=/var/db/mysql/my.cnf'
091009 7:38:34 [ERROR] Aborting

Has this option been deprecated, is it no longer valid on the command-line or has its syntax changed? This option is invoked by the command_args in the rc.d script.

MySQL: Error 1005; OLD Constraints still exist after DROP DATABASE in new database; (no replies)

Luke Wallace — Fri, 09 Oct 2009 02:06:50 +0000

Hi Guys

I am at my wits end with this one. The problem is simple, I am using the InnoDB engine. I created a database, added 4 or 5 tables with relationships between them. I then dropped the database.

I came back and recreated the database with the same name again. I have started to create new tables, some of which share names from the old database, and they are failing with:

Error 1005 (HY000) at line 42: Can't create table './dir/table.frm' (errno: 150)

I had a look in the InnoDB engine status and found:

Error in foreign key constraint of table db/entity_user-details: there is no index in referenced table which would contain the columns as the first columns, or the data types in the referenced table do not match to the ones in the table. Constraint: CONSTRAINT ENTITIES_ENTITY_USER-DETAILS FOREIGN KEY (entity) REFERENCES entities(entity) ON DELETE CASCADE ON UPDATE CASCADE

This constraint should have definitely been removed when the old database was initially removed but it appears to have stuck around and become involved in the new database of the same name and is haunting me.

I've tried droppingDB/restarting mysql/restarting system in multiple orders and combinations. I've tried SET FOREIGN_KEY_CHECKS. I've tried physically trashing the data files in the MySQL directory with no luck.

I'm hoping somebody can help here..

Cheers

Luke

LEFT JOIN problem (1 reply)

Morgan Leek — Thu, 08 Oct 2009 04:52:20 +0000

I currently have two tables which I am trying to join in order to figure out who has not filled a report for a given time period.

There is a 'child' table and a 'file_report' table.

My difficulty lies in that the 'file_report' table contains many instances of a 'child' filing a report and I can't get a nice list of all users who have not filed a report this week. I can quickly find those who have, I just need to MINUS that from all users.

# Current JOIN showing all reports completed for a time period
SELECT child.child_id, child.surveyor_id, child.name
FROM `child`
LEFT JOIN file_report ON child.child_id = file_report.child_id
WHERE
file_report.filed_date >= '2009-09-28'
AND file_report.filed_date <= '2009-10-05'

I was using the WHERE below, but this includes users who've filed reports for this time period.

WHERE filed_date IS NULL
OR filed_date < '2009-09-28'
OR filed_date > '2009-10-05'

I really just need is something like below, but that obviously works in SQL.
SELECT * FROM `child`
MINUS
SELECT child.* FROM `child`, `file_report`
WHERE `child`.child_id = `file_report`.child_id
AND `file_report`.filed_date >= 'start_date'
AND `file_report`.filed_date <= 'end_date'

UPDATE statement with RANK (no replies)

Richard Cranney — Wed, 07 Oct 2009 19:08:39 +0000

Hello,

I wondered if anyone could help me with a probably simple SQL command. I use DB2 SQL so I've noticed some changes are hitting me for 6 on something I used to do on DB2.

My problem is that I'm creating a mini league table and trying to store the positions of the team that week by terms of RANK.

I'll give a bit of background first about the tables and their structure...

Tablename INS_CONTROL
columns date_week

Tablename REP_TABLE
columns date_week (k), team_code (k), points_this_week, points_last_week, points_total, position_this_week, position_last_week

Example of data...

date_week team_code points_t_w, points_l_w, points_t, position_t_w, position_l_w
200901 AAA 10 0 10 0 0
200901 BBB 20 0 20 0 0
200901 CCC 30 0 30 0 0
200902 AAA 40 10 50 0 0
200902 BBB 10 20 30 0 0
200902 CCC 5 30 35 0 0
200903 AAA 20 40 70 0 0
200903 BBB 15 10 45 0 0
200903 CCC 50 5 85 0 0

What I would like to do is populate the column position_t_w with the current position they were in based on the points_t column for that week.

This data runs weekly and will add a new week in every time, so its only the current week I am wanting to update. I wrote something along the lines of...

DROP TABLE IF EXISTS SESSION_REP_TABLE
;

CREATE TEMPORARY TABLE SESSION_REP_TABLE SELECT * FROM REP_TABLE;
;

SET @rownum :=0
;

UPDATE REP_TABLE I
SET
I.position_this_week = (SELECT
@rownum := @rownum + 1
FROM
SESSION_REP_TABLE J
WHERE
I.team_code = J.team_code
ORDER BY
J.points_total desc)
WHERE
I.date_week = (SELECT
date_week
FROM
INS_CONTROL)

But this seems to put the rank in any old order assigned to any team.

In DB2 I would just use a MERGE statement but I can't seem to find that in MySQL unless i'm missing something.

Any help would be much appreciated. Thanks