FAQ Database Discussion Community


How to change my sql to loop in MySql stored procedure in mysql?

mysql,sql,database,mysql-workbench
When I write my sql in each statement,it works well.Now my sql is: SELECT COUNT(*) INTO v_Point1Num from tbpoint where Point=1; SELECT COUNT(*) INTO v_Point2Num from tbpoint where Point=2; SELECT COUNT(*) INTO v_Point3Num from tbpoint where Point=3; SELECT COUNT(*) INTO v_Point4Num from tbpoint where Point=4; SELECT COUNT(*) INTO v_Point5Num from...

To upgrade mysql vesion in workbench

mysql,version,mysql-workbench
I am using latest version of workbench The available MySQL version is 5.0.27. Does anybody knows how to update MySQL version?

Dynamic sql query for broken hours data

mysql,sql,mysql-workbench
Consider a table named "Books" every one hour new entry will be created in UTC format. I want to fetch last 24 hours data (24 entries) in my local timezone. I tried this select * from books where created_at >= DATE_SUB(NOW(),INTERVAL 24 HOUR); How do I fetch records at runtime...

count ocurrences over each day in mysql

mysql,mysql-workbench
I'm having some problems figuring out how to solve but I can't come with an answer at all.This is my problem. I have a mySQL table like the following: cust_id,date_removed,station_removed,date_arrived,station_arrived 6,"2010-02-02 13:57:00",56,"2010-02-02 13:58:00",77 6,"2010-02-02 15:12:00",66,"2010-02-02 15:12:00",56 30,"2010-02-05 11:36:00",32,"2010-02-05 11:37:00",14 30,"2010-02-05 11:37:00",14,"2010-02-05 11:37:00",20 30,"2010-02-05 12:41:00",85,"2010-02-05 12:43:00",85 30,"2010-02-05 12:44:00",85,"2010-02-05 12:46:00",85 30,"2010-02-06...

mysql workbench doesn't show the next result button

mysql,mysql-workbench
I have an issue with mysql workbench (under ubuntu), it doesn't show the next result button. my select * from my_table should return about 14000 rows but it displays only the first 1000 rows. Any idea ?

SQL server database on mac (replacing remote with localhost)

php,mysql,mysql-workbench
I have a software application that is designed to save data to a remote sever, by using the following URL: http://130.228.263.2/projectName.php The application itself creates tables, stores data, etc... so I don't have to worry about that. I have set up MySQL Server on my Mac as well as MySQLworkbench...

Foreign key constraint has no effect when described inline

mysql,sql,mysql-workbench
I have a simple schema: CREATE TABLE technologies ( technologyName VARCHAR(50) NOT NULL PRIMARY KEY ); CREATE TABLE technologySynonyms ( synonymId INT PRIMARY KEY AUTO_INCREMENT, sourceTechnologyName VARCHAR(50) UNIQUE, targetTechnologyName VARCHAR(50), FOREIGN KEY (targetTechnologyName) REFERENCES technologies(technologyName) ); I thought to simplify the technologySynonyms table definition further like so: CREATE TABLE technologySynonyms...

How to find Missing Indexes in MySQL?

mysql,mysql-workbench,mysqldump
5 and i want to identify missing indexes in it. Can somebody please help me to identify ? This will help us to increase the performance of query that leads to application.

Number of fields in pasted data doesn't match the columns in the table

mysql,laravel,laravel-4,laravel-5,mysql-workbench
Situation I had a table with 8 columns, then I need 2 more fields : company_wieght server_type_weight So I run a migration to add those fields, and now I have 10 columns. I got 0 data in there right now. I want to copy/paste all the data from my staging...

Not able to install mysql-workbench in ubuntu 12.10

mysql,ubuntu,mysql-workbench,ubuntu-12.10
I am trying to install msyql-workbench in my ubuntu 12.10 using the following command sudo apt-get install mysql-workbench but it shows the following error. Reading package lists... Done Building dependency tree Reading state information... Done Some packages could not be installed. This may mean that you have requested an impossible...

Where can i find good study material for “MySQL Developers” certification?

mysql,mysqli,mysql-workbench
Where can i find good study material for "MySQL Developers" certification ?. There is some material at http://education.oracle.com but is not free and is too expensive.

Msql: on MYSQL Query Browser and MYSQL WorkBench

mysql,mysql-workbench
I am currently learning mysql workbench, but I have gotten a book that uses mysql query browser. However, when I am trying to download query browser, it is stated that it is discontinued. So I am currently using workbench. However, I find it difficult to create procedure. What is workbench...

Migration HSQLDB to MySQL using MySQL Workbench 6.2

mysql,mysql-workbench,hsqldb
I've to migrate HSQLDB to MYSQL but I don't know how to configure Workbench. I tried to follow this instruction http://dev.mysql.com/doc/workbench/en/wb-migration-overview-steps.html but I've failed. How to properely configure migration ?

RETURN the amount of males and the amount of females in 1 table using MySQL

mysql,mysql-workbench
I need to RETURN the amount of males and the amount of females in 1 table using MySQL. I have created a query that return a tables with males and females rows, but my column is not being populated. its results =0; Here is my query. I get the table...

Create Trigger which updates a column automatically after update of a table

mysql,database,database-design,triggers,mysql-workbench
I have a table which looks like this: |idAvailability|SumRooms|AvailableRooms|AvailabilityRate| everything is int except AvailabilityRate which is float. I want to create a trigger which when updating the AvailableRooms column, automatically because of the existance of the trigger the column AvailabilityRate update as well. This is what I have done so...

Returning procedure execution code in MySQL

mysql,stored-procedures,mysql-workbench
I have a stored procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `GetNotExecutedOrders`(IN contractID INT(11)) BEGIN SELECT idContract, idOrder, ProductID, Quantity, (SUM(`order`.Quantity))*(product.Price) as 'Total amount' FROM typography.contract JOIN typography.`order` ON contract.idContract=`order`.ContractID JOIN typography.product ON `order`.ProductID=product.idProduct WHERE idContract=contractID and ExecutionDate IS NULL GROUP BY idOrder ORDER BY idOrder; END I need to fix it so...

Find stored grants on mysq tables

mysql,mysql-workbench
i'm looking for a way to find all privileges for a specific user. Provided I have a User "John" who has read-only rights on some tables, is there any "select" or "show" to retrieve me the names of the tables "john" has the rights for? So far, i looked...

trouble with creating subquery

mysql,sql,subquery,mysql-workbench
I am having some trouble with creating a query using subqueries. I have to use subqueries, because it is an assignment from school. I have two tables, an employee one and a department one. From these tables I have to return the employees that earn more than the average salary...

Error 1064 for no apparently reason

mysql,mysql-workbench,mysql-error-1064
I got this query: SELECT companies_comments.id as id, companies_comments.post as post, companies_comments.comment as comment, companies_comments.date as date, companies.`id` AS company, companies.`name` as name, companies.`username` as username, companies.`photo` as photo, companies.`status` AS company_status LEFT JOIN companies ON companies.id = companies_comments.company WHERE company_status NOT IN (3,4) AND companies_comments.post =1 The error: Error...

Preventing SQL statements from getting truncated by MySQL's Workbench in `Performance Reports` section

mysql,performance,mysql-workbench
Recently I was introduced to the new feature of MySQL: performance_schema, and it's awesome. Specially when it's joined with MySQL Workbench's Performance Reports. I find the High Cost SQL Statements section pretty useful and practical. There's only one downside to it, the SQL column which holds the executed SQL statement...

Mysql nested queries take a long time

mysql,sql,mysql-workbench
We are using the following question to get date out of a large mysql table. SELECT fullPath, Permissiontype, DinstinguishedName from cdm.test where fullPath in (SELECT distinct fullPath FROMcdm.test WHERE (Permissiontype = 'EXPLICIT' and not DinstinguishedName ='') OR(Permissiontype = 'INHERITED' AND (length(fullPath) - length(replace(fullPath,'/','')) < 4)) OR(Permissiontype = 'EXPLICIT' AND NOT...

What this message mean in MySQL Table does not support optimize, doing recreate + analyze instead?

mysql,mysql-workbench,mysqldump
Hi i am working on MySQL 5.5 and trying to do index rebuild using Optimize table query i am getting below error, Table does not support optimize, doing recreate + analyze instead What this mean for ? is MySQL engine not allowing to Index Rebuild ? what getting done behind...

Select from view and join from one table or another if no record available in first

mysql,mysql-workbench,mysqldump
I have a view and two tables. Tables one and two have the same columns, but table one is has as small number of records, and table two has old data and a huge number of records. I have to join a view with these two tables to get the...

adding column Foreign key MySQL

mysql,mysql-workbench
I create 2 table and i try add FOREIGN KEY to table on bottom picture. Tables look like http://zapodaj.net/02b893dfd5982.png.html the top table name:"tabela_postow" the bottom name:"tabfile" I try add column to tabfile with foreign key but all the time get Error Code: 1005. Can't create table 'heroku_fd1c348s48a7d8c.#sql-dd3f_ad2f19' (errno: 150) I...

MySQL TITLE QUERY [closed]

mysql,sql,mysql-workbench
Ok, so very new to SQL, but im starting to learn…but I have a question and im sure its simple. i have a database i'm working on and the question asks me to find the "title" of a courses prereq, so, the class is english 201, and its prereq is...

MySQL function declare 2 variables with one select

mysql,mysql-workbench,create-function
I'd like to know how I can create a MySQL function that declares 2 variables by using 1 select statement. Something like this: CREATE FUNCTION `inHashtagCampaign` (campaignId INT,startDateTime DATETIME,endDateTime DATETIME) RETURNS TEXT LANGUAGE SQL DETERMINISTIC BEGIN DECLARE result TEXT; DECLARE limit BIGINT(11); DECLARE suspended TINYINT(1); # #I don't know how...

Anorm & MySQL SHA1 password encrytion is not same with Mysql Workbench command

mysql,scala,mysql-workbench,anorm
In my Play Framework Scala project I am using MySQL as database. In my database I have a column with the name PASSWORD. I will store the users passwords in this column when they create a new user in my project. I use SHA1 encryption for password field while inserting...

Composite primary key in MySQL on existing table

mysql,database,mysql-workbench,composite-primary-key
I am using MySQL workbench to manage my a database that was handed down to me for a development task. Unfortunately, the schema is a nightmare: no primary keys for numerous tables, lots of column duplication, etc. First off, I wanted to add some uniqueness so that I can begin...

unable to create new user in mysql

mysql,mysql-workbench,mysql-error-1064
I am trying to create a new user in mysql 5.6, but while executing the query i am getting an error. please help. mysql> create user [email protected]'%' indentified by 'Passw0rd';<br></b> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server...

Cannot start SSH tunnel manager mysql workbench Ubuntu

mysql,ubuntu,mysql-workbench,workbench
I am trying to connect to my Localhost using mysql workbench, but run into the " Cannot start SSH tunnel manager" every time. I have tried reinstalling workbench with apt-get. I also reinstalled python2.7 and imported paramiko. Still doesn't work. I am running Ubuntu 14.10 64-bit. Here is a screenshot...

shortcut for edit selected field in Workbench

mysql-workbench
What is shortcut for edit selected field in Workbench? (windows) in docs http://dev.mysql.com/doc/workbench/en/wb-keys.html#workbench-keys-menu-edit They say: Modifier+E but it's not work, assuming Modifier key is Shift or Alt or Control....

SQL “Table_Name.Column_name” VS “Column_name” performance & syntax

mysql,sql,mysql-workbench
I'm new to SQL and am currently working through a "teach yourself SQL book" It was mentioned in the book that sometimes you NEED to specify table name with column name (immediately after SELECT line) to get your desired result. It was also mentioned that it is often good practice...

MySQL help forward engineering

mysql,database,database-design,mysql-workbench,mysql-error-1064
So I'm trying to deploy the database that I've created on workbench 6.2 onto a real database (to test it out)... But I keep on getting errors every time I deploy it, and every time I think I've fixed the issue some other error message pops up (this time it's...

Beginner: Can't Order my WHERE Statements Properly with Subselect MIN?

mysql,sql,mysql-workbench
For some reason, I am unable to get this query to return anything. SELECT film.title, film.length FROM moviedb.film, moviedb.`language` WHERE film.length=(SELECT MIN(length) FROM moviedb.film) AND film.language_id=`language`.language_id AND `language`.`name`='French' AND film.rating='R'; So, I am wanting to return two columns with 1 row...the shortest french movie with an 'R' rating. Am I...

Where's table info tab on MySQL Workbench EER?

mysql,mysql-workbench,diagram
When I select a table on EER Diagram on MySQL Workbench, the first tab shown is "Columns" instead of "Table" information where I can add comments to the table, change the engine, etc. Where's the "Table" tab? I've seen this print screen from another user and it's clearly that a...

Mysql: Getting the 2nd earliest date for each group

mysql,sql,mysql-workbench
Desired outcome: I want to get the 2nd earliest date for each group. Current: I am getting the earliest date for each group Query: select * from (select * from profile_match where match_available_on is not null order by profile_id asc, match_available_on asc) as P1 group by P1.profile_id Example: Link From...

MySQL Workbench save values

mysql,mysql-workbench
I already googled and found a question here on stackoverflow, but unfortunately for an older version where obviously has changed the GUI. My problem: I edit something in a row, delete, edit, add, but I can't find any way to save it and apply that. If I close the table,...

What is the differece between my codes when I use cursor to fetch my data with repeat and do while?

mysql,stored-procedures,cursor,mysql-workbench
I use cursor to fetch my datas.The number of my data is two.But I find that when I use repeat to fetch my data,it always fetch 3 times. And When I use do while,it is right which fetch 2 times.What is the different of thest two ways?How to fixed the...

Solving mysql query

mysql,sql,mysql-workbench
Part 1: Software: Mysql workbench Objective: To find the 2nd match for a particular user Table: Profile_match Columns: a_profile_id, b_profile_id, a_profile_match_available_on, b_profile_match_available_on My process: Find all the matches for a particular user Find all the dates of the match for that user Find the 2nd match (id, date) for that...

How do I associate a schema with a connection in MySQL Workbench 6.2

mysql,mysql-workbench
MySQL newbie question: how do I associate a schema that I saved to a custom location with a connection in MySQL Workbench 6.2 ? Here's what I did: (1) clicked the + sign on the home page to create a new model (2) right clicked the "myDB" icon and changed...

find IP addresses in a table in mysql

mysql,sql,mysql-workbench
I have a table in mysql. the table has one column which is UserName. Now, there are regular names in it and some IP addresses in it. How can I show in a query only the IP addresses? Keep in mind that there are names like this a.b.c.d So, if...

List customers that have bought the same pair of products MySQL

mysql,mysql-workbench
I have the following two tables: customer id | customer ----|--------- 1 | john 2 | jenkins 3 | jane 4 | janet 5 | jenny products customer_id | product | price ------------|-----------|----- 1 | brush | 3.5 1 | deoderant | 1 1 | soap | 2.5 2 |...

MySQL SELECT rows as successive columns

mysql,null,mysql-workbench
I have this table named 'Values' in a MySQL database: sp_id W Value_C Top_C 742 11 11.42 1.15 742 12 114.35 2.44 742 13 27.2 0.42 742 14 55.38 6.2 742 15 23.77 16.7 15 11 10.59 9.15 15 12 12.4 12.3 15 13 157.89 0.72 15 14 51.2 1.5...

Using One command In SQL - I need to change all instances of the word 'Cars' to 'Zip-Cars', this includes 'Cars 2' to be changed to 'Zip-Cars 2'

java,mysql,sql,database,mysql-workbench
CREATE DATABASE movies_200186807; USE movies_200186807; CREATE TABLE movies_200186807 ( movie_name VARCHAR(50) NOT NULL PRIMARY KEY, release_date DATE NOT NULL, cost DEC(4) NOT NULL, revenue DEC(4) NOT NULL ); INSERT INTO movies_200186807 (movie_name, release_date, cost, revenue) VALUES ('Toy Story', '1995-11-22',30 , 364), ('Toy Story 2', '1999-11-24',90 , 511), ('Toy Story 3',...

Setting a column as timestamp in MySql workbench?

mysql,sql,timestamp,mysql-workbench
This might be a really elementary question, but I've never created a table with TIMESTAMP() before, and I'm confused on what to put as the parameters. For example, here: I just randomly put TIMESTAMP(20), but what does the 20 as a parameter signify here? What should be put in here?...

Specifying a set of characters using LIKE

mysql,mysql-workbench
Apologies for the simple-minded question, but I am new to this. I am using mySQL Workbench 6.2, and I'm trying to write a simple query that will match any contact name that begins with a J or M from a cust_contact column. I've seen that one can achieve this by...

MariaDB, Workbench, and ON DELETE SET DEFAULT

mysql,mysql-workbench,mariadb
I have a non-nullable foreign key that I need to be set to default on a delete of the other table. I'm using MariaDB, and exporting the SQL script from a model in MySQL Workbench. MariaDB supports ON DELETE SET DEFAULT according to https://mariadb.com/kb/en/sql-99/constraint_type-foreign-key-constraint/ MySQL documentation says that while it's...

Average MySQL in new table

mysql-workbench
I have a database about weather that updates every second. It contains temperature and wind speed. This is my database: CREATE TABLE `new_table`.`test` ( `id` INT(10) NOT NULL, `date` DATETIME() NOT NULL, `temperature` VARCHAR(25) NOT NULL, `wind_speed` INT(10) NOT NULL, `humidity` FLOAT NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB...

How do I apply multiple id's to one record in Mysql?

mysql,database,netbeans,mysql-workbench
I did a database for my eCommerce using MySQL Workbench and I'm using Netbeans to put everything together. I have few tables that serve the purpose of what I'm trying to achieve. Two of these tables that are connected together are product and of course category. I'm at the point...

When installing MYSQL do I have to have MS Excel and Visual Studio

mysql,mysql-workbench
I'm new to MYSQL and following video tutorials. I don't own MS Office, why am I forced to instal MYSQL for excel and Visual studio? Can I skip those 2 parts?

Mysql How can i find max and min after sum a column

mysql,sql,mysql-workbench,minmax
This is my code from tables i want to select from it the first two and the last two rows as a result. is that possible? in other words i want to select min and max of a column after it was summed. prefer two of each if possible thanks...

UPDATE multiple columns using on query in MySQL

mysql,mysql-workbench
I need to set the city_populations Column as follows using 1 CASE command in my table 'customers' Barrie = 177,061 Toronto = 2,480,000 Collingwood = 17,290 Thunder Bay = 108,359 Ive created 2 querys, but it wont run, any suggestions. UPDATE customers SET city_population Barrie = 177061, Toronto = 2480000,...

How to index rebuild in MySQL for all tables in a database?

mysql,mysql-workbench
5 and i have to do index rebuild for all tables in a database which is in InnoDB. i know Optimize which did at table level. Is there any way to do this for all tables in a database in one go.

lower_case_table_names set to 2, Workbench still does not allow lowercase database name

mysql,mysql-workbench
I have installed MySql Workbench 6.2 with MySql version 5.6 on my Windows 7 64-bit. I would like to use Capital letters in my database name and table names. So I need to set the variable lower_case_table_names to 2. When I look at my Options file's General tab, it looks...

MySQL - Cannot create ERD in Server Instance Schemas

mysql,mysql-workbench,entity-relationship,erd
I am brand new to MySQL and I am trying to understand how it all fits together. I have a very strong understanding of Microsoft Access, but this is a whole new world. I create a schema (MySQL Model) and I had no problem creating an ERD. I then figured...

mysql query to find sum of fields grouping by time change

mysql,sql-server-2008,mysql-workbench
I have the following data structure timestamp(varchar) bid(decimal) ask(decimal) 20090501 03:01:01.582 0.000060 0.000000 20090501 15:01:01.582 0.000120 0.000060 20090501 16:01:01.582 -0.000080 0.000120 20090504 03:01:01.582 0.000040 0.000060 20090504 15:01:01.582 -0.000040 0.000040 20090504 16:01:01.582 0.000000 -0.000040 20090505 03:01:01.582 0.000050 0.000110 20090505 15:01:01.582 0.000000 0.000050 20090505 16:01:01.582 -0.000080 0.000000 Now I want the output...

I can't drop my database or alter my tables in MySQL

mysql,database,mysql-workbench
Something weird just happened, I have a database and I dropped the whole DB once since it was practically messy, afterwards, I created it again and managed to find more errors so I dropped it again but this time, the query went on for so long, I had to interrupt...

Access denied connecting to database using PHP generated by MySQL WorkBench

php,mysql,mysql-workbench
I've seen a couple of similar posts, but none really help with my problem. I'm trying to connect to a database I created by using MySQL workbench. Under my user privileges in Workbench there's a GUI which allows you to enable all grants and permissions. When I run the script...

How to change the behavior of double click on table in MySQL Workbench?

mysql-workbench
In MySQL Workbench, double clicking on a table object pastes the table name into the query file currently in focus. Can it be changed? I would like to see table contents when double clicking on it. Or at least, disable the current behavior. Is that possible? and how? Thanks....

SQL 0 if negative value

mysql,sql,mysql-workbench
So I want to turn negative value's into a 0. I found 2 solutions that do exactly the same: SUM(IF(ordered_item.amount < 0, 0, ordered_item.amount)) as purchases And SUM(CASE WHEN ordered_item.amount < 0 THEN 0 ELSE ordered_item.amount END) as purchases They give me both the same result, but which would give...

Set fixed height of rows in select ouput

mysql,mysql-workbench
I use MySQL Workbench 6.2.4. When I make a select query for columns that contain, for example, html code, workbench makes the resulting rows very high. Some rows are of normal height, but some are too high. It's very difficult to work with such rows. Is it possible to make...

implementing a many to many relationship

mysql,database,foreign-keys,many-to-many,mysql-workbench
I am planning on creating a database to track teams and players in a soccer league. The stats I wish to track are as follows... goals scored/conceded(gk), assists, minutes played, cards received and leading scorers. As well as overall standings and fixtures and results. I have created an ER diagram...

How can I tell how many rows or objects are in my table using MySQL Workbench?

mysql,mysql-workbench
How can I tell how many rows or objects are in my table using MySQL Workbench ? I think I have 9000 items, but since I can only select 1000 as my limit in Workbench. Now I am not sure whether or not my data is there. ...

Sub Queries and fetching data from two tables

mysql,mysql-workbench
I need to list the horse_id, horse_name for each horse that has placed in the top 3 (e.g was place 1,2 or 3) two or more times. HINT: Try using WHERE...IN... I'm pretty sure I'll have to get data from the HORSE table, which includes the horse_id and horse_name, and...

Backup mysql databases into self contained files

mysql,mysql-workbench
I have a linux system with Mysql contain more than 400 databases,I need to export each databases as a single *.sql file.Is it possible to do this with mysql_dump or Mysqlworkbench. I have tried mysql_dump with --all-databases option.but this make a file with all database.it is large in size....

Mysql views return data when table empty

mysql,sql,sql-server,mysql-workbench
I am having a weird problem with mysql. I am developing a visual application on C# that stores data into a database. Previously I used SQL for the Database, but my client changed his mind for mysql. So I recreated the same schema on mysql. Now it is happening a...

SQL AVG of a SUM

mysql,sql,mysql-workbench
I want to get the average of a calculated sum. I have tried the syntax from this stackoverflow answer So my SQL query looks like this: SELECT AVG(iq.stockvalue_sum), iq.date FROM( SELECT CONCAT(DATE_FORMAT(s.date, '%Y'), '-01-01') as date, SUM(GREATEST(s.stockvalue,0)) as stockvalue_sum FROM stockvalues s GROUP BY CONCAT(DATE_FORMAT(s.date, '%Y'), '-01-01') ) iq However...

MySQL Workbench. Way to generate foreign keys in forward engineering as alter table statements?

mysql,mysql-workbench
Is there any option in MySQL forward engineering which can generate FOREIGN KEYS as ALTER TABLE STATEMENT?

MySQL Workbench: No objects selected

mysql,mysql-workbench
I have installed MySQL Workbench in my machine. But now I'm unable to select a table on left hand side. Here's what I have currently: How can I get to the tables in my database?...

Foreign Keys and Compοund PKs

mysql,mysql-workbench
I largely like to work on my more complex tables using mySQL Workbench's EER diagrams. I use it to define foreign keys between tables. I understand that a foreign key should be indexed, but when I use Workbench to draw the relationship to another table, it creates a compound PK...

How to connect MySQL workbench to database

php,mysql,mysql-workbench,putty
I'm trying to use mysql workbench to get access the database in my office. My friend has already helped me setup the connection in PuTTY, so I can write my MySQL script in that "black and white" window. However when I try to use MySQL workbench to get connection to...

Database CREATE TABLE for 2 similar tables

mysql,database,mysql-workbench,create-table
I have the following information in 2 *.CSV files. There are many more rows but I have included a small set here as an example. file_id descrip Date file_heat_value_1 1 ABC 2015-02-11 1.02500 1 ABC 2014-11-19 0.85500 1 ABC 2014-05-22 17.20 2 DEF 2014-08-20 1.3700 2 DEF 2014-05-21 15.5500 2...

Using MySQL: Display all of the Column_Name in upper case, sorted alphabetically without duplicates

mysql,mysql-workbench
I have finished this question, Except I need my query to not show duplicates. How can I do this: SELECT UPPER(surname) FROM customers ORDER BY surname; ...

MySQL workbench SSH connection error [Bad authentication type(allowed_types=['publickey'])]

mysql-workbench
I have issue regarding SSH connection with my server. When i try to connect it results into error:"Bad authentication type(allowed_types=['publickey'])" Thanks

SQL Yes/No query without control flow functions (IF,CASE,COALESCE etc)

mysql,sql,mysql-workbench
I've been trying to find a way to get a Yes/No answer to a query without using control flow functions(IIF,ELSE,CASE,COALESCE,ISNULL,IFNULL,etc). I want my query to give me a "Yes" answer if there has been a flight of a certain Airline on a given date between 2 given airports. This is...

MySQL Workbench AWS EC2 With Non publicly accessible RDS

mysql,amazon-web-services,amazon-ec2,mysql-workbench
I have set up a new RDS instance within my VPC that is not publicly accessible. I can connect via WorkBench and create new schemas and do other miscellaneous tasks, however, when I try to import (Data Import) whether from dump or self contained I get "ERROR 2003 (HY000): Can't...

Conditional statements and multiple queries in mysql

mysql,phpmyadmin,mysql-workbench
In MySQL (both instances of phpmyadmin and MySQL workbench) I would like to check if a value exists in a table before I perform any other additional queries to the same table or other tables and below are the sets of queries I have tried but neither are working and...

To check for error in a MySQL transaction

mysql,mysql-workbench
I want to carry out a transaction in MySQL. If any error is present, I wish to rollback or else i want to commit. Is there any code to carryout these operations?

In MSQL Workbench, PHP can be scripted together with MYSQL?

php,mysql,mysql-workbench
Does MYSQL WORKBENCH have any feature to accommodate PHP scripting within MYSQL scripts and edit PHP scripts as it does MYSQL script?

How do I edit a unique column reference as foreign key in another table in mysql?

mysql,database,mysqli,mysql-workbench
I have one master table having column keywords varchar(120) as Unique and it is reference to another table **cmp_keywords** as a foreign key. Now how can I edit the column keywords value from master table and cmp_keywords table?

CREATE FUNCTION Syntax error @postLimit (at text suffix)

mysql,mysql-workbench
I have this SQL code to create function: CREATE FUNCTION `inHashtagCampaign` (campaignId INT,postDateTime INT UNSIGNED, postSource CHAR(10)) RETURNS BOOLEAN LANGUAGE SQL DETERMINISTIC BEGIN DECLARE @postLimit INT(11); DECLARE @active OOLEAN; DECLARE @postCount BIGINT(50); DECLARE @suspendOnLimit TINYINT(1); DECLARE returnVal BOOLEAN; DECLARE @trackingTwitter BOOLEAN; DECLARE @trackingInstagram BOOLEAN; DECLARE @trackingYoutube BOOLEAN; SELECT @postLimit :=...

How to select multiple avg(column) in one select statement in MySql stored procedure?

mysql,mysql-workbench,mysql-error-1064
I find when I select one avg(column) into a variable,it is OK.But when I select more than one avg into a variable,it is wrong.I do not want to write it twice,how to fixed it? //work well SELECT avg(MathPoint) into MathPoint from student where sex=1 SELECT avg(EnglishPoint) into EnglishPoint from student...

How to connect mysql using ssh tunnel and dedicated IP

mysql,ssh,mysql-workbench
I have been working with mysql for a while in localhost and now I decided to install it on a different workstation within my private network. In order to achieve my objective I executed the following: I installed it on a Linux workstation (mysql 5.6) with sudo apt-get install mysql-server-5.6...

No Synchronize Model with Database in Mysql Workbench

mysql,synchronization,schema,mysql-workbench,sync
I'd like to synchronize a model in Mysql workbench. However, I can't find the wizard following these steps in the docs. How do I enable this feature? I'm running 6.3.3.0 (latest) on Mac Os X . ...

Mysql workbench EER diagram line style field-to-field

mysql-workbench
Currently I see 90deg-based table-to-table lines. Is there any way to change it to straight field-to-field lines? Now it looks like this. I googled this image. So its probably possible change line style. Or its differs by OS or workbench version(maybe this is old feature?)? ...

Move column_name AFTER Column_name using MySQL

mysql,mysql-workbench
I need to move a column_name AFTER column_name; I have done the following: ALTER TABLE tables_name MODIFY COLUMN columns_name AFTER columns_name; Any suggestions, ...

Inheritance relationship in ERD diagrams

database,mysql-workbench,erd
I have question about inheritance representation in ERD-diagram. The following example: I have plane, and I have also two types of planes 1-PersonsPlane 2-CargoPlane I am confused which relationship to use ( 1-to-1 OR 1-to-Many). My DB teacher told that I should use 1-to-1 relationship, But I have found on...

Select top 1000 rows in MySQL Workbench And Generate List of Headers

mysql,sql-server,mysql-workbench
I previously used Microsoft SQL Server Management Studio and when I right clicked on a table and selected Select top 1000 rows a new tab would open created. Inside that tab, some code would be generated automatically and would contain the list of headers (in TSQL syntax) within the select...

MySQL Workbench export error

mysql,import,export,comments,mysql-workbench
I designed a mysql database with Mysql Workbench and exported it as SQL commands. Then executed them on phpmyadmin. This is what happened: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near...

mysql: add subquery to existing query

mysql,sql,mysql-workbench
I'm new to sql, during my internship i was required to know a bit of sql so i started learning and was allowed to practice on the test environment(no updating, inserting or deleting but just displaying ), while practicing i had this question,this is my existing query: Select ab.USER_ID, user.email...

Sorting using MonthName and Year

mysql,mysql-workbench
I have a table structure like this:- ID Month Year 1 January 2015 2 February 2015 3 March 2014 4 April 2014 5 May 2014 6 June 2014 7 July 2014 8 August 2014 9 September 2014 10 October 2014 11 November 2014 12 December 2014 I want to sort...

How to connect MySQL Workbench to Amazon RDS?

mysql,database,ssh,mysql-workbench,mysqladministrator
I am accessing the Amazon RDS (MySQL) from Putty. MySQL Workbench works through SSH commandline only, but I would like to access the cloud MySQL host database directly from MySQL Workbench.