MySQL without password prompt?

Question

I am using MySQL database. I have employee leave table which having information about employee leave.

Please find table details:

CREATE TABLE IF NOT EXISTS `APPLY_LEAVE` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `EMP_ID` varchar(100) NOT NULL,
  `TYPE_OF_LEAVE` varchar(100) NOT NULL,  
  `DAYS` varchar(100) NOT NULL,
  `REASON` varchar(200) NOT NULL,  
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `STATUS` tinyint(2) NOT NULL,
  `CREATED_ON` date NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

--
-- Dumping data for table `APPLY_LEAVE`
--

INSERT INTO `APPLY_LEAVE` (`ID`, `EMP_ID`, `TYPE_OF_LEAVE`, `DAYS`, `REASON`, `START_DATE`, `END_DATE`, `STATUS`, `CREATED_ON`) VALUES
(1, 'EMP001', 'SL', '2', 'Sick Leave', '2018-11-30', '2018-12-01', 1,'2018-11-06'),
(2, 'EMP002', 'EL', '1', 'Personal', '2018-12-13', '2018-12-13', 1,'2018-11-09'),
(3, 'EMP003', 'CL', '2', 'Casual Leave due to Birthday', '2018-08-31', '2018-09-01', 1,'2018-08-20'),
(4, 'EMP001', 'CL', '3', 'Casual Leave', '2018-12-04', '2018-12-06', 1,'2018-11-27'),
(5, 'EMP002', 'SL', '4', 'Sick Leave', '2018-09-10', '2018-09-13', 1,'2018-10-04'),
(6, 'EMP003', 'SL', '3', 'Sick Leave', '2018-10-30', '2018-11-01', 1,'2018-11-25');

Require Output: I want to generate Report/excel to receive information as month wise employee leave data based on leave type i.e (Month wise, Leave type data) Format should be below:

Requirement: I want MySQL query to fetch attached result month wise, Leave type data(SL/CL/EL) which took by Employee.

---

Query tries:

SELECT EMP_ID,
SUM(CASE WHEN TYPE_OF_LEAVE = 'EL' AND MONTH( START_DATE ) =11 THEN DAYS ELSE 0 END ) AS EL_NOV,
SUM(CASE WHEN TYPE_OF_LEAVE = 'CL' AND MONTH( START_DATE ) =11 THEN DAYS ELSE 0 END ) AS CL_NOV,
SUM(CASE WHEN TYPE_OF_LEAVE = 'SL' AND MONTH( START_DATE ) =11 THEN DAYS ELSE 0 END ) AS SL_NOV,
SUM(CASE WHEN TYPE_OF_LEAVE = 'LOP' AND MONTH( START_DATE ) =11 THEN DAYS ELSE 0 END ) AS LOP_NOV,
SUM(CASE WHEN TYPE_OF_LEAVE = 'EL' AND MONTH( START_DATE ) =12 THEN DAYS ELSE 0 END ) AS EL_DEC,
SUM(CASE WHEN TYPE_OF_LEAVE = 'CL' AND MONTH( START_DATE ) =12 THEN DAYS ELSE 0 END ) AS CL_DEC,
SUM(CASE WHEN TYPE_OF_LEAVE = 'SL' AND MONTH( START_DATE ) =12 THEN DAYS ELSE 0 END ) AS SL_DEC,
SUM(CASE WHEN TYPE_OF_LEAVE = 'LOP' AND MONTH( START_DATE ) =12 THEN DAYS ELSE 0 END ) AS LOP_DEC
FROM APPLY_LEAVE
GROUP BY EMP_ID

Facing Issue: I.e One employee look leave on Friday and Saturday (i.e EMP001 took SL on 2018-11-30 to 2018-12-01) (Friday is month of last date and Saturday is first date of month and I am inserting single record into table. When employee applied leave from application. Here result should be

EMP001 - SL

November - 1 leave

December - 1 leave

How can I write this MySQL query?

Solution 1

Not that I would encourage running MySQL without a password if you're storing anything of value, but in this case the problem seemed to be a shell alias for the mysql command which was specifying user/password options.

Usually if you just run 'mysql', you'll get an error along the lines of 'access denied (using password: NO)'. At this point you'd realise that you need to tell mysql to ask you for the password by using the -p option. (Or specify the password directly in the command with --password=pass).

In this question the clue was that the error suggested a password was being used, even though no password options have been provided on the command line directly. If mysql has no password, any supplied password (even blank) will cause an auth failure.

Solution 2

Is a segurity risk but, if you add this to your my.cnf:

skip-grant-tables

You can access to mysql whitout password.

Is better create a /root/.my.cnf file for access whitout password prompt

Comments

[ravi yarlagadda]

What's output from select user, host, password from mysql.user;?

[ricksebak]

It's the same on both computers. This computer forces me to use the password prompt: +------+-----------+----------+ | User | Host | Password | +------+-----------+----------+ | root | localhost | | | root | server2 | | | root | 127.0.0.1 | | | root | ::1 | | This computer doesn't prompt me: | User | Host | Password | +------+-----------+----------+ | root | localhost | | | root | server1 | | | root | 127.0.0.1 | | | root | ::1 | |

[USD Matt]

is there actually a value for the password field in mysql.user?

[ricksebak]

That formatted terribly, but the password field is blank on the server that prompts me for a password and blank on the server that doesn't prompt me.

[ricksebak]

good call! it was an alias. If you want to add it as an answer instead of just a comment I'll mark it as accepted. Thanks.

[Madhur Bhaiya]

No excel screenshots please. Please frame SQL questions with relevant and minimal sample data showcasing your requirements, and corresponding expected output. Please read this link: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

[ADyson]

This is not a free write-my-code or do-my-research service. We won't generally just "provide" you with a query. We like to help you with your attempt to solve the problem, and work with your initial code. If you don't try at all you won't learn anything. And if you have tried, please show us, so we don't waste time making the same mistakes again. (If you just want someone to complete the whole job for you with no effort, this is the wrong place to go, instead find a programmer available for hire).

[Dipti]

@ADyson: Sure, i am providing my query. I updated my question. Thank you

[ADyson]

Ok thankyou. Next question - what version of mysql are you running?

[Dipti]

@ADyson Mysql version 5.6.33-0ubuntu0.14.04.1

[Strawberry]

Seriously consider handling this issues of data display in application code.

[ADyson]

It might have been a bit easier to do something like this (or closer to it) with some of the new functionality available in 8.x. But as Strawberry says above, in your current environment, you're probably better just to write a simpler query to get the data out of SQL, and then use application code to process it and create the desired layout.

[Dipti]

@ADyson: Main issue was, I am inserting single row which have two different month of data. I want result into two separate month. How to differentiate from single row. I have old legacy data so can't change logic while insert time. if two month then insert two records like.

[ADyson]

What do you mean? Most of the sample data you've shown doesn't cover specific months, it's just arbitrary start and end dates. Again this is a case where your SQL can just output the raw data, and application code can process each row, look at the dates and split it out to increase the count pertaining to each month which overlaps the time period between the start and end. Much much easier to do it in code than in SQL.

[ott--]

Why was that downvoted? If this a security flaw, it should be fixed in mysql.

[Zoredache]

@ott--, this isn't a security flaw. It is a feature designed for repairing a server when authentication is broken. It doesn't really address the original question.

[Skamasle]

Not is a bug yes, is a feature but if you add it is a segurity risk for your server, and also is valid to access mysql whitout password, I use it for my local enviroment, but of curse not for production servers. And maybe not respond the questions because my english is bad, and I dont understand somethings, but I try to improve my english and try to help :/

[reallynice]

In my case it wasn't a shell alias, but I found the file .my.cnf in root's home, and that was the reason for which mysql entered directly with root credentials.

[treyBake]

you should explain your code so OP understands the problem/solution

[Dipti]

Thank you @Devraj but i am not getting result which i expect. I.e. EMP001 should display SL - 1 in Nov month and second in Dec month. Your provided query skipped other leave data. I will check with other alternate via application code

[Devraj verma]

@Dipti kindly check corrected query and confirm me.

[Dipti]

@Devrajverma: Thank you very much. Now you did UNION to merge both data. This is very useful. It's work for me. I will add some more logic as per our template. Thank you very much for quick help