Forum Replies Created
-
AuthorPosts
-
kshahk
Member1) Server: Red Hat Enterprise Linux ES release 4 (Nahant Update 2) x86
Client: Windows Vista Enterprise
2) Direct
3) See below
[root@prodb-1 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
default-character-set=utf8
init-connect='SET NAMES utf8'
query_cache_size=67108864
tmp_table_size=67108864
log-bin=mysql-bin
log-slow-queries=mysql-slow
max_connections=250
thread_cache_size=40
key_buffer_size=67108864
myisam_sort_buffer_size=16777216
sort_buffer_size=8388608
myisam-recover=FORCE,BACKUP
max_heap_table_size=33554432
innodb_support_xa=0
table_cache=256
log_warnings=2
log_error=/var/lib/mysql/mysql-error.err
skip-name-resolve
MemberCorrection: This happnes with SP not create table.
MySQL Version is: MySQL Enterprise Server (GPL) 5.0.44-enterprise-gpl-log
Here is what I get when I go to SP and try to alter it:
DELIMITER $$
DROP PROCEDURE IF EXISTS `art_relations`.`sp_get_release_priority_info`$$
CREATE DEFINER=`theorchard`@`10.10.20.%` PROCEDURE `sp_get_release_priority_info`(IN dms_id TEXT, IN country_ids TEXT, IN theUPC TEXT)
BEGIN
SET @stmtStr := '';
SELECT CONCAT(“SELECT r.upc, r.release_date, r.sale_start_date, min(re.sale_start_date) exclusive_date , r.release_name
, a.name as artist_name, ca.orchard_country as artist_country, cv.orchard_country vendor_country
, count(mpa.upc) as priority_a, count(mpb.upc) as priority_b, count(mpi.info_for_id) as mkt_blurb
FROM art_relations.releases r
INNER JOIN art_relations.artist_info a ON a.artist_id = r.artist_id
INNER JOIN art_relations.artist_contact ac ON a.artist_id=ac.artist_id AND ac.master = 'yes'
INNER JOIN art_relations.contact ca ON ac.contact_id=ca.contact_id
INNER JOIN art_relations.vendor v ON v.vendor_id = a.vendor_id
INNER JOIN art_relations.vend_contact vc ON v.vendor_id=vc.vendor_id AND vc.master = 'Y'
INNER JOIN art_relations.contact cv ON vc.contact_id=cv.contact_id
LEFT JOIN art_relations.release_exclusive re ON re.upc = r.upc AND re.dms_customer_id IN (“,dms_id,”)
LEFT JOIN art_relations.mkt_priority mpa ON mpa.upc = r.upc AND mpa.priority = 'a' AND (mpa.country_id IN (“,country_ids,”) OR mpa.country_id IS NULL)
LEFT JOIN art_relations.mkt_priority mpb ON mpb.upc = r.upc AND mpb.priority = 'b' AND (mpb.country_id IN (“,country_ids,”) OR mpb.country_id IS NULL)
LEFT JOIN art_relations.mkt_program_info mpi ON mpi.info_for = 'release' AND mpi.mkt_program_id = 15 AND mpi.info_for_id = r.upc AND mpi.scope = 'public'
WHERE r.upc IN (“,theUPC,”)
GROUP BY r.upc”) INTO @stmtStr;
PREPARE stmt FROM @stmtStr;
EXECUTE stmt;
END$$
DELIMITER ;
And here is what comes when I try to export it as sql dump.
/* Procedure structure for procedure `sp_get_release_priority_info` */
/*!50003 DROP PROCEDURE IF EXISTS `sp_get_release_priority_info` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`theorchard`@`10.10.20.%` PROCEDURE `sp_get_release_priority_info`(IN dms_id TEXT, IN country_ids TEXT, IN theUPC TEXT)
BEGIN
SET @stmtStr := '';
SELECT CONCAT(“SELECT r.upc, r.release_date, r.sale_start_date, min(re.sale_start_date) exclusive_date , r.release_name
, a.name as artist_name, ca.orchard_country as artist_country, cv.orchard_country vendor_country
, count(mpa.upc) as priority_a, count(mpb.upc) as priority_b, count(mpi.info_for_id) as mkt_blurb
FROM aeleases r
INNER JOIN artist_info a ON a.artist_id = r.artist_id
INNER JOIN artist_contact ac ON a.artist_id=ac.artist_id AND ac.master = 'yes'
INNER JOIN aontact ca ON ac.contact_id=ca.contact_id
INNER JOIN aendor v ON v.vendor_id = a.vendor_id
INNER JOIN aend_contact vc ON v.vendor_id=vc.vendor_id AND vc.master = 'Y'
INNER JOIN aontact cv ON vc.contact_id=cv.contact_id
LEFT JOIN aelease_exclusive re ON re.upc = r.upc AND re.dms_customer_id IN (“,dms_id,”)
LEFT JOIN akt_priority mpa ON mpa.upc = r.upc AND mpa.priority = 'a' AND (mpa.country_id IN (“,country_ids,”) OR mpa.country_id IS NULL)
LEFT JOIN akt_priority mpb ON mpb.upc = r.upc AND mpb.priority = 'b' AND (mpb.country_id IN (“,country_ids,”) OR mpb.country_id IS NULL)
LEFT JOIN akt_program_info mpi ON mpi.info_for = 'release' AND mpi.mkt_program_id = 15 AND mpi.info_for_id = r.upc AND mpi.scope = 'public'
WHERE r.upc IN (“,theUPC,”)
GROUP BY r.upc”) INTO @stmtStr;
PREPARE stmt FROM @stmtStr;
EXECUTE stmt;
END */$$
DELIMITER ;
-
AuthorPosts
