ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming

MYSQL general problems and their solutions

Updated on January 7, 2017
mysql routines and procedures,general problem errors
mysql routines and procedures,general problem errors

How to create procedures and functions in mysql | what is stored routines

Stored routines require the proc table.

Stored routines can be particularly useful in certain situations:

When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.
When security is paramount. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.

Stored routines can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side.

Stored routines also enable you to have libraries of functions in the database server.


CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

routine_body:
Valid SQL routine statement

Syntax Of Create Trigger MYSQL | Query Of Create Trigger MYSQL

To know about Existing Trigger's | Query To Find Out Existing Triggers MYSQL

Show Triggers;

Structure of Existing Trigger MYSQL


show create trigger TriggerName;


Syntax to create Trigger MYSQL

1. Change Delimiter using :

delimiter $

2. Trigger would be based on certain event like Delete,Insert

create trigger TriggerName after/before delete/update on tableName for each row begin YourCondition FollowedByAction end $

Example of Creating trigger MYSQL


CREATE TRIGGER TriggerName AFTER DELETE ON TableName FOR EACH ROW BEGIN IF((SELECT COUNT(DISTINCT Column) FROM TableName WHERE ColumnName=OLD.ColumnName)=0) THEN DELETE FROM TableName2 WHERE columnValue=OLD.columnValue; END IF; END$

OLD.ColumnName will pick old value of Column indicated.
NEW.ColumnName will pick newly inserted value of Column indicated.

MYSQL or Mongo DB

Which one is better

See results

TRIGGER command denied to user 'root'@'%' for table 'TableName'

How to show all Triggers of DataBase in MYSQL ?

> show all Triggers/Views, this will result into a list of triggers or views that are available.

If you are getting an error like " TRIGGER command denied to user 'root'@'%' for table 'TableName' " for Trigger or View then following solution would be effective :


First Method :

1. Take a dump of all Triggers .
2. editing Definer using gedit or textPad.
3. Surce back to DB

Second Method :

1. show create trigger TriggerName.
2. Copy the structur.
3. Drop Trigger Trigger NameOfTrigger.
4. Create Trigger(using structure that you copied earlier).

Possible cause of error TRIGGER command denied to user 'root'@'%' for table 'TableName'


Reason : User(root or something else), i.e user previllaged changed and now is something else

Incorrect parameter count in the call to native function 'SUBSTRING_INDEX'

Error occurs due to silly mistake at time of writing MYSQL query.

Example :

SELECT SUM(CASE (SUBSTRING_INDEX((SUBSTRING_INDEX(t1.column,'#',t3.column_NO),'#',-1))) WHEN 'l' THEN 1 ELSE 0 END ) AS LEF,SUM(CASE (SUBSTRING_INDEX((SUBSTRING_INDEX(t1.column,'#',t3.column_NO),'#',-1))) WHEN 'r' THEN 1 ELSE 0 END ) AS RIGH, SUM(CASE (SUBSTRING_INDEX((SUBSTRING_INDEX(t1.column,'#',t3.column_NO),'#',-1))) WHEN 'w' THEN 1 ELSE 0 END ) AS WRONG FROM table1 t1 LEFT JOIN table2 t2 ON t1.ID=t2.ID LEFT JOIN table3 t3 ON RE.column_ID=t3.column_ID WHERE t1.TEST_ID=610 AND t2.column1_ID=341 AND t3.column_NO=1;

Note : Error in above query is some extra brackets.

SUM(CASE (SUBSTRING_INDEX((SUBSTRING_INDEX(t1.column,'#',t3.column_NO),'#',-1))) WHEN 'l' THEN 1 ELSE 0 END ) AS LEF

Rate Us

Cast your vote for MYSQL

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.