How to sync local MySQL database to Amazon RDS database?
I am setting up a connection between MySQL Workbench and Amazon AWS RDS MySQL database. How can I sync the two databases?
The operation (Inserting, deleting and updating) directly happens to cloud (AWS RDS) database instead of storing first on the local database which is further sync to cloud. Because it is taking much time in storing procedure directly to the cloud.
I expect all operations happen first in a local database which then syncs to the cloud database.
You may be interested in using the AWS Database Migration Service, which should be able to meet all of your requirements including remote replication of your local database. (https://aws.amazon.com/dms/)
See also questions close to this topic
Pass a variable into CURSOR's SELECT Query (MySQL)
I'm trying the stored procedure below. However, when i pass the actual column name in 'DECLARE cur1' line, the SP returns correct value but when I pass variable name i.e. input parameter (colName), it returns 0. I've added comments in my code below. Is the code correct?
DROP PROCEDURE IF EXISTS test1.checkHardcodedField; CREATE PROCEDURE test1.checkHardcodedField(IN textValue CHAR(10), colName CHAR(10), OUT counter VARCHAR(100)) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE temp CHAR(16); DECLARE i INT DEFAULT 0; -- Issue with this statement, returns incorrect value. DECLARE cur1 CURSOR FOR SELECT colName FROM data1; -- This statement works. MSH3 is actual column name -- DECLARE cur1 CURSOR FOR SELECT MSH3 FROM data1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur1; read_loop: LOOP FETCH cur1 INTO temp; IF done THEN LEAVE read_loop; END IF; IF temp = textValue THEN SET i = i + 1; END IF; END LOOP; SET counter = i; CLOSE cur1; END;
Adding checkout fields based on product quantity
I followed ACF repeater based on product quantity generating checkout fields in Woocommerce question and the answer is perfect.
In addition, I need to add same number of fields added as quantity of the product. So, if 3 is the quantity then it adds name1, name2 and name3.
I changed into
for( $i = 1, $j = 1; $i <= $item->get_quantity(); $i++, $j++ )and it works fine with all quantity except for 2. When quantity is 2, it messes up as we can see in the next image
How can I fix this?
How do I setup a search that allows users to properties by location, feature and date using sql
I have a tech test with a company where I have been given SQL code that has been done for me.
I have been looking at videos to help me with SQL and I understand the basics and the data types.
But I need some pointers to look at.
I also need to ensure that it can work on a tablet, phone and computer.
CREATE DATABASE IF NOT EXISTS `sykes_interview` DEFAULT CHARACTER SET utf8 ; USE `sykes_interview`; /*Table structure for table `bookings` */ DROP TABLE IF EXISTS `bookings`; CREATE TABLE `bookings` ( `__pk` int(10) unsigned NOT NULL AUTO_INCREMENT, `_fk_property` int(10) unsigned DEFAULT NULL, `start_date` date DEFAULT NULL, `end_date` date DEFAULT NULL, PRIMARY KEY (`__pk`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; /*Data for the table `bookings` */ insert into `bookings`(`__pk`,`_fk_property`,`start_date`,`end_date`) values (1,1,'2020-08-26','2020-09-02'), (2,1,'2020-12-06','2020-12-13'), (3,3,'2021-05-26','2021-06-02'), (4,2,'2019-12-06','2019-12-13'), (5,5,'2019-10-01','2019-10-08'), (6,4,'2021-01-13','2021-01-20'), (7,3,'2020-11-26','2020-11-02'), (8,5,'2020-12-06','2020-12-13'), (9,2,'2021-08-26','2021-09-02'), (10,4,'2020-10-06','2020-10-13'); /*Table structure for table `locations` */ DROP TABLE IF EXISTS `locations`; CREATE TABLE `locations` ( `__pk` int(10) unsigned NOT NULL AUTO_INCREMENT, `location_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`__pk`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `locations` */ insert into `locations`(`__pk`,`location_name`) values (1,'Cornwall'), (2,'Lake District'), (3,'Yorkshire'), (4,'Wales'), (5,'Scotland'); /*Table structure for table `properties` */ DROP TABLE IF EXISTS `properties`; CREATE TABLE `properties` ( `__pk` int(10) unsigned NOT NULL AUTO_INCREMENT, `_fk_location` int(10) unsigned DEFAULT NULL, `property_name` varchar(255) DEFAULT NULL, `near_beach` tinyint(1) unsigned DEFAULT NULL, `accepts_pets` tinyint(1) unsigned DEFAULT NULL, `sleeps` tinyint(3) unsigned DEFAULT NULL, `beds` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`__pk`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `properties` */ insert into `properties`(`__pk`,`_fk_location`,`property_name`,`near_beach`,`accepts_pets`,`sleeps`,`beds`) values (1,1,'Sea View',1,1,4,2), (2,3,'Cosey',0,0,6,4), (3,5,'The Retreat',1,0,2,1), (4,5,'Coach House',0,1,5,3), (5,4,'Beach Cottage',1,1,8,6);
add AWS API gateway mapping template from sam template or swagger 2.0
I am creating a aws based app with sam template So, I have my custom lambda authorizer so I need to add mapping template to the methods and i cant find a template that can do that
AWS Elastic Beanstalk, wont update django.config with "eb deploy"
I've deployed a API application to AWS Elastic Beanstalk written in python and django. I'm now developing the frontend from which I'm trying to send a simple get request to the AWS python server. Trying different solutions to the CORS header issue I added:
files: "/etc/httpd/conf.d/cors.conf" : mode: "000644" owner: root group: root content: | <Location "/"> Header set Access-Control-Allow-Origin: * Header set Access-Control-Allow-Methods: "POST, GET, PUT, DELETE, OPTIONS" Header add Access-Control-Allow-Headers: "Authorization, Content-Type, Accept" Header set Access-Control-Allow-Credentials: true SetOutputFilter DEFLATE </Location>
to the django.config file which made it work on AWS but not locally (the django.config file is located in .ebextensions file). I decided to instead use the 'django-cors-headers' middleware so that it would work both locally and on AWS. But now it wont work on AWS since it registers multiple Access-Control-Allow-Origin domains.
So after removing the above files: code from django.config the problem persisted so the reasonable problem would be that the header is added both by the middleware and by the django.config code which I'm not able to remove.
Could someone help me find a solution?
Terraform quickest way to import multiple resources
My Terraform state file is messedup. Resources are already available on AWS. When I run terraform apply command I am getting multiple "Already Exists" error same as below.
aws_autoscaling_group.mysql-asg: Error creating AutoScaling Group: AlreadyExists: AutoScalingGroup by this name already exists - A group with the name int-mysql-asg already exists
When I do terraform import then it goes away. but I have hundreds of resources which is giving error. What is the best way to sync terraform state and make terraform apply successful?
- MySQL Workbench : create shortcut Select top 1000 rows
MySQL polygon search procedure
I am in MySQL 5.7 I have been trying to get to the bottom of a procedure that isn't working for me, and I'm finding that I'm getting null values which shouldn't be null. The procedure is for finding which area described by a polygon table called csatest1 a point falls within, points are within a table called gsatest1. I just need to find the first csatest1 polygon than encloses a point which is a user input.
Instructions to create and test a test data set that matches mine:
create table gsatest1 (element_id integer, y double, xpanel1 double, xpanel2 double, xpanel3 double, xpanel4 double); Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(11,-4, -5,-6,-7,-8); Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(13,-2, -5,-6,-7,-8); Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(14,-15, -5,-6,-7,-8); Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(15,-28, -5,-6,-7,-8); Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(16,-1500, -5,-6,-7,-8); Create table csatest1 (area_id integer, Poly polygon,testtext varchar(300)); set@testpoly1=ST_GeomFromText('POLYGON (( -1000 -1000, -1000 10, 10 10, 10 -1000, -1000 -1000 ))'); set@testpoly2=ST_GeomFromText('POLYGON (( -1000 -2000, -1000 10, 10 10, 10 -1000, -1000 -2000 ))'); set@testpoly3=ST_GeomFromText('POLYGON (( -2000 -2000, -2000 20, 20 20, 20 -2000, -2000 -2000 ))'); INSERT INTO csatest1 SET Poly = @testpoly1, area_id = 1, testtext = ST_astext(@testpoly1); INSERT INTO csatest1 SET Poly = @testpoly2, area_id = 2,testtext = ST_astext(@testpoly2); INSERT INTO csatest1 SET Poly = @testpoly3, area_id = 3,testtext = ST_astext(@testpoly3); #test that table of polygons csatest1 is working as planned Set @ele_no = 2; Set @poly = (select poly from csatest1 where area_id=@ele_no); set @texttest = (select testtext from csatest1 where area_id = @ele_no); Select @ele_no,@poly,@texttest;
My Procedure, which runs without errors but returns null values is below. It returns the point value as text without a problem, but the polygon value as coming through as null for some reason I can't figure out.
CREATE DEFINER=`root`@`localhost` PROCEDURE `polygon_matcher`(IN gsa_ele_id integer, IN panel_no integer) BEGIN Declare done boolean default 0; Declare xval double; Declare yval double; declare i integer default 1; declare text1 varchar(300); declare gsa_pointxy point; Declare textgsa_pointxy varchar(300); declare csaele_no integer; declare poly polygon; declare textcsa_polygon varchar(500); declare ysno integer default 0; #declare the cursor, cursor goes through row by row the table with polygons Declare rows cursor for Select area_id from csatest1; #declare continue handler DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; # create a table for results CREATE table datadump (CSA_element_ID integer, textcheck_point varchar (300) , textcheck_polygon varchar (300) , container double); #find y value only depends on gsa element ID Set yval = (select y from gsatest1 where element_id=gsa_ele_id order by element_id limit 1); # extract x value for particular panel number specified by user set xval=( SELECT CASE panel_no WHEN 1 THEN xpanel1 WHEN 2 THEN xpanel2 WHEN 3 THEN xpanel3 WHEN 4 THEN xpanel4 ELSE NULL END FROM gsatest1 order by element_id asc limit 1); # make gsa data into a point set text1 = concat('POINT (',xval,' ',yval,')'); set gsa_pointxy = ST_geomfromtext(text1); #convert to text for testing set textgsa_pointxy = ST_astext(gsa_pointxy); #OPEN THE CURSOR OPEN rows; # tell it to loop through all rows repeat #get element number from polygons table into cursor FETCH rows into csaele_no; set poly = (select poly from csatest1 where area_id=csaele_no order by area_id asc limit 1); set textcsa_polygon = ST_astext(poly); # return 1 or zero depending on if statment is true set ysno = (Select MBRContains(poly , gsa_pointxy)); # put data into results table Insert into datadump (CSA_element_ID, textcheck_point, textcheck_polygon, container) Values (csaele_no, textgsa_pointxy, textcsa_polygon, ysno); UNTIL done END REPEAT; #close the cursor CLOSE rows; END
Then I called this procedure as follows: call polygon_matcher (14,2);
+----------------+-----------------+-------------------+-----------+ | CSA_element_ID | textcheck_point | textcheck_polygon | container | +----------------+-----------------+-------------------+-----------+ | 1 | POINT(-6 -15) | NULL | NULL | | 2 | POINT(-6 -15) | NULL | NULL | | 3 | POINT(-6 -15) | NULL | NULL | | 3 | POINT(-6 -15) | NULL | NULL | +----------------+-----------------+-------------------+-----------+
How Do i get the heights ranked Decade of a movie?
I'm new to my SQL I came across a question which I couldn't solve i will list down the tables and what I attempt to do so please help!
The question is: Which decade has the highest average ranked movies?
The tables: movies
+---------+-----------------------------------------------------------------------+------+ | movieid | title | year | +---------+-----------------------------------------------------------------------+------+ | 1672052 | 12 O'Clock (1980) | 1980 | | 1672111 | The Room Dundee II (1988) | 1988 | | 1672580 | Lost Soul (1997) | 1997 | | 1672716 | Home alone (1986) | 1986 | | 1672946 | Mr Ben (2014) | 2014 |
+---------+------+--------+--------------+ | movieid | rank | votes | distribution | +---------+------+--------+--------------+ | 1672052 | 7.8 | 8111 | 0000001222 | | 1672111 | 5.3 | 32183 | 0001221000 | | 1672580 | 4.4 | 1894 | 0011110000 |
SELECT movieid, avg(r.rank) FROM movies INNER JOIN rate on movieid = movieid GROUP BY rank ORDER BY avg(r.rank)
What I am expecting to get is the decade that has the highest avg ranked movie.
How to get full-resolution images of amazon products?
I need to get a product info, including it's images in acceptable resolution. Everything except images is already done. Seems like the images are limited to 500px by the longest side.
Even in this example, if the size part is removed from it's url, we'll get a 375x500 image.
But if we visit the page of the product, we'll see that the original image is much larger.
So how do i get full-res images from amazon? I could parse them manually, but it's not looking like a good way to solve the task. Any other ideas? Maybe there is a third-party amazon api?
Amazon Fire Tablet - Do WorkManager PeriodicWorkRequest Work?
Do WorkManager PeriodicWorkRequests work on Amazon Fire tablets? I can't seem to find any documentation indicating that it wouldn't, but I'm not seeing it work on my Amazon Fire 7 device running FireOS 18.104.22.168.
I'm testing an application I wrote that uses one, and I can't get AlarmManager or WorkManager to do something periodically when the tablet is in any state (including at the lock screen with the screen being off).
Has anyone been able to use these workers to accomplish a periodic task on the Amazon Fire tablets?
The same WorkManager code is working on an official Android tablet, so I'm not sure what's going on here.
How to create a amazon cart pre-filled?
i'm creating a website where my users will be able can head to the cart with all the items and my associate tag. I'm using this link model for now. Is it still working as i didn't get the money on few orders that were made by my link.
http://www.amazon.com/gp/aws/cart/add.html ?AssociateTag=your-tag &ASIN.1=B003IXYJYO &Quantity.1=2 &ASIN.2=B0002KR8J4 &Quantity.2=1 &ASIN.3=B0002ZP18E &Quantity.3=1 &ASIN.4=B0002ZP3ZA &Quantity.4=2