MySQL Forums - General

MYSQL: Load Data Issue (no replies)

Ayaz Alavi — Tue, 21 May 2013 12:10:54 +0000

Following mysql command doesnt impoirt anything into DB,

LOAD DATA INFILE "Database1.csv" REPLACE INTO TABLE business FIELDS TERMINATED BY ',' ESCAPED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (business_name,address,city,state,zip_code,website,phone,contact_name) SET website = nullif(website,''), contact_name = nullif(contact_name,'')

Following is the struct of business table:

CREATE TABLE `business` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`metro_id` bigint(20) DEFAULT NULL,
`contact_name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
`contact_email` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
`business_name` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
`state` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
`zip_code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`website` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
`address` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Following is the sample data

Nail Glamour,7407 Legend Point Dr,San Antonio,TX,78244-2411,,2106665200,Bich-Ha Nguyen
G & S Fire Systems,P.O. BOX 182192,Arlington,TX,76096-2192,,9726479274,Gary Jones
Harold R Moore Dr,764 W Commerce St,Fairfield,TX,75840-1428,,9033894151,Harold Moore
Cerda Roofing,563 N 1st St,Beaumont,TX,77701-1646,,4098357569,
Jason Doerr & Freinds Mem,1201 Wildwood Trl,Lancaster,TX,75146-3530,,9722271769,Jason Doerr
R & D Finishing,P.O. BOX 171,Newark,TX,76071-0171,,8176389128,Raffaele Reitnauer
Wtamu Alumni Assoc,P.O. BOX 60753,Canyon,TX,79016-0001,,8066512311,M Brown
Papa Dantes Italian Restaurant,P.O. BOX 669,Converse,TX,78109-0669,,2106585261,Rosemarie Dante
George P Kirkpatrick Jr,1100 S Pecan St,Kountze,TX,77625-7449,,4092463912,George P Kirkpatrick Jr
Flowers By Alicia,12502 River Trail Dr,Houston,TX,77050-2808,,2819873239,Alicia Thomas
Pepi Alonti Deli,777 Walker St,Houston,TX,77002-5316,,7132249988,Russ Watkins
Anderson Automotive Performanc,305 Tyler,Longview,TX,75601,,9037583881,
Checkrite of Austin,P.O. BOX 42377,Austin,TX,78704-0040,,5124400994,William Archer
Kohler Rental Power,11175 Windfern Rd,Houston,TX,77064-4807,,2814779108,Erik Hansen
Red Iron Fabrication,5029 Ridgeview CT,Fort Worth,TX,76180-7812,,8174286406,David Jones

Difference between "" and localhost in mysql.host (no replies)

Pavel Ondrej — Tue, 21 May 2013 08:37:11 +0000

Hello,

please can you explain me what is differnce between

select Host,Db from mysql.host;

*************************** 1. row ***************************
Host: localhost
Db: %

and

*************************** 2. row ***************************
Host:
Db: %

When is only localhost in host table, so I have problem with select database (sometimes). In users table are all users with host="".

Thank you

Pavel

Can some please help me with a duplicate key error (14 replies)

mike browne — Tue, 21 May 2013 04:19:29 +0000

Hello,

I have been trying to solve this problem for a long time and have not found a solution, so could someone please assist me.

I am getting this error:

[MySQL][ODBC 3.51 Driver][mysqld-4.1.22-community-nt]Duplicate entry '' for key 2

Now as far as I know I only have one index on the table and it is the primary key which is auto-increment. So what is this error really telling me, what is it referring to when it says "For Key 2", I cannot figure out what key2 is.

Also, it shows as a '' which to me looks like a null. However I have every field filled in with something on the insert clause.

Insert clause is:

INSERT INTO ASSETSCHEDULE (AssetID,DateReserved,DateReservedString,CreatDate,CreatDateString,CreatTime,CreatTimeString,TimeReserved,TimeReservedString,ReserveLength,LastRecordedTime,LastRecordedTimeString,PatronBarcode,PatronFirstName,PatronLastName,PatronType,ReserveType,BookingCode,RecordDeleted,BranchLocated,ZoneLocated) VALUES('Table7',41409,'5/15/2013',41409,'5/15/2013',0.59880594907,'2:22:16 PM',0.59791666667,'2:21:00 PM',60,0.59791666667,'2:21:00 PM','Temp Schedule ',' ',' ',' ',1,'T8DJ9B350E7OO23',2,'Main Room',0)

I am at a loss for why this would error off with the above error message. So can someone please assist me, or tell me what to look for.

Thanks for any information

Mike

Linking data together (4 replies)

bob dolle — Sun, 19 May 2013 17:42:31 +0000

Hello. I have been using MySQL for about 2 weeks now. I access and modify the database elements via jquery and php (it is an internal inventory system for my company accessed easily on a web browser).

We develop separate products that "mate" with each other. Example

Serial__Type__Mate
001______A____003_
002______A____004_
003______B____001_
004______B____002_

What I want to be able to do, because we have a lot of units, is to be able to change one cell and have it automatically change the other. So if I changed the Mate data for 001 to 004 I want 004's Mate to change to 001.

I am using one huge database for all our products. So unless someone know's the jquery way of doing this (should it exist) I am wondering what the SQL command would be to acheive it.

I can provide more info if necessary.

Thanks so much.

create table named 2E9 (3 replies)

Roberto Novakosky — Fri, 10 May 2013 04:51:05 +0000

I tried this: (not quoted) (On Versions 5.6.10 and 5.1.42)

CREATE TEMPORARY TABLE 2E84
( ID INT(10) NOT NULL AUTO_INCREMENT,
QUANTIDADE decimal(37,5) DEFAULT NULL,
TOTAL decimal(37,2) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=MEMORY;

MySQL returns error

this way works: (quoted)
CREATE TEMPORARY TABLE `2E84`
( ID INT(10) NOT NULL AUTO_INCREMENT,
QUANTIDADE decimal(37,5) DEFAULT NULL,
TOTAL decimal(37,2) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=MEMORY;

But 2E84 isn't reserved name (or it is ?) and your characters is valid [0-9,A-Z] ..
This is a bug ?

moving mysql database (7 replies)

Varun Nidadavolu — Fri, 17 May 2013 05:35:34 +0000

Hello Everyone,
I am new to this mysql, so need your help regarding the following:

We are planning to move our mysql database from remote server to local server so could you suggest me the best to do this. for this do we require any down time. Both servers are of same configuration and versions.

Regards

Mysql 5.5 and 5.6 slower than 5.0 and 5.1 on selected query (11 replies)

Alex Voytsekh — Mon, 13 May 2013 20:16:38 +0000

grant permission on information_schema (1 reply)

Abdur Rahman — Tue, 07 May 2013 18:00:38 +0000

Hi All,

I want to give access to some user on information_schema.*:

GRANT SELECT ON information_schema.global_status to 'myuser'@'%' identified by 'mypassword';

But it returns the following error even though logged in through root user:

1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

It is happening with this information_schema database only. Can someone help me how to do that? Thanks.

Where to find latest fix pack (1 reply)

Guajita Guajitova — Mon, 06 May 2013 16:01:32 +0000

Hello everybody,

I have MYSQL 5.1.30, in one of the servers, do you know where can I find the latest fix pack?

Do you have a website for this?

Thanks for your replies.

Mysql is loosing privileges (5 replies)

Pavel Ondrej — Sun, 19 May 2013 08:29:41 +0000

Hi,
I have problem on some Centos OS 6 with Mysql.

On some servers, randomly, some random users lose their privileges. Show grants is ok, but when user call "use db", so mysql return perm. problems. After flush privileges is all ok.
Can you help me? Thanks.

Pavel

--------------

mysql> show grants for 3067_jevicky@
-> ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for 3067_jevicky@ |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO '3067_jevicky'@'' IDENTIFIED BY PASSWORD 'XXX' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, TRIGGER ON `XXX\_cz\_1`.* TO '3067_jevicky'@'' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> use thaimay_cz_1
ERROR 1044 (42000): Access denied for user '3067_jevicky'@'' to database 'XXX_cz_1'

T

Events on a Replication Server (4 replies)

David Hicks — Mon, 13 May 2013 21:47:28 +0000

I'm posting this in the general forum, because it's not specifically about replication.

I'm about to set up some Event objects on my Master server. I have a replication server that has some additional databases added to it for archiving purposes, and I would like to add some events to those archive databases. If I turn on event processing on the replication server, it's probably going to also process the events defined on the master, isn't it? Is there any way to avoid that problem? If not, I guess I'll have to drop back out and use cron to execute my jobs.

Thanks,
Dave

using where using filelist (1 reply)

Andrew West — Sun, 28 Apr 2013 21:04:38 +0000

Hello, I run this explain query

EXPLAIN SELECT database1.video . * , database1.users.userid, database1.users.username
FROM database1.video, database1.users
WHERE database1.video.status = 'Successful'
AND database1.video.active = 'yes'
AND database1.video.broadcast != 'unlisted'
AND database1.video.userid = '5410'
AND database1.video.userid = database1.users.userid
ORDER BY date_added DESC
LIMIT 15

that returns

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE video range mykey mykey 32 NULL 16123 Using where; Using filesort
1 SIMPLE users eq_ref PRIMARY PRIMARY 8 video.userid 1 Using where

I've added a index to the table video

ALTER TABLE video ADD INDEX mykey (broadcast,status,active,userid,date_added)

but since it shows Using where; using filesort I guess something is going wrong, what's the problem ?

Thanks

Change Management in MySQL 5.6 or above (2 replies)

Vishal Soni — Tue, 30 Apr 2013 20:20:45 +0000

Are there any change management utilities or tools with MySQL latest version?

RDS MySQL instance runs our disk space temporarily (1 reply)

Rajesh K — Thu, 25 Apr 2013 15:07:13 +0000

Hi,

We have had two instances of our RDS MySQL instance run out of disk space for a few minutes, and then recovers back completely on its own. Any ideas what could be causing this?

Currently, I'm suspecting that this could be caused by temporary files created by a bad query, for which mysql might be creating a large temporary table, which is getting written onto disk. But I'm unable to confirm that, because when the RDS instance runs out of disk space, it doesn't log slow queries either. Is there a way to confirm this? Assuming this is the root issue, one approach is to create the tmp disk on a separate, but that is not possible to be done on an RDS instance, since you don't get shell access. Any other way to limit total temporary table size on disk?

This has brought down our website twice today, and could get out of control quickly. Need urgent help!

Thanks in advance.
Rajesh

flush logs; Unkown error (no replies)

Adam Scott — Fri, 19 Apr 2013 20:32:00 +0000

If you get ERROR 1105 (HY000): Unknown Error after issuing a flush logs; make sure the permissions are set correctly on your log_error file.

Hints
* Find the path of log_error for mysql. Send the following to mysql: show global variables like '%err%';
* Check the permissions and make sure the UID (from ps -C mysqld -f at linux prompt) has permissions to write to the file

Hoping this helps somebody.

Adam

Area "costs" (2 replies)

Max Tsaren — Thu, 18 Apr 2013 16:30:09 +0000

Sorry if I'm posting in the wrong section.
I am just looking for rectangular forms as of the moment so I am trying to get it working with vanilla mysql on phpmyadmin, I want to have a database where there are multiple areas that could potentially overlap each other, every area has a "cost" per square pixel, so you can get the cost at a certain point or measure the cost of an entire area while ignoring the parts that are being overlapped, you could also get the average of the total cost existing in the database.

I am wondering if one of you mysql vets could help me write such a query/database schema, I have already opened a topic on stackoverflow (http://stackoverflow.com/questions/16024462/advanced-mysql-area-costs?noredirect=1#comment22858074_16024462) and received the answer
select sum(c.cost)
from (select x.num as x, y.num as y, max(priority) as maxpriority
from numbers x cross join
numbers y join
costs c
on x.num between c.x and c.x + c.deltax and y.num between c.y + c.deltay
where x.value between PIXELX and PIXELX and DELTAX and
y.value between PIXELY and PIXELY and DELTAY
group by x.num, y.num
) xyp join
costs c
on xyp.x between c.x and c.x + c.deltax and xyp.y between c.y + c.deltay and
xyp.maxpriority = c.priority

which seems to be invalid and riddled with bugs, also no database schema was mentioned. I have put multiple days into trying to correct it and make it work but it keeps returning null.

Thanks in advance, I appreciate it.

Mysql db suddenly stop no log encountered. (9 replies)

Frwa Onto — Sat, 20 Apr 2013 04:01:30 +0000

We have a mysql instance running well and suddenly it stop. I have gone through the logs file but found no indication why it stopped? But there have some instance start and stop before this? Below is the snippet of mysqld.log.

121229 22:17:45 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
121229 22:17:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
121229 22:17:50  InnoDB: Initializing buffer pool, size = 8.0M
121229 22:17:50  InnoDB: Completed initialization of buffer pool
121229 22:17:50  InnoDB: Started; log sequence number 0 206087326
121229 22:17:50 [Note] Event Scheduler: Loaded 0 events
121229 22:17:50 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.66-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
130205 11:09:32 [Note] /usr/libexec/mysqld: Normal shutdown

130205 11:09:32 [Note] Event Scheduler: Purging the queue. 0 events
130205 11:09:34  InnoDB: Starting shutdown...
130205 11:09:36  InnoDB: Shutdown completed; log sequence number 0 529664030
130205 11:09:36 [Note] /usr/libexec/mysqld: Shutdown complete

130205 11:09:36 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130205 11:09:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130205 11:09:37  InnoDB: Initializing buffer pool, size = 8.0M
130205 11:09:37  InnoDB: Completed initialization of buffer pool
130205 11:09:37  InnoDB: Started; log sequence number 0 529664030
130205 11:09:37 [Note] Event Scheduler: Loaded 0 events
130205 11:09:37 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.67-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
130310 11:33:12 [Note] /usr/libexec/mysqld: Normal shutdown

130310 11:33:12 [Note] Event Scheduler: Purging the queue. 0 events
130310 11:33:14  InnoDB: Starting shutdown...
130310 11:33:16  InnoDB: Shutdown completed; log sequence number 0 788753738
130310 11:33:16 [Note] /usr/libexec/mysqld: Shutdown complete

130310 11:33:16 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130310 11:36:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130310 11:36:03  InnoDB: Initializing buffer pool, size = 8.0M
130310 11:36:03  InnoDB: Completed initialization of buffer pool
130310 11:36:04  InnoDB: Started; log sequence number 0 788753738
130310 11:36:04 [Note] Event Scheduler: Loaded 0 events
130310 11:36:04 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.67-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
130413 20:56:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130413 20:56:56  InnoDB: Initializing buffer pool, size = 8.0M
130413 20:56:56  InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 0 1139894636
130413 20:56:56  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 0 1139895853
130413 20:56:56  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 335782050, file name ./mysql-bin.000003
130413 20:56:57  InnoDB: Started; log sequence number 0 1139895853
130413 20:56:57 [Note] Recovering after a crash using mysql-bin
130413 20:56:59 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 809, event_type: 2
130413 20:56:59 [Note] Starting crash recovery...
130413 20:56:59 [Note] Crash recovery finished.
130413 20:56:59 [Note] Event Scheduler: Loaded 0 events
130413 20:56:59 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.67-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

How would you send all queries to a secondary server (6 replies)

Clint Alexander — Sun, 14 Apr 2013 22:09:33 +0000

I have a need to monitor an existing production server by send the same queries it receives to another server and watch queries per second and tune where appropriate on the secondary server.

The front-end is all web-based but none of the code can be updated. Anything I/we do, must be done at the server level. However, we're having trouble identifying the best method to use to send queries to another server.

Is there a known way to do this?

We were thinking something similar to:
tail -f /var/log/mysql/query.log | mysql -h otherserver
(obviously with some formatting and conditionals in between).

But we don't want the log file to get too big.

I'd rather have it piped right to the other server with something like:
general_log_file="|/usr/bin/mysql -h otherserver"

Any thoughts?

datetime warning (3 replies)

Peter Kanas — Wed, 17 Apr 2013 13:32:43 +0000

I don't see what's wrong with the datetime format in the warning below? This record ends up in the table with the event_date set to 2013-03-10 03:00:00. I see this value 282 times which is consistent with the number of warnings coming from mysqlimport - see below.

Each record has as 5 datetime columns. All datetime values are formated using the same awk script. All but the 282 values are loaded correctly. Many have a one digit hour incoming and end up with leading zero for hour and load ok.

I put the awk function that generates the datetime string below and am guessing the hour character are not as expected given the script goes between char and int. Awk usually does the right thing in these cases...
============================================================================
mysql> LOAD DATA INFILE 'c:/$programs/mysql/defect_data_cache/metadata' INTO TABLE metadata FIELDS TERMINATED BY '\t'; S
HOW WARNINGS\G;
ERROR 1292 (22007): Incorrect datetime value: '2013-03-10 02:35:47' for column 'EVENT_DATE' at row 160703
*************************** 1. row ***************************
Level: Error
Code: 1292
Message: Incorrect datetime value: '2013-03-10 02:35:47' for column 'EVENT_DATE' at row 160703
1 row in set (0.00 sec)

==========================================================================
$mysqlimport -L -i grenada metadata
grenada.metadata: Records: 213572 Deleted: 0 Skipped: 0 Warnings: 282
==========================================================================
# string passed to function=3/10/2013 3:35:47 AM

function mysql_date(string) # convert date to mysql format
{ # input format example: 2/18/2013 8:44:06 PM
split(string,a," ")
split(a[2],time,":")
h=time[1]
if (index(a[3],"PM")==1){
h=time[1]+11
}else{
if (time[1]==12){
h=0
}else{
h=time[1]
}
}
split(a[1],date,"/")
return(sprintf("%04d-%02d-%02d %02d:%02d:%02d",date[3],date[1],date[2],h,time[2],time[3]))
}

Please Help with Data Types (2 replies)

Karin Eik — Wed, 10 Apr 2013 04:16:47 +0000

I am needing some help. I have to create 2 additional data types that generate random numbers...one by normal distribution and the other by the Pareto Principle.

Is Database active or inactive in mysql (1 reply)

Mari Selvam — Mon, 08 Apr 2013 16:53:47 +0000

How to i check the what are the databases are active and inactive. Could any one please tel the solution..

No FAQ's - Need help (1 reply)

Harshada Thakur — Sat, 06 Apr 2013 01:00:59 +0000

Hello Admin & Moderators,
I am newbie here & I am searching for the FAQ's over here but I didn't find any.
Actually I just want to know about the rules of this forum because I don't want my post to be called as spam & be deleted for the reason I might not be aware of.
Please do help.

Thanks
Harshada

MySQL over SSH (3 replies)

Christo Gouws — Tue, 09 Apr 2013 04:34:55 +0000

I know it is possible to access mysql over an SSH tunnel.

Are the any implications using mysql over an SSH tunnel
for a high load server in a production environment?

Difference between schema and a database (1 reply)

Chris Bassett — Wed, 03 Apr 2013 13:17:18 +0000

After reading through the documentation for MySQL, I am curious about the difference between a schema and a database?

For example, in the documentation it states that CREATE DATABASE and CREATE SCHEMA are about the same thing.

CAn someone please explain this, or are the two terms pretty much the same thing used interchangeable when talking databases.

how to move INNODB database to another server without stopping MySQL server? (6 replies)

Justin Xu — Wed, 17 Apr 2013 08:35:52 +0000

HI,

My innodb engine databases are running on server A, and I need to moving a database db1 to the new server B . Becasue the service is 7x24 ,I can't stop the servers and application,so some tables in db1 will be kept reading and writing data by applications. the physical table data files of db1 are arroud 1.2TB .
The mysql server is 5.5.27 and OS is solaris 10 x86_64.
I wanted to use Xtrabackup to backup and sync the data from A to B , but as I know the Xtrabackup 2.0 seems not support Solaris.

how about rsync? I don't know if Rsync will be used for the running innodb tables.

Or other way to synchronize the database in running status?

I will appreciate that if anyone can help to give me suggestion about the database moving.

Justin

Problem adding function (4 replies)

Glenn Puckett — Tue, 02 Apr 2013 05:05:53 +0000

I have a function on my production server running MySQL v5.5.8 that executes with no problem.

When I try to create the same function on my test server running MySQL v5.5.9 the create fails with an error 1064. Can someone please explain why it is ok on one server but not on the other?

The create is:

DELIMITER $$

CREATE FUNCTION `CheckProjType` (type INT, cost BIGINT ) 
    RETURNS VARCHAR(50) 
    CHARSET latin1
    NO SQL
BEGIN
    DECLARE rtn VARCHAR(50);
    IF type = 5 THEN SET rtn = 'New Maint';
    ELSE
      IF type = 4 THEN SET rtn = 'Ongoing Maintenance';
      ELSE
        IF type = 3 THEN SET rtn = 'New Development';
        else
          IF cost > 1000000000 THEN SET rtn = 'New Expansion';
          else
            IF cost > 250000000 THEN SET rtn = 'Major Improvement';
            ELSE
              IF cost > 25000000 THEN SET rtn = 'Major Development';
              ELSE
                 SET rtn = 'Small Improvement';
              END IF;
            END IF;
          END IF;
        END IF;
      END IF;
    END IF;

  RETURN rtn;

END $$

DELIMITER ;

The error is:

ERROR 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 '' at line 6

SQL Statement:

CREATE FUNCTION `CheckProjType` (type INT, cost BIGINT ) 

    RETURNS VARCHAR(50) 

    CHARSET latin1

    NO SQL

BEGIN

    DECLARE rtn VARCHAR(50)

What am I doing wrong?

Why I can't delete it? (6 replies)

ilia chinkarev — Thu, 28 Mar 2013 06:23:33 +0000

message deleted

mysqldump 5.6 from 5.1 (OPTION SQL_QUOTE_SHOW_CREATE=1) (2 replies)

maninder bains — Thu, 28 Mar 2013 16:06:14 +0000

I am tryint o take mysqldump from 5.1.28 mysql
of the server where 5.6.1 is installed.

It fails with following error:

C:\Documents and Settings\Administrator>mysqldump -c -h 10.171.170.13 -u root vm
s tbl_billing_success > e:\db_backup\tbl_billing_success20130325.sql
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an er
ror in your SQL syntax; check the manual that corresponds to your MySQL server v
ersion for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line
1 (1064)

Lost connection when accessing table (4 replies)

Steve Wechsler — Fri, 05 Apr 2013 01:53:40 +0000

I have a 16GB innodb table; MySQLdump fails at the same row every time I try to dump the database. I tried running CHECK TABLE, but it also disconnects with the same error (Error 2013: Lost connection to MySQL server during query). I'm using Unix sockets, not TCP/IP, so I don't think this is a server timeout issue (also, what are the odds that it would time out at the same row every time?).

Running MySQL version 5.1.57 Percona

MySQL not closing connections (1 reply)

reddy reddy — Sat, 23 Mar 2013 20:50:11 +0000

I've recently started to work with RabbitMQ and I'm developing a cron-job type of mechanism. Where one script (producer) pushes data from MySQL table to RabbitMQ queue and an another script (consumer script) reads data from rabbitMQ queue and process each record one-by-one. The consumer script never get terminated and it keep listing/checking rabbitMQ queue. In consumer script, I'm using mysql functions to do some database related works.

Till here everything works fine, but, whenever I execute "SHOW Processlist" in mysql then it shows me too many mysql open connections (in Sleep state). And this is all from consumer script. When I try to close them by using mysql_close() function then they still exists (they never get killed). Anyway, this is a bit weired to tell that I'm using non-persistence type connections (but they are still alive). Within a few seconds consumer script creates many connections, so, by changing connection_timeout is never going to work (nor increasing number of connections).

Any idea/suggest for the cause of the problem ?

Anyway, If I kill all mysql processes which are in sleep state for last more than 10 seconds then will it be dangerous ? if then how dangerous it is (as I'm using non-persistance connections) ? I want to update my consumer script to check for all the mysql processes/thread which are open (Sleep) for last more than 10 secs and kill them all.

Thanks in advance for the help.

Reddy