|
|
I can't tell if mysql clustering could be a solution(I am thinking about galera or xtradb clustering). But my biggest problem is about the unstable internet connection.
Actually it is planned as multi-tenant setup with a single online mysql server and the tenant is separated by schema, and a tenant can also have their own local database on their location.
But I want to solve this problem first.
For example I have a tenant, but this tenant has a multiple collocation, and want to sync the data on every location and also on the online server.
They can read/write on the first, second, etc collocation server using a desktop app(respective to their location) and they can also read/write on the online server using a mobile app.
Now my problem is how could I sync them, when internet is available? So data written and updated on the first location will propagate to other server, on the online server on the second server and so on, and vice-versa.
P.S. Data deletion will be soft-delete.
Thanks.
|
|
|
|
|
|
Thanks. A very nice concept, I definitely want to try that.
|
|
|
|
|
Well I had a simple table that contained 2 contact records, in which I was able to do a join and get the 2 contacts to send emails to.
Th entire program was based in this everywhere
Then the customer wanted to be able to do 3, 4 and 5 contacts to send emails to, So I said OK to make it unlimited.
I created a new table with 1 contact record.
So now I have an invoice that is a single record, and a list of contacts that can be multiple.
I forgot about the double record if my join has 2 contacts, so that's out the window.
I have no clue what would be the best solution, perhaps start again on the design; maybe someone can point me in the right direction here.
My last resort may be loading all the contacts in a 2nd query, and using linq to match and populate.
<pre>queryString += _
" UNION ALL " & _
" SELECT " & _
" h.FINVNO " & _
", h.FCUSTNO " & _
", h.FCOMPANY " & _
", h.FSALESPN " & _
", h.FSHIPVIA " & _
", h.FORDDATE " & _
", h.FSHIPDATE " & _
", h.FNTAXAMT " & _
", (SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE h.FCUSTNO = a.FCUSTNO) AS FADDR1 " & _
", (SELECT j.FPRINT FROM ARCEMJN.dbf j WHERE h.FINVNO = j.FINVNO) AS FPRINT " & _
", (SELECT j.FSENT FROM ARCEMJN.dbf j WHERE h.FINVNO = j.FINVNO) AS FSENT " & _
", (SELECT p.FBATCH FROM ARPRINT.dbf p WHERE h.FINVNO = p.FINVNO) AS FBATCH " & _
", (SELECT ce.FID, ce.FCUSTNO, ce.FCHECK, ce.FNAME, ce.FEMAIL FROM ARCEM02.dbf ce WHERE FCUSTNO = h.FCUSTNO) AS CONTACTS " & _
" FROM ARINV01H.dbf h " & _
" WHERE h.FSHIPDATE = @startDate "
Here, I started using List to return the records, so I can do further processing
I made a model with the contacts as a list. I'm in uncharted waters here, and I'm not sure if I do get the SQl to work, that I can populate the contacts using this method.
pResults.Add(New model_invoice_index() With {
.FINVNO = If(reader.IsDBNull(0), String.Empty, reader.GetString(0)),
.FCUSTNO = If(reader.IsDBNull(1), String.Empty, reader.GetString(1)),
.FCOMPANY = If(reader.IsDBNull(2), String.Empty, reader.GetString(2)),
.FSALESPN = If(reader.IsDBNull(3), String.Empty, reader.GetString(3)),
.FSHIPVIA = If(reader.IsDBNull(4), String.Empty, reader.GetString(4)),
.FORDDATE = If(reader.IsDBNull(5), New DateTime(89, 1, 1), reader.GetValue(5)),
.FSHIPDATE = If(reader.IsDBNull(6), New DateTime(89, 1, 1), reader.GetValue(6)),
.FNTAXAMT = If(reader.IsDBNull(7), 0.0, reader.GetValue(7)),
.FADDR1 = If(reader.IsDBNull(8), String.Empty, reader.GetString(8)),
.FPRINT = If(reader.IsDBNull(9), False, reader.GetValue(9)),
.FSENT = If(reader.IsDBNull(10), False, reader.GetValue(10)),
.FBATCH = If(reader.IsDBNull(11), False, reader.GetValue(11)),
.FCONTACTS = New List(Of model_invoice_contacts()) With {
dsdaasd
}
}
If it ain't broke don't fix it
|
|
|
|
|
I've created a flash animation clip, Username & password also set that file. now i want to integrate this File with my another java application,with giving this files to link & never ask flash file password, because I've only one UI for java application only
now the how to get a key from javadb to flash?
|
|
|
|
|
Isn't flash, like Silverlight, no longer supported in current browsers which should mean you are wasting your effort coding flash animations.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: no longer supported in current browsers
It's not dead yet, but it's not looking too healthy. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
how to retrive data from database in android
|
|
|
|
|
|
Hello,
I'm trying to call the following MySQL SP:
DROP PROCEDURE IF EXISTS tgc.UpdateCustomerBalance;
CREATE PROCEDURE tgc.`UpdateCustomerBalance`(customerId varchar(50), cost Decimal(8,2))
BEGIN
UPDATE Customers SET Balance = Balance - cost WHERE CustomerId = customerId;
END;
with the following C# code:
try
{
using (var context = new Db_Entities())
{
object[] parameters =
{
new MySql.Data.MySqlClient.MySqlParameter("@customerId", customerId),
new MySql.Data.MySqlClient.MySqlParameter("@cost", cost)
};
await context.Database.ExecuteSqlCommandAsync("UpdateCustomerBalance @customerId,@cost", parameters);
}
}
catch (Exception e)
{
throw;
}
But I receive the error:
"Message": "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 'UpdateCustomerBalance 'xyz',0.03' at line 1",
Can anyone help shed some light on this? I've tried all sort and am now seeing tunnel vision.
Incidentally, this works:
await context.Database.ExecuteSqlCommandAsync("UPDATE Customers SET Balance = Balance - " + cost + " WHERE CustomerId = '" + customerId + "'");
All help appreciated.
|
|
|
|
|
|
Richard,
Worked a treat, thank you.
I've trawled the sites looking for a solution but didn't come across the CALL keyword - so very frustrating.
On a side note, from a performance perspective, is there a better way of this being implemented? The solution could take a large throughput and I'm keen to understand any improvements.
Again, thanks for the tip.
JMY.
|
|
|
|
|
How can I convert this query to a view
The query work but i get error message if I try to save as a view - the errors talk about duplicate names ?
SELECT DISTINCT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadrantids2017 AS a
JOIN quadrantids2017 AS b
WHERE a.`date` = b.`date`
AND a.user_ID != b.user_ID
AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30
Thanks in advance
John B
|
|
|
|
|
Every column in a view needs to have a unique name. You've given all four columns the alias "a", which is obviously not unique.
Just remove the four As a alias from the SELECT part of the query, and you should be able to create a view.
SELECT DISTINCT
b.event_id,
b.user_ID,
b.date,
b.Time
FROM
quadrantids2017 AS a
JOIN quadrantids2017 AS b
WHERE
a.`date` = b.`date`
AND
a.user_ID != b.user_ID
AND
time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30
Alternatively, give each column a unique alias.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
So this query now works really well
SELECT DISTINCT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadrantids2017 AS a
JOIN quadrantids2017 AS b
WHERE a.`date` = b.`date`
AND a.user_ID != b.user_ID
AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30
ORDER BY
a.date ASC,
a.Time ASC,
a.user_ID ASC
I have two issues
1 - Its slow, there are 3144 records and its taking 37 seconds to get the result which is 1519 records so I added an index to event,_id, user_id, date and time and its reduced to 24 secs
2 -
It returns the results like this
33501 1 2017-01-01 01:14:59
47829 3 2017-01-01 01:24:49
33503 1 2017-01-01 01:24:48
37787 2 2017-01-01 02:21:13
33504 1 2017-01-01 02:20:43
47837 3 2017-01-01 03:06:19
33505 1 2017-01-01 03:06:17
47838 3 2017-01-01 03:07:30
33506 1 2017-01-01 03:07:28
33507 1 2017-01-01 03:07:42
47840 3 2017-01-01 04:35:12
33510 1 2017-01-01 04:35:11
Im wondering how I might show the related results
ie
47829 3 2017-01-01 01:24:49
33503 1 2017-01-01 01:24:48
37787 2 2017-01-01 02:21:13
33504 1 2017-01-01 02:20:43
47837 3 2017-01-01 03:06:19
33505 1 2017-01-01 03:06:17
47838 3 2017-01-01 03:07:30
33506 1 2017-01-01 03:07:28
33507 1 2017-01-01 03:07:42
47840 3 2017-01-01 04:35:12
33510 1 2017-01-01 04:35:11
Is there any way I can add a group number to each record or something like that ?
John B
|
|
|
|
|
I consider myself a novice with databases. I want to learn more about MySql and would like your recommendations for a good book and other resources. I can find my way around a database and can use some features of Workbench. My most recent task was to create a second table to log changes to a single table in the database. After much searching, I have yet to find clear explanations as how to do this simple task. So, a book that contains examples like this would best suit my needs. The MySql documentation is way above me. So, one objective in learning is to be able to understand and apply the documentation. Thank you for your help.
|
|
|
|
|
|
So this query is driving me a little crazy
I have a table called quadrantids2017 which has a number of events submitted by different contributors, key fields are event_id which is unique and user_ID and datetime
what I want is a query that will bring back all events which ocoured on the same date within 30 seconds of each other but different contributors so here is some data
event ID, user_id, datetime
37775 2 2017-01-01 00:01:23
33500 1 2017-01-01 01:07:56
37776 2 2017-01-01 01:08:45
47827 3 2017-01-01 01:09:07
37777 2 2017-01-01 01:09:44
33501 1 2017-01-01 01:14:59
47828 3 2017-01-01 01:15:00
33502 1 2017-01-01 01:16:10
37778 2 2017-01-01 01:16:47
37779 2 2017-01-01 01:19:29
37780 2 2017-01-01 01:19:36
33503 1 2017-01-01 01:24:48
So i would want a result that looks like this
33500 1 2017-01-01 01:07:56
37776 2 2017-01-01 01:07:45
33501 1 2017-01-01 01:14:59
47828 3 2017-01-01 01:15:00
37780 2 2017-01-01 01:19:36
33503 1 2017-01-01 01:19:48
On issue I have is that my current attempts bring back matches from the same contributor
Help really appreciated
Regards
John B
|
|
|
|
|
Can you post your query and explain what records are returned?
|
|
|
|
|
ok my query is like this
SELECT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadrantids2017 AS a
JOIN quadrantids2017 AS b
WHERE a.`date` = b.`date`
AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30
Note in this version I am using a separate date and time field
here are some of the results I have spaced and marked what should have been returned
37775 2 2017-01-01 00:01:23
33500 1 2017-01-01 01:07:56
37776 2 2017-01-01 01:08:45
37776 2 2017-01-01 01:08:45 - correct
47827 3 2017-01-01 01:09:07 - correct
47827 3 2017-01-01 01:09:07 - correct
37777 2 2017-01-01 01:09:44 - correct
33501 1 2017-01-01 01:14:59
33501 1 2017-01-01 01:14:59
47828 3 2017-01-01 01:15:00
47828 3 2017-01-01 01:15:00
33502 1 2017-01-01 01:16:10
John B
|
|
|
|
|
Looks like you're missing:
AND a.event_id != b.event_id from your WHERE clause.
(You might want to change that to an ON clause for the JOIN.)
If you want to exclude matches from the same user, you'll also need:
AND a.user_ID != b.user_ID
Or, to exclude matches for different users:
AND a.user_ID = b.user_ID
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
so this works fine
SELECT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadrantids2017 AS a
JOIN quadrantids2017 AS b
WHERE a.`date` = b.`date`
AND a.event_id != b.event_id
AND a.user_ID != b.user_ID
AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30
my only query now is the length of time it took the record set its self has 3133 records and the query returned 2990 records but it took some 40 seconds to do ? - I understand its actually doing quite a lot
John B
|
|
|
|
|
spoke to soon, there are duplicates
37919 2 2017-01-01 11:43:02
37920 2 2017-01-01 11:43:23
47890 3 2017-01-01 11:42:56
47890 3 2017-01-01 11:42:56
47894 3 2017-01-01 12:13:31
33558 1 2017-01-01 12:13:29
47896 3 2017-01-01 13:24:44
37937 2 2017-01-01 13:24:17
37945 2 2017-01-01 14:09:53
37945 2 2017-01-01 14:09:53
sorry
|
|
|
|
|
I want to make sure I get the basic design correct from the beginning so help really appreciated.
Im creating a dbase (MySql) that will store Radio Meteor Events.
Individuals collate data on a monthly basis and a contributor may collect anywhere between 4,000 to 8,000 unique events per month, we currently have
6 contributors out of a possible 30 or so.
So planning ahead if all 30 contributed each month we could have around 250,00 event per month
The key purpose is to correlate event capture, so did anyone else capture the same event as me
Each event per contributor is unique and amongst other things each event records a full date yyyy/mm/dd and time HH:MM:SS
Once all this data is in a database there would be quite general queries like how many events on a particular date across all contributors say in the month of Jan how many events across all contributors match with regard to both date and time
Another common slightly more complex query would be to get all events for a particular date where the time matches with a tolerance of say 10 seconds either way
so if we had
Contributor 1 15:00:10
Contributor 1 15:00:30
Contributor 2 15:00:05
Contributor 2 15:01:10
Contributor 3 15:06:10
Contributor 3 15:00:10
It would return
Contributor 1 15:00:10
Contributor 2 15:00:05
Contributor 3 15:00:10
so i was planning two tables
1 - Contributors
2 - Events
Event ID would be unique and the primary key
the link between the two tables would be the Contributor ID
I’m not sure about date and time
I thought for flexibility I could have
Date
Time
Datetime
The data its self is quite simple but given this could grow I want to store it in the most appropriate way to ensure that queries are efficient
Help appreciated
Regards
John Berman
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
