MySQL Forums

Error code 1607: Cannot create stored routine . . Check warnings (local only) (no replies)

Michal Shafir — Sat, 07 Nov 2009 04:10:57 +0000

I recently installed Windows 7 on my laptop to Drive B (fresh install, temp dual-boot). After installing the latest version of MySQL Essentials, I'm having trouble creating some stored procedures.

I can create them fine on all our remote servers, just not locally. The issue has thus far only occurred on 2 procedures. I get an error when trying to create:

Cannot create stored routine `abm_TicketList`. Check warnings

SHOW WARNINGS returns nothing. I've tried flushing everything, dump and rebuild. I've lost many valuable hours on this, so any suggestions on how to troubleshoot would be greatly appreciated.

Thanks.

MySQL and VS2008 problems with Time/TimeSpan and DateTime (no replies)

Harsh W — Sat, 07 Nov 2009 03:08:00 +0000

I'm using MySQL 5.1.39 x64 on Windows and Visual Studio 2008. I have a couple of problems, just want to know if anyone has seen something like this before ...

Using connector/net versions 5.2.7, 6.1.2, 6.2.0 and adding a dataset to Visual Studio - I get the dreaded " Failed to add relations : Column 'REFERENCED_TABLE_CATALOG' does not belong to table... ". It adds the tables but not the relationships ... I wonder what is going on. Tried recreating database from scripts, no luck so far. Has anyone come across this problem and if they solved it, care to share the wisdom ?

The other problem is that one of the tables uses TIME and DATETIME columns. The TableAdapter created from the typed dataset for the TIME column maps to a System.DateTime ... shouldn't it map to a System.TimeSpan ? Anyone know of how to get the VS2008 integration to handle TIME mappings correctly ?

Merge Data between two Tables (no replies)

Jeff Mitchell — Sat, 07 Nov 2009 02:41:13 +0000

Hello,

I have a forum that I co-support and we are looking to install a recipe Blog application that we would like to be able to use the forum user table instead of the recipe blog app user table.

I am wondering if it is possible to merge the two tables together and then query the info from the "forum User Table".

I can provide table info if needed.

Thanks in advance!

Multiple events Times (no replies)

ashr ben — Sat, 07 Nov 2009 02:31:18 +0000

what would be the parameters:

1>To launch an event every 20 minutes starting at 9:00am ending at 5:00pm every day.
(? how do you define a time without a date)

2>To launch an event based on if statement.... Check if table updated, if table update, do nothing. If not, update. would run at 1 hour interval, between 00:00:00 to 00:00:10.

3>at 5:00pm, dump txt files. Again at 5:00pm, not date but time only.

MYSQL Command Line + Workbench (no replies)

sam b — Sat, 07 Nov 2009 02:16:00 +0000

Hi guys,

Its my second day using MYSQL and iv been using the workbench and command line for a good 7hours + now.

I have created tables in both the commandline way and the workbench way and added data in to the created table.

The problem im having is that i have the MYSQL server running and setup on my machine and i created a database using the workbench but now im wanting to submit queries in the commandline by accessing data from my newly created database (from workbench)

I know of the command "SHOW DATABASES;" but my workbench one is not appearing in the list. I have searched my computer numourse times to find where these tables are stored so that i can place my workbench table with the ones i have created using the command line earlier but cannot find the location.

How can i access and start using queries to select data on my new tables from the workbench database as i cannot locate it!!

Thankyou in advance!, plz be gentle...

Combining multiple tables by date/time (no replies)

Dion Houston Sr — Sat, 07 Nov 2009 01:57:06 +0000

Hi all,

Full disclosure, I'm not actually doing this in MySQL, but I couldn't find any general database modeling forums and I need help. :)

I am trying to model a database containing information about events from different sources. All of these tables have date and time (DATETIME and INT respectively), and I've added an integer primary key to each table.

To make it a bit more concrete, say for example table1 and table2 look like

PKEY, DATE, TIME, DESCRIPTION
PKEY, DATE, TIME, CATEGORY, LOCATION, etc.

The desired end state is a query which produces

DATE, TIME, DESCRIPTION, CATEGORY, LOCATION, etc.

Which contains all records in both tables (my real model currently has 4 tables). Currently the way that I'm doing it is by having an additional table in the form

PKEY, DATE, TIME, table1ID, table2ID

Which I populate with the key of the appropriate table in the appropriate field and then join.

The biggest issue I have with this approach is that each change I make to table1 or table2 requires me to make an appropriate change in the additional table. If possible I'd like to do this through modeling instead. Secondarily, I imagine that as I add sources that the performance of this will be close to O(N^2).

Does anyone have any suggestions on how to improve this scenario? I'm quite a bit rusty on SQL, but I don't recall an easy way to do this. Theory rather than actual code is most appreciated, but any suggestions I'll make work.

Thanks in advance,

Dion

Creating MySql Database with ASP.Net (no replies)

Robin Rembish — Sat, 07 Nov 2009 01:38:17 +0000

I have posted on other forums but have not gotten a good response to this question. Is is possible to create a MySql database with ASP.Net? If so, how. A response from the Microsoft forum brought me here.

This link provides code to create the tables:
http://forums.mysql.com/read.php?38,63403,63403

Thanks for any help you can offer,

Robin

question marks (2 replies)

Cash Perkins — Sat, 07 Nov 2009 06:34:33 +0000

Hi..

I have used the following script to create table and load data:

CREATE TABLE `TaskAccepts` (
`SR_Number` VARCHAR(12) NOT NULL,
`Type_Name` VARCHAR(25),
`Summary` VARCHAR(255),
`Status_Name` VARCHAR(12),
`Severity_Name` VARCHAR(2),
`Language` VARCHAR(2),
`Timezone` VARCHAR(12),
`Region` VARCHAR(30),
`Gem` VARCHAR(30),
`Country_Group` VARCHAR(30),
`Country` VARCHAR(30),
`Task_Number` VARCHAR(16),
`Task_Type` VARCHAR(25),
`Task_Name` VARCHAR(255),
`Owner_Group` VARCHAR(40),
`Business_Unit` VARCHAR(12),
`Vertical` VARCHAR(12),
`Geo` VARCHAR(30),
`Gem2` VARCHAR(30),
`Country2` VARCHAR(30),
`Partner_Sun` VARCHAR(10),
`Assignee` VARCHAR(50),
`L5_Name` VARCHAR(50),
`L6_Name` VARCHAR(50),
`L7_Name` VARCHAR(50),
`L8_Name` VARCHAR(50),
`L9_Name` VARCHAR(50),
`Task_Status` VARCHAR(12),
`Task_Creation_Date` VARCHAR(20),
`CalYear` VARCHAR(4),
`CalYearQtr` VARCHAR(6),
`CalYearMonth` VARCHAR(7),
`Week` VARCHAR(2),
`Date` VARCHAR(20),
`PlannedStartDate` VARCHAR(20),
`ScheduledStartDate` VARCHAR(20),
`ActualStartDate` VARCHAR(20),
`TaskClosedDate` VARCHAR(20),
`CalYear2` VARCHAR(4),
`CalYearQtr2` VARCHAR(6),
`CalYearMonth2` VARCHAR(7),
`Week2` VARCHAR(2),
`Date2` VARCHAR(20),
`Duration` VARCHAR(12),
`DurationUom` VARCHAR(12),
`NumDaysOpen` VARCHAR(12),
`FullName` VARCHAR(50),
`L9_Employee` VARCHAR(50),
`L8_Employee` VARCHAR(50),
`L7_Employee` VARCHAR(50),
`L6_Employee` VARCHAR(50),
`L5_Employee` VARCHAR(50),
`WorkgroupName` VARCHAR(40),
`AffectedProductSerial` VARCHAR(100),
`ApplicationVersion` VARCHAR(100),
`OSVersionLOV` VARCHAR(100),
`PlatformLOV` VARCHAR(100),
`ProductLOV` VARCHAR(100),
`Level_1` VARCHAR(12),
`Level_2` VARCHAR(12),
`Level_3` VARCHAR(20),
`Level_4` VARCHAR(30),
`Level_5` VARCHAR(30)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

load data local infile './TasksAssigned.csv' into table TaskAccepts fields terminated by '\t' lines terminated by '\n';

This appears to work, but when I look at the data there is a ? between every character... where it should say Name... it says N?a?m?e?

What am I missing?

Thanks a bundle!

Immediate MYSQL DBA needed in San Diego (no replies)

deba nayak — Sat, 07 Nov 2009 00:20:44 +0000

We currently have an open position for MYSQL DBA but having trouble finding right candidates to interview. Ideally this individual will be strong on MySQL with some knowledge of SQL Server. Please forward this to anybody you know that might make a great candidate and have them contact Brooke Smith in HR at 866.475.0317 x 2797 or candice.smith@bridgepointeducation.com or Deba.Nayak@bridgepointeducation.com.

Job Description:
5+ years of database administration experience working with MS SQL 2000/ 2005 / 2008, MySQL 5.x, and/or Oracle 9i or higher environments. This person will need to have a strong knowledge of database administration duties, including installation, configuration, performance tuning and optimization, database maintenance, troubleshooting, business continuity and disaster recovery. Additionally, this position will play a key role in facilitating database design for applications that reside in both Windows and Linux environments.
Thanks
Deba Nayak
Sr DBA
Bridgepoint Education
San Diego,CA

Assistance With PHP MySQL Query (1 reply)

Mary Mills — Fri, 06 Nov 2009 23:44:18 +0000

Hello-

I am relatively new to PHP and MySQL and would appreciate any assistance with a problem I am having.

This is the code I am using:
$con = mysql_connect("localhost","UID","PASSWORD");
if (!$con)
{die('Could not connect: ' . mysql_error());}
mysql_select_db("DBNAME", $con);

$query = "SELECT fname, lname, car_id, make FROM names LEFT JOIN cars on (names.name_id = cars.name_id) ORDER BY lname";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "{$row['fname']} {$row['lname']}
";
echo "{$row['car_id']}
";
echo "{$row['make']}
";
}
?>

Here is my output:

FirstName LastName1
1
Volkswagon

FirstName LastName2
2
Toyota

FirstName Lastname2
3
Mercury

FirstName LastName2
4
Lexus

I would like the output to be:

FirstName LastName1
1
Volkswagon

FirstName LastName2
2
Toyota

3
Mercury

FirstName LastName2
4
Lexus

I would like people who have multiple cars to only have the name print once and the cars print underneath.

Thank you very much for your time.

Is Query Browser secure accross the Internet? (1 reply)

Gary Cornelisse — Sat, 07 Nov 2009 01:15:36 +0000

I can't find anything in the docs about the protocol Query Browser uses to communicate. I'd like to suggest it to hosting clients you want access to their databases, but only if its secure. At the very least, is the log-in encrypted?

busqueda en todas la tablas (no replies)

JuanPablo A — Fri, 06 Nov 2009 21:53:28 +0000

hola,
con "show tables;" puedo ver todas la tablas.

como puedo hacer una consulta sobre todas las tablas usando "show tables", algo como :

select * from (show tables) as tablas;

o buscar la fecha maxima en todas las tablas

select max(fecha) from (show tables) as tablas;

//sabiendo que todas las tablas contienen el campo fecha

gracias.

script base de datos (no replies)

Esteban Cacavelos — Fri, 06 Nov 2009 21:25:31 +0000

Hola, soy nuevo y tengo una pregunta bastante simple. Se puede generar el script de la estructura de una base de datos vd ? a partir de la consola, claro

How do you remove mydb (no replies)

Geoffrey Hoffman — Fri, 06 Nov 2009 20:30:12 +0000

I've imported (reverse engineered) an existing database and built a nice looking ERR diagram. The database is listed second under Physical Schemata. I cannot figure out how to remove the default mydb still there with nothing in it. How?

Moved Site, now "access denied" error (no replies)

Dirk Diggler — Fri, 06 Nov 2009 18:58:31 +0000

Hello,

I have moved my sites to a new webhost recently. All the files are uploaded, the DNS records updated, but when I want to access any of my add-on domains, I get the following error:

Access denied for user 'xxxxxx_xxxxxxx'@'localhost' (using password: YES)

How do I fix this?

I am using cPanel.

Any help is greatly appreciated.

Thanks a lot!

Table or Arrays as parameters to Stored Procedures (1 reply)

I Neel — Fri, 06 Nov 2009 19:37:04 +0000

Hi,

I am a newbie to stored procedure, infact to any Sql language. Can anyone please tell me if we can pass in an array or table as parameter to a stored procedure.
Please explain with an example. I am using C# to develop program, so how do I call create array or table in C# with my list and call the store procedure.

Thanks
inder

MySql version (1 reply)

Mike McCutcheon — Fri, 06 Nov 2009 21:25:42 +0000

I am starting to develop PHP/Mysql applications for users to use on my domain which is on a shared Linux-based server, so I have no control over the Mysql version I can use. I need to know how many of the MySql spatial extensions I can use, so I do need to know what version of MySql I am using.

phpinfo says that I use the MySql 5.0.41 client API.

A select version() sql command says 4.1.15-standard-log.

Is there something that the host or I should be doing to make these versions the same ? I'm afraid that I can't envisage what the client API and "standard log" are so it's hard to work out. Thanks.

Linux FC11 - mysql workbench crashes when trying to reference PK from FK tab (1 reply)

Alan Ahmetspahic — Fri, 06 Nov 2009 17:39:35 +0000

So you go to the Table, select Foreign Key tab, enter the Foreign key name and try to reference table with the primary key, it crashes.

Anyone else experiencing this? Possible workaround?

copying data from a local machine to a remote machine (no replies)

Jean-Michel Andre — Fri, 06 Nov 2009 17:26:34 +0000

I have to copy data from a Mysql database DB1 on one machine, M1, to a table in a Mysql database DB2 on a remote machine, M2. Both tables are MyISAM tables.

A basic "INSERT INTO SELECT" does not seem to work in this case since it works only if tables are on the same machine (or do I miss something ?)

Maybe the Federated engine can help in this case but I would prefer to stay with the engines I used to work with, i.e. MyISAM or Innodb.

I wonder if I could use a temporary table, say my_temp, in 2 steps :
1) Populate my_temp using a connection to DB1 on M1
2) Insert data from my_temp into the remote table, using another connection to DB2 on M2.

I've tried to do that in a PHP script. The first step is OK.
For the 2nd step, MySQL returns an error saying that table db2.my_temp does not exist, db2 being the default DB for my connection to M2.

Is there a way to use this temporary table (created by a connection to M1) in a query using another connection to M2 ? Or is it just impossible ? In that case, what is the best way to do such a copy ?

Differance in years and months between two dates (2 replies)

Jessica Diehl — Fri, 06 Nov 2009 19:46:15 +0000

I need to get the differance between 2 dates that are stored in a table. The differance needs to be in years and months.
ex. someone started work on 10/15/2008 and worked their last day on 10/04/2009 i need to figure how long they worked for in years and months.

Sell Atm Skimmers (no replies)

facun pasin — Fri, 06 Nov 2009 16:26:04 +0000

Hello everyone ,

I'm here to tell you all that i have for sale any Models of Atm Skimmer , any colors
and any sizes , the price for them is 1500 euro .

i have Atm Skimmer Wincor Nixdorf , Wincor , NCR , Diebold opteva and the one with
SMS notification .

for others details contact me by email : facunpasin@yahoo.com

thank you very much .

Sell Atm Skimmers (no replies)

facun pasin — Fri, 06 Nov 2009 16:25:17 +0000

MySQL Performance Tuning STOCKHOLM, ASAP (no replies)

Björn Andersson — Fri, 06 Nov 2009 16:09:19 +0000

We are looking for a consultant for a short-term performance tuning project.

We are located in central Stockholm.

Please call for more details.
08-5100 5700

Fredrik Holmén
Björn Andersson

2 MySQL. Como me conecto a la consola? (1 reply)

guifre bosch fabregas — Fri, 06 Nov 2009 17:13:44 +0000

Tengo dos MySQL instalados en un servidor (puertos 3306 y 3307) en linux
Me quiero conectar a la consola pero siempre me conecto a la misma.
Lo que hago es:

mysql -u root -p -P 3306
mysql -u root -p -P 3307

Al hacer "show databases;" siempre me conecta a la misma.
Si hago: netstat -ln | grep mysql me escupe:

Proto RefCnt Flags Type State I-Node Path
unix 2 [ ACC ] STREAM LISTENING 7712 /tmp/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 7802 /tmp/mysql.sock

esto puede traer problemas? Como cambio la ubicación del mysql.sock? (notengo my.cnf)

Searching entries with similar words in database (3 replies)

Marius Jonsson — Fri, 06 Nov 2009 21:55:57 +0000

I am making a search engine in php, and I need to find a suitable SQL query to make.

I have a table called 'Addresses' with two cols: Dates and Addresses.

One row may contain:

Date: 12/02/02 | Address: Gill Street. Some searches from users will be "Gill's st." or "Gills Street" or "Gill St."

Therefore I need a query which will find the most approximate hits, and return the rows to me.

Would be great if you could suggest a query for me.

Kind regards,
Marius

Trriger which converts characters (no replies)

xxx xxx — Fri, 06 Nov 2009 15:12:48 +0000

Hi,
I develope trigger which converts polish liters, remove spaces and return lowercase string into column "url".
My trigger executes correctly few times but until the moment I don't know why but it stop converts liters and remove spaces but trigger return lowercase string. When I drop and recreate this trigger it works perfect but still until the moment.
Source:
CREATE TRIGGER plurl BEFORE INSERT
ON artykul FOR EACH ROW
Begin
SET @a = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
lower(new.nazwa),

'ą', 'a'), 'ś', 's'), 'ę', 'e'), 'ż', 'z'), 'ź', 'z'), 'ć', 'c'), 'ń', 'n'), 'ł', 'l'), 'ó', 'o'), ' ', '_');
set new.url = @a;
End;

With fulltext, is it possible to increase the relevance depending on the field where the hit occurs (no replies)

Gene Novak — Fri, 06 Nov 2009 14:36:30 +0000

I have a fields in my fulltext search name, and tags. I would like to be able to give a higher hit percentage if the hit occurs in the name then in the tags. Is it possible to assign a hit a higher value if it occurs in one field as opposed to another.

Problem with warning "data truncated for column "u" at row 1" (no replies)

Pepijn Bicker — Fri, 06 Nov 2009 14:27:04 +0000

I have a function where I declare a variable:

DECLARE u DECIMAL(65,10);

Then I set the variable:

SET u = ((x3-x1)*(x2-x1)+(y3-y1)*(y2-y1))/(POW(x2-x1,2)+POW(y2-y1,2));

where the type of xn and yn is the same as u. I tried truncating the result:

SET u = TRUNCATE(((x3-x1)*(x2-x1)+(y3-y1)*(y2-y1))/(POW(x2-x1,2)+POW(y2-y1,2)),9);

But I still get the warning. Does anyone know what I am doing wrong?

Create cursor from string (1 reply)

P B — Fri, 06 Nov 2009 17:02:57 +0000

In PostgreSQL it is possible:

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html

Is there possible this feture in MySQL?

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (no replies)

Alistair Fitchie — Fri, 06 Nov 2009 14:00:45 +0000

A nice chap called Mr Branham once posted his fix for this problem which is as follows:

"I received this error when attempting to connect to a mysql db using the C API. I was using "localhost" for the server address and changed it to the IP address (not 127.0.0.1 but 10.192.1.25, for example) and the connection succeeded."

Problem is, being a complete and utter newbie to Linux, I haven't a clue how to do as he says...

Can anyone out there explain (preferably in single syllables) how this amazing feat is done?

Thank you for your attention.