texts:
Morning all,

I have been working on some moderately complex sql to 'obtain' some data from a third party products database, to display it in our own in house applications.

I added in a select to obtain the top record from a table within a subquery (if thats makes sense)

the query took nearly 3minutes to return a final result set of 100 records using

SELECT TOP 1 ...

I had a look online for improvements to what I was trying to achieve and it was suggested that I change my select to use a variable, as below

DECLARE @topCount INT
SET @topCount = 1

SELECT TOP (@topCount) ...

This took the same query from the 3minutes down to 1second, which is great!

But can anyone explain why this is so.


texts: How do I return a json object of my database table

texts:
I'm creating a web client that uses data from my database to plot markers on a google map. So my collumns are id, name, address, lat, lon, description. My client gets the data from a route in my restful server.

I am currently using flask in my python server.  This is the current code on my route on my server.

@testRestServer.route("/dbMap", methods=['GET', 'POST'])
def getData():
    cursor.execute("SELECT * FROM markers ")
    return jsonify(cursor.fetchall())

This is the output of the json i get, however i'm unable to manipulate it correclty to work with my google maps code as it has none of the headers as reference.

[
  [
    1,
    "Test1",
    "york",
    53.95734,
    -1.086833,
    "test1"
  ],
  [
    2,
    "Test2",
    "york",
    53.961533,
    -1.085244,
    "test2"
  ],
  [
    3,
    "Test3",
    "york",
    53.963051,
    -1.078202,
    "test3"
  ],
  [
    4,
    "Test4",
    "york",
    53.963104,
    -1.082185,
    "test4"
  ]
   ]

I'm not sure why its not outputting like this and not with the column names like this which i believe is what i need to be doing.

[
{"name": "test1"
 "address": "york"
  "lat" : "53.963104"}
]

I still need to work out if its best to return the column header as a string or key but if i can try either option and see which works best.
Any assistance on how I could return an output like the latter would be greatly appreciated.

Many thanks

This is the latest code with the error TypeError: 'int' object has no attribute 'getitem'

@testRestServer.route("/dbMap", methods=['GET', 'POST'])
def getData():
    #    cursor.execute("SELECT * FROM locations ")
    #    return jsonify(cursor.fetchall())

    records = cursor.execute("SELECT * FROM locations ")
    payload = []
    content = {}
    for result in range(records):
        content = {'id': result[0], 'name': result[1], 'address': result[2], 'lat': result[3], 'lon': result[4], 'description': result[5]}
        payload.append(content)
        return jsonify(payload)


texts:
This question has been asked before ( Speed impact of using varchar in SQL Server 2008 ), but the answers are not detailed enough to my satisfaction.  Admitedly this is not a current issue and mainly for my education.

However, I will try and make this a concrete example from some current DB schema work I am doing;

Option A

CREATE TABLE [VMware].[TblHostSystem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    <snip/>
    [PowerStateID] [int] NOT NULL
)

ALTER TABLE [VMware].[TblHostSystem]  WITH
CHECK ADD  CONSTRAINT [FK_TblHostSystem_TblPowerState]
FOREIGN KEY([PowerStateID])
REFERENCES [VMware].[TblPowerState] ([ID])
GO

CREATE TABLE [VMware].[TblPowerState](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PowerState] [varchar](50) NOT NULL
)

Option B

CREATE TABLE [VMware].[TblHostSystem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    <snip/>
    [PowerState] [varchar](50) NOT NULL
)

If I've chosen option B (as the previous author of the schema has), as the power of a host is something that will frequently change, what sort of churn will this create?

Some statements that I believe are true,

Varchars are stored in the same row/page on MSSQL 2008.
Varchars are not padded?

So my question is, when updating PowerState in the second example from 'poweredOn' (20 bytes) to 'poweredOff' (22 bytes) what does the DB need to do?

Move the row to a new page because there is now room for the extra 2 bytes?
Move everything after the current row 2 bytes forward because there is a cluster index on ID and the order on disk has to be maintained?
None of the above, the database actually allocates 102 bytes (maximum possible size of varchar(50)) and has loads of free space most of the time to accomodate such changes without massive churn?
Breakout the varchar(50) column to another page to avoid the massive churn caused by 1, and 2, and it doesn't pad out as 3, but it means locking potentially multiple pages per row and two reads off the disk (maybe) rather than one.

Would appreciate a detailed of what happens under the covers!


texts: Database Design: Linking 'vague' entries to associations?

texts:
Experienced developer, inexperienced DBA here.

I'm currently working on a project that lets users input the names of freight vehicles for particular packages. Eg:

Company: White Star Line
Vehicle: The Titanic
Vehicle Type: Ship

At the present time, all three of those fields are unrestricted text fields. So effectively the data is useless for anything except display. If someone enters the exact same details as above, the DB has no means of realising it's the same ship as a record with the exact same entries.

We cannot restrict any of these fields to a set of predefined values (too many potential entries, need to be open to anything), but I'd love to unify some of the more frequently used values. I'm wondering about the most elegant/efficient way of doing this.

My current line of thinking is to add an extra set of database tables with 'recognised company' & 'recognised vehicle', which work in parallel to the existing table. So if a user enters "White Star Line" as the company, and there's a pre-approved entry in the "recognised company" table, it forms an association with that company in an optional 'recognised_company_id' field.

Similarly, if the user enters White Star Line AND 'The Titanic' as ship, and there's an entry for that ship WITH that brand, it enters the association into recognised_vehicle_id.

A few things:

Autocomplete on the input fields could hopefully eliminate some spelling errors/syntax differences on identical brands/vehicles.
Reviewing a count of duplicates of the existing table could help determine which recognised companies/vehicles should be manually added.

This is far from a pretty solution, but it's the best I've got. I'm guessing this is something folks have to do quite regularly, and I'm just wondering - is there a more graceful approach to what I'm trying to achieve? Any rules of thumb for tackling this sort of issue?

Thanks in advance


texts: Change an index order from ascending to descending

texts:
I have a table index in my postgres 9.6 db that I think would be more efficient if the table was sorted descending rather than ascending (as it currently is). The data that I want to test is not in full production use, but could potentially be accessed by users at any time, and I'd rather not take it down if I can avoid it. Two questions:

Is it possible to switch the index from ascending to descending on-the-fly?
If not, I guess I need to drop the index and re-create it. Obviously this will slow down search queries while there is no index. Is that the worst that will happen? Nothing will stop working entirely, right? As I understand it, it will get an exclusive lock on the table before dropping the index, right?

Apologies for the n00b question, I'm quite new to databases. Please be gentle with me!


texts: Restrict user to only take COPY_ONLY backups

texts:
Is there a way to force users to only take a full backup with COPY_ONLY so they don't interfere with differential backups?

Our developers need to take backups every now and then but they don't always remember to use the COPY_ONLY option and then they move the backup away so it causes a few issues.


texts:
I am trying to load a dump file into a SQL Server table using bcp.exe but it fails to insert one row.

Input Data File

Content [\\\\SERVER\INPUT_SOURCE_FILE.DAT]:

COL001|||COL002|||COL003XXDATA001|||DATA002|||DATA003XXDATA004|||DATA005|||DATA006XXDATA007|||DATA008|||DATA009

Column Delimiter is: ||| and row terminator is: XX

Input Table Format

bcp command

bcp.exe [MY_DB].[dbo].[MY_INPUT_TABLE] IN "\\SERVER\INPUT_SOURCE_FILE.DAT" -c -F2 -k -C ACP -t||| -rXX -U sa -P SERVER_PASSWORD -S DBSERVER\INSTANCE

bcp output

Starting copy...

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered
in BCP data-file

2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total: 16     Average : (125.00 rows per sec.)

Results

Rows loaded:

but this row is not inserted:

DATA007 | DATA008 | DATA009 |

Why does it only add two rows instead of three?

I have added \r\n at the end of the line in order to put EOF char. I have even tried using a different delimiter(*) but still getting the same issue. I also tried adding a final XX:

COL001|||COL002|||COL003XXDATA001|||DATA002|||DATA003XXDATA004|||DATA005|||DATA006XXDATA007|||DATA008|||DATA009XX

..but it did not work either.


texts: Is two unrelated tables with foreign keys to a third table breaking first normal form?

texts:
I was wondering if this breaks first normal form.

AddressTable
  Id           - PK
  Street
  City
  State

BusinessTable
  Id           - PK
  BusinessName
  AddressId    - FK

PersonTable
  Id           - PK
  FirstName
  LastName
  AddressId    - FK

The reason I ask is that I've heard that because any given field on the address table may be a business address or a person address, you have two different data types in the same column.  Under that argument, I'd need to have a BusinessAddress and a PersonalAddress table.  At the same time, this database seems to be very well ordered, and I'm use to databases that break first normal form being far less sensible, and it doesn't appear easy for an anomaly to appear.  The closest thing to an anomaly appearing would be if a Business and Person linked to the same address, which then creates a problem if one of them updates their address but the other doesn't (or they could point to two different records with the same data in each except for the id, which might also be an anomaly).


texts: How can I find last access time for users or specific users to all databases in SQL Server

texts:
I have a SQL Server with more than 100 Databases and I should manage the server and it's space usage, users can restore and use backups they have on the server, I have a software for this server,if users use the software to restore the databases, then the software is monitoring and deleting databases older than a specific time and there is no problem, but in some exceptions (especially for large databases) we should manually restore the DBs and the software does not have any information about it.

The problem is, we need a way to detect not just old restored databases, but the least recently used ones.
I searched and found things like this Script that I can extend to get information about all databases.
what I would like to know is, if there is any other DMV or set of DMVs, that I can use to query the "last access time for each database on a server".

I have a SQL Server 2008R2 and a SQL Server 2012 that I need to get this kind of information from.

Appreciate your help.


texts: Unsent log behaviour in database mirroring

texts:
Need your suggestion on how below works.

Situation: We had unsent log on Principal stuck for almost 10 hours with unsent data around 200 GB.

Action: Paused and resumed the mirroring. This kicked of the stuck mirroring. All 200 GB is now send to mirror and its trying to restore.
But when new transactions are showing up on principal i see that again the records or mirror seems to stuck showing send rate 0 KB/sec.

So my question would be , is this suppose to happen and block unsent log on P till all previous 200 GB is restored on Mirror or there could be different problem here? And can we again PAUSE and RESUME mirroring if mirroring is still synchronizing ?

Please suggest


texts: Why is a COUNT query faster than a result set query?

texts:
Suppose I have two similar queries, with complex joins; query 1 returns rows and columns:

SELECT col1, col2, col3, ...etc
FROM MyBigTable
   LEFT JOIN AnotherTable1 On ... etc
   LEFT JOIN AnotherTable2 On ... etc
WHERE someCol1= val1
AND someCol2= val2
AND etc...

and query 2 returns just the count of rows as per query 1:

SELECT count(MyBigTable.PKcol)
FROM MyBigTable
   LEFT JOIN AnotherTable1 On ... etc
   LEFT JOIN AnotherTable2 On ... etc
WHERE someCol1= val1
AND someCol2= val2
AND etc...

Self-evident logic tells me the SQL engine (of most databases, say SQL Server, MySQL, IBM DB2, whatevs...) will return from the count query faster
than the result set query – at least when we are talking big result sets.
Obviously transferring lots of rows and columns across a network will take longer than just a single scalar value of count!

My questions are:

Does the DB Engine have to do similar amount of work / effort for both queries?
If so, is the bottleneck (or delay in receiving the results) simply due to transferring bigger data across a network?


texts:
I have a select statement which is followed by a union. Inside the select, there is a where clause. I am trying to add in the following code to the where clause but I'm having an issue getting it to work out:

declare @BudgetFilter int;
set @Budgetfilter = 0;

AND CASE @BudgetFilter WHEN 0 THEN ISNULL(Project.Contribution, 0) >= 25000 END

The error is with the >. Error message: "Incorrect syntax near '>'."

Some additional background. Budget filter may be one of 3 values. 0,1,2 which translate into >=25K, <25K, <0 respectively.
There are other filters(dropdown value selections) that i am working once I get past this issue.

Thanks.
A


texts: Information about youngest child with inner subquery

texts:
Consider a table like this :

CREATE TABLE people (
  id INTEGER NOT NULL PRIMARY KEY,
  motherId INTEGER REFERENCES people(id),
  fatherId INTEGER REFERENCES people(id),
  name VARCHAR(30) NOT NULL,
  age INTEGER NOT NULL
);

Populated with data like this :

INSERT INTO people(id, motherId, fatherId, name, age) VALUES(1, NULL, NULL, 'John', 50);
INSERT INTO people(id, motherId, fatherId, name, age) VALUES(2, NULL, NULL, 'Marry', 50);
INSERT INTO people(id, motherId, fatherId, name, age) VALUES(3, 2, 1, 'Bob', 30);
INSERT INTO people(id, motherId, fatherId, name, age) VALUES(4, 2, 1, 'Alice', 20);

The expectation is to have output like this (order does not matter) :

 name  | age
-------+-----
 John  | 20
 Marry | 20

The explanation for the output is John and Marry are parents and have two children: Bob and Alice. Alice is younger and is 20 years old.

This is my solution :

select parents.name as name, kids.age as age from people kids
  inner JOIN
  (
    SELECT id, name FROM people where motherId is null and fatherId is null
  ) parents
  ON (parents.id = kids.motherId OR parents.id = kids.fatherId)
  group by parents.id

However this trips when several generations of parents have the same name.  Any ideas why this might be happening?


texts: SQL Server: Grant access to all views in schema, but not tables

texts:
I have tables and views in a single database under a specific schema.  For simplicity, here is an example:

[DBXYZ].[SCHEMAXYZ].[TABLEWITHRESTRICTEDDATA]
[DBXYZ].[SCHEMAXYZ].[SAFEVIEW1]
[DBXYZ].[SCHEMAXYZ].[SAFEVIEW2]

I would like to grant SELECT access to a certain set of users to the views in schema "SCHEMAXYZ" but not the tables.  I prefer not to grant access to specific views because it means more administrative maintenance in the future when new views are added.

I realize that one option is simply to use a different schema name for the views (e.g. "SCHEMAXYZ_VIEWS") but that is not my preference.

Is this possible?


texts: Select Changed Rows in MySQL and Postgres without Timestamp column

texts:
I have the following scenario:
I have 2 source systems, one running Postgres, the other running MySQL. An ETL process (using Azure DataFactory and Azure Integration runtimes) runs the Extract for a number of Tables in each system, pulling them into an Azure SQL Database (where the Transformation and eventual load happens).
Most of the tables do not have a TimeStamp for last modified (or similar) field.
The current process involves truncating the destination tables and then bulk loading the entire table into the destination DB.
This process takes about 15-20 minutes all up, however we are looking at expanding the tables and columns we import to run some additional reporting, to the point where it might adversely affect the time the process takes.
Is there a quick or elegant way that this process can be changed to only select the new and updated rows for importation?


texts: Parent -> Child -> Grandchild with a foreign key back to Parent, Bad Practice?

texts:
Basically the foreign key on options is there to make it so that if you want to know what product an option belongs to you don't have to go through variants.

A variant is like Color, Size Etc.. An option is like Medium, Large, Red Blue.

Is this a bad design choice?
Is it sometimes okay to do this?

The more I think about it, it seems like it's a bad idea just for the sheer fact that blue, when related to the product has no meaning without also being tied to the variant. This option loses any true meaning without the variant so the shortcut is never really worth taking?


texts: Include non-inserted rows as result of INSERT INTO ... RETURNING

texts:
I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion.

For example

CREATE TABLE all_sums (sum INTEGER);
SELECT
    CASE WHEN a_sum IS NULL THEN null
         ELSE (SELECT sum FROM (INSERT INTO sums (sum) VALUES (sum) RETURNING sum))
    END
FROM
    (SELECT a + b FROM (VALUES (1, null), (null, 2), (2, 3)) AS row (a, b))
AS a_sum;

should result in the table all_sums looking like:

all_sums: sum
         ------
           5
         (1 row)

but the output of the query should be:

 null
 null
 5
------
(3 rows)

This example fails due to a syntax error:

ERROR:  syntax error at or near "INTO"

Is there any way to achieve the desired query output?

(For context: My reason for doing this is because there are further queries which rely on knowing whether the insertion occurred for a particular row.

This is part of an effort to insert some data more efficiently from a file by transposing my queries from one per row to one per column. I'm not looking for other tips in improving insert speed though and if it's not possible I'm happy to call it a day at this point.)


texts:
I am often requested to upload data from file (let's say csv) into a Sql Server 2008 db.

I would like to use BULK INSERT, but I have no access to the db server file system.

Questions

is there a way to pass/upload a remote file to the Sql Server in order to use the BULK INSERT?
is there any other tsql way to import data from file to db?


texts: "The schema script could not be propagated to the subscriber" error has SQL code instead of file name

texts:
This type of error is unfortunately not uncommon. One report of many is this one on Stack Overflow: https://stackoverflow.com/questions/11360331/sql-server-merge-replication-error-the-schema-script-xxx-sch-could-not-be-pro

The problem is that in every report of this error I can find the error message displays a file name ending in ".sch" as part of it, here it displays part of a function that is being replicated.

The function in question is using SUSER_SNAME(), which is also used for filtering rows in the replicated tables.

Imagine that the function in question has the following code (this is an example, I'm not going to post the actual code publicly):

ALTER FUNCTION [dbo].[GetDataForCurrentUser]
(
)
RETURNS TABLE
AS
RETURN
(
    select data_id, data_type_id, data_key, data_value
    from UserDataTable
    where user_name = REPLACE(SUSER_SNAME(), 'OURDOMAIN\', '')
        and data_is_valid = 1
    order by data_key
)

Then the actual error that appears is the following:

The schema script ', '')
        and data_is_valid = 1
    order by data_key
)' could not be propagated to the subscriber

In other words, instead of a file name what appears is the last part of the function in question, starting after the string literal, in single quotes.

It seems like either the use of SUSER_SNAME() is causing the problem, or perhaps the backslash that appears as part of the function. However I am not aware of reasons not to use that function in a user-defined function, or a need to escape backslashes in string literals. Also, the function itself works with no problems when actually called.

Is there a problem with the function definition or is this a bug in replication itself?


texts: PostGres- How to loop in Table to update every record in Table

texts:
I am working on complicated problem, but I will simplify it to this problem.

I have two tables

A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

and i want to update the third:

C [ID, column1, column2]

I am writing a query to

Select A.column1, B.column1 from A,B where  (A.column1 -B.column1)=1 limit 1;

Now I want to get all the points that

insert C(column1, column2)
Select A.column1, secElement from (Select A.column1 firstElement, B.column1 secElement from A,B where  (A.column3 -B.column3)=1 limit 1) as ex, A where A.column1=ex.firstElement limit 5;

But I am getting just one row.

I aspire to get a result like that.

Loop on A
for every element in A, I should find its peer.

But I couldn't find a way to do the condition. Take the element, look for its peer then go to the other element.

  A.column1 | secElement

  ----------+-------------
  25033419 |      244959
  25033420 |      244960
  25033421 |      244961
  25033422 |      244962
  25033424 |      244963
  (5 rows)

I can express it algorithmically :

Foreach (element e in A)   //Global Query
  Find_Peer(e)             // Query 1

UPDATE:
I'm not in syntax. I am trying to get adapted to it. but it should be something like that.

CREATE OR REPLACE FUNCTION PointPeer(elem integer) RETURNS text AS $$
Select A.column1, B.column1 from A,B where  (A.column1 -B.column1)=1 where  A.ID=elem limit 1::text
$$ LANGUAGE sql;

Select A.ID, PointPeer(A.ID) from A limit 5;

I still struggling with the Syntax.


texts:
i have 8 slave servers and i need to restart one of them but in my.cnf file master-host, master-user etc. variables dont exists. So if i restart server as indicated here
replication will start again?


texts: Does SQL Server re-create system databases if they are lost/missing?

texts:
We have a third-party server backup tool running every 24 hours, which backs up everything (files and database in preparation for a bare-metal restore).  After much investigation, it emerged that the tool used non copy_only backups, thus destroying the log chain on both the system databases, and our own.

(I've been researching whether the system databases should be backed up, and the consensus is generally yes because of the data they hold concerning SQL Server in general (e.g. users, agent jobs, etc). This isn't so much about their contents, but what would happen if they were missing after a restore because of the situation I describe).

To stop this crime, I've disabled the tool performing database backups, so now it only does file backups (except the MDF/LDF files).  I then have scheduled SQL Agent to run backups every hour and I then sent them to my private cloud.

In the event of complete server failure, and a bare metal backup was required, I expect that the databases wouldn't be there when SQL started back up, as they weren't backed up/restored by the tool (although I have them safely backed up).

What actually happens at this point?  Does SQL Server re-create the system databases, which I then can restore over?  Or, does it just refuse to start up and I'm heading for trouble?


texts: How to group rows while filtering out values per each group?

texts:
Imagine you have the following setup:

CREATE TABLE namechanges (
  id text,
  new_name text,
  change_date timestamp
);

INSERT INTO namechanges VALUES
  ('x', 'alice', '2020-03-01'),
  ('y', 'Bob T.', '2020-03-03'),
  ('x', 'Alice', '2020-03-05'),
  ('z', 'charlie', '2020-03-07'),
  ('x', 'Alice C.', '2020-03-09'),
  ('z', 'Charlie Z.', '2020-03-11')

How would look like a query that would retrieve just the current name for each id and return the following?

| id  | max        |
| --- | ---------- |
| z   | Charlie Z. |
| y   | Bob T.     |
| x   | Alice C.   |

Here's the example above on DB Fiddle if you want to play with it: https://www.db-fiddle.com/f/ugNcXhRyb44KTpqjFXmKDW/0


texts: Is it possible to query "NOT IN" in NO SQL?

texts:
I asked the same thing about a specific NoSql engine (Big Table) on SO.
It seems that Big Table doesn't support NOT IN queries?

Is that possible in any other NoSQL databases?
Will it ever be possible on NoSQL databases?


texts: SQLAnywhere/Sybase - General error when trying to count number of occurrences, crashed server

texts:
I'm trying to do something that seems relatively simple.
The below query returns a list of "case numbers" and the name of an associated "referrer" among other junk.
I'm trying to count the number of times each individual referrer name appears in the results.
When run, the query below returns: "Error: General Error", and it actually crashes the entire server seconds later. posted entire query instead of just relevant lines since the entirety of this query is crashing the server.
In line 6, each individual referrer is listed under column 'referrer'.
In lines 7-12, I'm trying to count the number of times each individual referrer occurs
Any insight? First how can I simply count number of occurrences for each referrer... and second why the heck is my server crashing from this select query!?
select
casenum,
matcode as casetype,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
    (
        SELECT first
            count(sp_name(referred_link,1))
            from cases
            group by referrer
    ),
(case when mailing_list is null then 'No'else 'Yes' end) as old_mattar_star,
'repeat client' as reason,
names.email as 'client email'
from cases
inner join party on cases.casenum=party.case_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a
on party.party_id=a.names_id
inner join names on cases.referred_link=names.names_id
where party.role='Plaintiff'
and party.our_client='Y'
and date_opened>='2020-01-01'
and date_opened<='2021-01-01'
and cases.case_date_6 is null
and party.party_id IN
(select party_id from
(select count(casenum) as casecount, party_id from cases
inner join party on cases.casenum=party.case_id
where party.role='Plaintiff' and party.our_client='Y'
and matcode IN ('PMV','MVA','IMV','GPI','IGP','S%F','ISF')
group by party_id) multicases
where multicases.casecount>=2)


texts: Postgres: check disk space taken by materialized view?

texts:
I know how to check the size of indexes and tables in Postgres (I'm using version 9.4):

SELECT
   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
   FROM pg_class
   WHERE relpages >= 8
   ORDER BY relpages DESC;

But this doesn't show materialized views. How can I check how much disk space they are taking up?


texts: SQL Server: Clarity on which service user does what (backups, etc) RE accessing network shares

texts:
To backup / restore to or from a network share, either the SQLServer or SQLAgent user needs access to that share.

Which is it?  SQL itself or the agent?

And then how?

(I wanted to create the SQLServer user on a remote system, but did not know the pw for the SQLServer user on the db server itself... so I went to set the pw on the db server SQLServer user... and was not allowed to, even though I am a member of administrators...)

What am I missing here?

Scenario:

I have OLD-DB and NEW-DB servers.
I wanted to do a backup on OLD-DB   to \new-db\incoming-logs\
I set up the SQLAgent user on new-db, with
same pw as that user on old-db... but it failed, with error that
cannot access that share.

So now I have a backup in \old-db\backups\  and would like to restore directly from there (rather than copying).... AND I need to set up log shipping between these two systems.

NOTE: Active Directory / domain users are NOT available. This is plain jane, two stand alone servers.

Thank you for the missing piece. I know I am close!


texts:
I want to fetch nearest rates for a given value from below table.

mysql> select * from rates;
+------+----------+----------+
| ID   |   Type   |   Rate   |
+------+---------------------+
| 1    |    A     |    12    |
| 2    |    A     |    18    |
| 3    |    A     |    25    |
| 4    |    A     |    27    |
| 5    |    A     |    34    |
| 6    |    A     |    38    |
| 7    |    B     |    40    |
| 8    |    B     |    43    |
| 9    |    B     |    50    |
| 10   |    A     |    55    |
| 11   |    A     |    58    |
| 12   |    A     |    62    |
+------+---------------------+

For example I want to get rates nearest to 12 it should return 12, 18, 25 and like wise nearest to 25 are 18,25,27

I tried many ways but I'm unsuccessful.

SELECT ID,
       TYPE,
       Rate
FROM (
        (SELECT *
         FROM
           (SELECT *
            FROM rates
            ORDER BY Rate DESC) a
         WHERE a.Type ='A'
           AND a.Rate <=25 LIMIT 0 ,
                                 1)
      UNION
        (SELECT *
         FROM
           (SELECT *
            FROM rates
            ORDER BY Rate ASC) b
         WHERE b.Type ='A'
           AND b.Rate >25 LIMIT 0 ,
                                3)) r
GROUP BY r.Rate
ORDER BY r.Rate ASC LIMIT 3

It gives me 25, 27, 34 but in fact it should return me 18,25,27. Any suggestions?


texts: SQL Server Management Studio - Actual Execution Plan - Actual Row Size and Actual Data Size

texts:
I am looking at an actual execution plan in Microsoft SQL Server Management Studio v 17.9.1 and I can't see a lot of actual values to compare against the estimates.

For example, we have a connector which holds the following data:

I can see the Estimate Row and Data Size, but where is their actual size? Is there a way to see this information?

Same question for actual CPU, I/O, number of executions and so on.

Update: MS SQL Server 2008 R2


texts: MySQL syntax for order by not accepted in SQL Server

texts:
I'm working through the SQL snippets for nested sets in this article by Mike Hillyer.

The article is using MySQL, but I'm using SQL Server.  I get the following error reported when attempting to execute the query that should return the immediate subordinates of a node.

  The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

What do I need to change to get this query to work on MSSQL?

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;


texts:
I'm currently working on a quiz system and am trying to make the database design first. I've read some threads about different quizzes but they are all unique on some parts.

The system will use a MySQL database with PHP.

The goals of this quiz system:

Be able to have different "quizzes" inside it
Timetrack on the full quiz and for each question (thinking ahead for a challenge part between people)
Be able to handle a lot of participants in the quiz
Have a good base so it can be developed further with new, more complex functions without the need to rebuild the whole system (the database design that is).

Image of the database design so far. I've written the field on each "relationship".

I'm quite sure most of it is mostly self-explanatory, but I've added notes on the less obvious ones.

In the start at least it won't be any "live" contest. More likely that you do the quiz, get the time for it and then you can send a link to a friend and compete. But I'm still thinking a bit for that part.

I realise that with this design it's a bit more work to get the complete results etc than a basic quiz design where you put everything together in a table or two. But I try to divide it into different tables to be able to scale it better and make it more complex for later.

I'm thinking about adding some kind of support so a question can be used in different quizzes as well.  Just thinking about how I should do it best.

A key feature in this is also to be able to load everything fast.

Any tip is welcome!

wp_quiz (Here we store all the different quizzes)

quiz_id
quiz_name
quiz_text
quiz_site (internal thingy, nothing important for the quiz itself)
quiz_difficulty

wp_quiz_questions

quiz_question_id
quiz_id
quiz_question
quiz_question_difficulty
quiz_question_multianswer (In case it's a special question that has more than one correct answer, then we use for example selects intead of radiobuttons).

wp_quiz_question_answers

quiz_question_answer_id
quiz_question_id
quiz_question_answer
quiz_question_answer_correct

wp_quiz_participants

quiz_participant_id
quiz_id
quiz_participant_name
quiz_participant_email
quiz_participant_userid
quiz_participant_time_start
quiz_participant_time_end

wp_quiz_participant_answers

quiz_participant_answers_id
quiz_id
quiz_question_id
quiz_participants_id
quiz_question_answer_id
quiz_question_time_start
quiz_question_time_end


texts: Database design - do I need another table?

texts:
I am trying to make a database that follows a form that the company uses.
When a client walks in the members of staff have to fill in a form and the data is recorded. The form on paper is 10 pages long. The first time a client is seen the entire form is filled in and the client gets a clientID.

I have split the form into sections that make sense like accommodation and employment. I know I can link these tables together with the clientsID. That's the simple part.

Now when a client returns the form comes out again but this time only certain parts are filled in, what ever the clients needs are. The records for most parts don't need updating but a new record needs inserting. what would be the best way around this.

So at the moment I have for example a table called client with an id and name
another table called accommodation with clientid and address
and another table employment with clientid and employer.

But how do I go about it when a client comes in to let us know he has a new employer. I cant update the current one as that is needed but I would need to add new record for the client.

Would this mean I would have to add a look up table for all my current tables?


texts: Data Warehouse measures without a corresponding dimension

texts:
I have an operations database that contains this table:

salesQuality salesPrice salesCategory

According to my business requirements, the salesQality and salesPrice are measures.

However, salesCategory is a dimension.

Unfortunately, in the operations database, there are some rows without the salesCategory value.

What should I do in the fact table to represent these sales that don't have a category value in the category dimension?


texts: EDB Replication Server - Replicate from EDB Postgres Advanced Server 9.3 to EDB Postgres Advanced Server 9.6

texts:
I am new to EDB Replication Server and I was wondering if I can use this tool to upgrade from EDB Postgres Plus Advanced Server 9.3 to EDB Postgres Plus Advanced Server 9.6.
Is this a bad idea?  What issues might I encounter?


texts: Finding most recent entry per group running very slowly

texts:
I have a table (notifications) containing about 300k entries.  Each entry relates to a price change on one of about 500 different products (Trigger_ASIN).

I need to frequently pull the most recent notifications entry for each Trigger_ASIN.  (I'll be using the resulting query to look up related details in another table)

My query as it currently stands is:

SELECT n1.Trigger_ASIN, n1.UniqueId
from notifications n1
  left outer join notifications n2 on n1.Trigger_ASIN = n2.Trigger_ASIN
and n1.Trigger_TimeOfOfferChange < n2.Trigger_TimeOfOfferChange
WHERE n2.UniqueId is NULL
limit 20;

When limited to 20 records, it runs in 5secs (0 secs to fetch).
Limited to 40 records it takes 15 secs.
Limited to 80 records it take 125 secs.
By the time I get to my c.500 different products it's so slow it's unusable.

The EXPLAIN output on the query shows that an index is being used (one one side of the join at least).

1   SIMPLE  n1  index       notifications__index_most_recent2   221     302974  Using index
1   SIMPLE  n2  ref ix_notifications_Trigger_ASIN,notifications_Trigger_TimeOfOfferChange_index,notifications__index_most_recent_entry,notifications__index_most_recent2    notifications__index_most_recent_entry  63  amz.n1.Trigger_ASIN 240 Using where; Not exists; Using index

I've looked through a number of related questions, but still haven't managed to find the reason for the query running so slowly.

Can anyone see what I'm doing wrong to cause this to run so slowly?

For reference, the notifications table and associated indices is are described below.

Notifications Table:

    -- auto-generated definition
CREATE TABLE notifications
(
  UniqueId                  VARCHAR(50) NOT NULL
    PRIMARY KEY,
  PublishTime               DATETIME    NULL,
  SellerId                  VARCHAR(30) NULL,
  MarketplaceId             VARCHAR(20) NULL,
  Trigger_MarketplaceId     VARCHAR(20) NULL,
  Trigger_ASIN              VARCHAR(20) NULL,
  Trigger_ItemCondition     VARCHAR(50) NULL,
  Trigger_TimeOfOfferChange DATETIME    NULL
)
  ENGINE = InnoDB
  CHARSET = utf8;

CREATE INDEX ix_notifications_PublishTime
  ON notifications (PublishTime);

CREATE INDEX ix_notifications_SellerId
  ON notifications (SellerId);

CREATE INDEX notifications__index_most_recent_entry
  ON notifications (Trigger_ASIN, Trigger_TimeOfOfferChange, UniqueId, Trigger_ItemCondition);

CREATE INDEX ix_notifications_Trigger_ASIN
  ON notifications (Trigger_ASIN);

CREATE INDEX ix_notifications_Trigger_ItemCondition
  ON notifications (Trigger_ItemCondition);

CREATE INDEX notifications__index_most_recent2
  ON notifications (Trigger_TimeOfOfferChange, Trigger_ASIN, UniqueId);

CREATE INDEX notifications_Trigger_TimeOfOfferChange_index
  ON notifications (Trigger_TimeOfOfferChange);


texts: ERROR - Function 'innodb' already exists

texts:
I'm looking to start converting some MyISAM tables to INNODB. Running Ubuntu 14.04 Server with MySQL 5.6 ( apt-get upgraded from 5.5 ), 32G ram on 4 core/ 8 thread cpu. Initially had trouble with...

could not open single-table tablespace file .\mysql\innodb_index_stats.ibd after restart of MySQL

and

Error: Table "mysql"."innodb_table_stats" not found.

I followed Rolando's posted advice and the DB isn't crashing or creating a huge error.log anymore.
( the above issues are gone, just describing leading events )

Now I'm concerned about the following errors before I convert anything to INNODB. Federated can be ignored from what I read, the second line below ( also the subject of this post ) concerns me

2014-05-19 01:50:57 30950 [Note] Plugin 'FEDERATED' is disabled.
2014-05-19 01:50:57 30950 [ERROR] Function 'innodb' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2014-05-19 01:50:57 30950 [ERROR] Function 'federated' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2014-05-19 01:50:57 30950 [ERROR] Function 'blackhole' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2014-05-19 01:50:57 30950 [ERROR] Function 'archive' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.

added the following to my.cnf from Rolando's posted advice

innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M

Wasn't sure if there should be anything else INNODB related. Based on what I could find also added

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1

That didn't help. Not that I expected it to eliminate an "existing function"...lol


texts: mysql_upgrade on fresh MySQL/MariaDB installation

texts:
What I've noticed lately is that whenever I install MariaDB or MySQL on a clean machine, and if I run mysql_upgrade it does the upgrading, so I figured since it's not showing it's already upgraded, I should do it.
So, it's been my practice ever since. Does anyone know anything more about this? Why this isn't done during installation process? Does this mean newer mariadb/MySQL server comes with table from older version?


texts: SQL Server agent option in SQL Server Express

texts:
I am using SQL Server 2008 Express. I don't have SQL Server Agent option in it. How can I enable it?

I have searched on internet, but couldn't find anything. Do I need to install anything?


texts:
The default output format for a query is something like this:

ID | field1
-----------
1  | val1
2  | val2

Is it possible in SQL to change it to a CSV format like this?

ID , field1
1  , val1
2  , val2

Currently, I am converting fields to string values and then concatenating them using the || operator, e.g.

SELECT ID::text || ', ' || field1 ...
FROM ...

But this is really cumbersome to write, and not very readable either.

Is there a more succinct way of doing this without converting to strings?

This isn't a duplicate of Export remote Postgres table to CSV file on local machine because as shown in my example, I am looking to output in CSV, not store the output as a CSV file.


texts: What model design supports both data store backed and free form entries?

texts:
I have an application where users are tracking a workflow around producing a derivative work from an existing Book. The derivative work, we'll call "BookDerivative" must have a book that it relates to from a data model perspective. The existing system which stores the Book info (Title, Author, etc) must be considered an "optional third party" system that can be interchanged with another system in the future (which I've abstracted via IBookService).

My application must provide two options for users when they create a BookDerivative:

Select an existing book from the IBookService provider (via lookup); OR
Create a "free form" entry for the Book which would NOT be linked to the IBookService provider. This allows the user to create a BookDerivative for a Book title not found by the IBookService lookup.

My solution to support this is to include the needed columns from Book on my BookDerivative table/entity (BookTitle, BookAuthor, BookId) where BookId will only be present IF the book comes from the third party system. This way if the book is "free form" I would only populate BookTitle and BookAuthor.

Future accesses of BookDerivative do not need to look to the IBookService. The essential book info should never (or rarely) change in the third party system and I'm prepared to except the fact that it is theoretically possible for information to be out of sync.

There will be no data-integrity between the two systems and no FK. My application cannot assume the underlying data model of the Book system but instead must rely on a defined model and IBookService lookup.

My question remains, is this a good way to solve this problem? What recommendations or pitfalls might exist? I would think this is a common requirement, have you used a different or better solution in the past?


texts: may i reduce the restore time with pgsql 9.2?

texts:
I need your help about how to reduce time to restore process for a big dump (400GB)

We have a database that takes weeks to restore due to the single threaded nature of index generation on PostgreSQL. Please i need your help to figure out a way that we can restore this quicker, maybe by disabling index generation at load time and adding the indexes back later or some better trick.

sample pg_dump command:

pg_dump --compress=0 -bo -F c --lock-wait-timeout=1500 -h $HOST -p $PORT $DBNAME | lbzip2 > $DB-$TIMESTAMP.bz2

sample pg_restore command:

pg_restore -Ov -j 2 -h $HOST -p $PORT --dbname=$DBNAME $RESTOREFILE

-j option does not help us since it helps the part that doesn't take long (backup restore) but not the part that does take a long time (index generation) - this ticket is to figure out a way to streamline the process so index generation is done separately on an already working database or to speed up index generation, on PG 9.2

I'd like a clear procedure for removing index generation from the restoration process and doing it afterwards, to not block usage of the DB.

Is possible do that? what is your opinion about that?


texts: Why is there no max(uuid)/min(uuid) function?

texts:
Why can I use a UUID to sort rows:
SELECT uuid_nil()
ORDER BY 1;

But I cannot compute the maximum value:
SELECT max(uuid_nil());

[42883] ERROR: function max(uuid) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I know that I can cast to a character type or ORDER BY and LIMIT 1. I'm just curious as to why I have to use a workaround.


texts: Auto scaling MongoDB as a service in Microsoft Azure

texts:
I see a MongoDB service in Microsoft Azure but that deploys a VM and installs MongoDB in that. I am guessing this will not auto scale and also charge me for the entire VM and MongoDB management (not just for my usage).

Is there any managed MongoDB as a service available in Microsoft Azure which will:

a) Auto scale.
b) Charge me only for my usage.

I see MongoDB container image by Bitnami but I might have to deploy this on Helm.

What are my options here?


texts:
why do i get  ORA-01031: insufficient privileges ?

SQL> grant create view to rojib;

Error :

grant create view to rojib
*
ERROR at line 1:
ORA-01031: insufficient privileges


texts: Which application should I use to create a relational database from an excel file?

texts:
I have an excel inventory of network assets (inaccurately sold as a 'CMDB') that is becoming difficult to manage. I need to import the data into some kind of database but I dont know which one to use. It has to be easy to update - i.e add/modify/delete records and also be easy to show which records have been updated and when

It would be really good if I could show relationships between items for example, one line showing a the record for a router and another line showing which LAN switches are connected to it.

thanks


texts:
I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the most recent and Date1 must exist:

For each distinct ID in InputTable (= rst(0) ) do:

INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1)
SELECT InputTable.ID,
       InputTable.FID,
       InputTable.KeyValue1,
       InputTable.KeyValue2,
       InputTable.Date1
FROM
  ( SELECT TOP 1 *
    FROM
      ( SELECT *
        FROM InputTable
        WHERE ID='" & rst(0) & "' And Date1<>''
      )
    WHERE Date2 =
          ( SELECT Max(Date2)
            FROM InputTable
            WHERE ID = '" & rst(0) & "' and Date1 <> ''
          )
  );

The InputTable has about 400.000 records and ~200.000 distinct IDs and only contains the 5 fields that will be in the Final Table, all of which are TEXT.

InputTable is an importet .txt file and i am working with ACCESS outside of any real database. The Result will be again written into a .txt file.

It takes about 1 second for each ID, which is too long... Is it possible to improve the performance significantly?

I really appreciate any help.


texts: MariaDB innodb bufferpool load taking long time

texts:
When I am restarting my MariaDB database engine it takes a long time to show 'innodb bufferpool load completed' depending on data volume. Is there a way to make it faster?
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 100000000      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 29             |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 100000000006   |
+-------------------------------------+----------------+


texts: Web application for Software as a Service - Shared database or Multiple Databases?

texts:
(I am not sure if this is the best place to post this question. If not, please redirect me to the appropriate stack exchange site.)

I am looking to deploy a web application for multiple clients. The idea is that each client can only access his or hers own data. Let's say that the client is the owner of the application installation. Each installation can have multiple users. All installations will be in my own hosting, so that I can better control the code and business model (its a SaaS kind of thing).

I am looking for technical options to perform this kind of deployment.

[Option 1] - Shared single database

On easy option to do this would be:

change the database in order for each relevant table to know its
"owner id".
change all SQL queries so that each User will only fetch
data that is related to its "owner id" (I don't have the requirement
for an User to be common to more than one client/installation).

However, this solution has a few drawbacks:

requires changing most of the SQL code;
there might be some performance impact since the database will have much more data (the data for all installations).
mixing data of different clients in the same database may cause some issues (if there is a bug, its easier for someone to end up accessing some else's data);

So I would prefer an alternative where the data would be isolated.

[Option 2] - Multiple databases

One alternative that I was considering was:

For each installation, buy a DB from the hosting company
Come up with a configuration scheme that allows associating each installation with the database where the connection should be performed.
For example, if the user accesses http://very-nice-app.com/client-3/ the code knows that it must connect to database-name-for-client-3.

This solves the potential issue of data leaking, but requires me to maintain several databases (if DB tables change, it has to be done across multiple installations). This can probably be automated somehow.

[Questions]

What would be the better approach? Is there a better alternative than the ones I have indicated?

Thanks in advance for your help.

[Additional info]

The database engine will probably be MySQL.
This is a startup, so we are trying the business model. We don't have a good idea of how many clients we will have - it will be zero for some time. Right now we are looking for the ability to deploy and scale for a few clients (lets say up to 10).
This app is targeted at small business and the amount of data should not be in millions of records. The largest table should have about 10.000 records per year (per client). This value is based on the company with whom we did the prototype (which is a mid sized company for the area x business).


texts: How can I filter databases by owner using psql?

texts:
I know I can list my postgres databases with the following command:

# list only the databases
psql -U openerp -q -t -c "select datname from pg_database;" template1

Or

# show a full listing of databases
psql template1 -U openerp -l

How can I filter the databases by owner 'openerp'? datdba field is just a number... how can I map it?


texts: Being careful about additional records being added in a duplicate record merge?

texts:
We have a multi-user MS-Access 2003 database.  Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit.  I've read that transactions on multi-user JET databases are supposed to be set to optimistic locking, which concerns me a little, and I don't know how to lock specific records using JET's SQL syntax.

I have however verified that when I do an update to a record within a transaction on one connection in SQuirrel, if you try to access that record in the other connection the changes are not seen by the other connection until after commit.  Also updates in the other connection to those records will fail.

On this database I've been tasked with merging a new duplicate record into the old record.  The new record has alot of related child tables, the foreign keys of which, I need to switch to the original record to merge the two.

As far as I can tell they are all 1-M relationships.

So far what I have done is, begin a transaction and then follow this procedure for each of the related child tables:

I get a count(*) of the number of child records from the new duplicate record and the number of child records from the original record, resulting in a table like this:

| ID                        |the_count       |
| primary key old record    |numChildRecsOld |
| primary key of new record |numChildRecsNew |

And then what I do is sum and store the two counts:
numChildRecsOld + numChildRecsNew = numChildRecsOldThatShouldExistJustBeforeCommit

Next I would UPDATE the foreign key in the child records that point to the new record id, so that they point to the old original record ID.

And I would be sure to store the value of numChildRecsOldThatShouldExistJustBeforeCommit for comparison later in the transaction when I re-run the count query to ensure that no new child records are associated with the old or new parent record have been added while our thread was busy updating the other tables.

So after all of the updates occur within our transaction, we have to go back and check that all the sums are as expected before commiting:

| ID                        |the_count                                                          |
| primary key old record    |numChildRecsOld should = numChildRecsOldThatShouldExistJustBeforeCommit |
|                            - AND -
| primary key of new record |numChildRecsNew should = 0                                         |

Or...we have to rollback the entire transaction and start over completely...if the number of records for the old query (numChildRecsOld) does not match numChildRecsOldThatShouldExistJustBeforeCommit then we've lost or gained a record somewhere.

And if the numChildResNew is greater than 0, then someone has inserted another child record related the duplicate and we've failed to merge it with the original record.

In either case the transaction needs to be rolled back, and we need to restart the entire query.

Is this logical thinking?  I'm I overly concerned about this?   Will the transaction keep other people from adding records?   Or should I just send out an email to everyone in the department to get off the database until I run my transaction?  (poor man's exclusive lock as it were)


texts: Stored Procedure to update an adjacency Model to Nested Sets Model


texts: Is it good practice to use non standard schema names in SQL Server?

texts:
If I have a database MyDB, is it considered good practice / bad practice to use schemas other than dbo?

For example:

MyDB.reports.YearlySales
MyDB.reports.TopSellers
MyDB.[data].ProviderX
MyDB.[data].ProviderY
etc


texts:
I have a column in table with type character varying  here is the
query:

update logins
set access_token='l38r8cfjm31b38ye91yjn1956vr3n74tc5sc0btholj0iain77' and uuid='123456789'
where username = 'PWD_2006'

It give me the error:

  ERROR:  invalid input syntax for type boolean:  "l38r8cfjm31b38ye91yjn1956vr3n74tc5sc0btholj0iain77"
  LINE 1: update logins set access_token='l38r8cfjm31b38ye91yjn1956vr3...
                                             ^
  SQL state: 22P02
  Character: 32


texts: Indexes involved in intervals collision detection

texts:
Let's assume an hotel booking application wants to display all customers being present between two dates.

Starting with a venue table :

CREATE TABLE `venue` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `startdate` DATE NOT NULL,
  `enddate`   DATE NOT NULL
)

I wish to perform a query finding venues for a one month interval, e.g. last december 2013:

SELECT `venue`.id
WHERE `startdate` <= '2013-12-31'
AND   `enddate`   >= '2013-12-01'

In order to have great performance among millions of rows (big hotel), I immediately add two indexes, one for each date:

ALTER TABLE `venue`
ADD INDEX (`startdate`),
ADD INDEX (`enddate`  )

I leave to the SQL engine the responsibility to choose the right index. If dates intervals are distributed homogeneously, from say 2000-01-01 to 2014-01-01, the previous query will immediately use the enddate index, only scanning the last month. If the interval is closer to the beginning, e.g. last week of 2000, the startdate index will be used, scanning only the whole 2000 year.

Now, what if I want to get the venues for a single day, right in the middle of the venues distribution, e.g. first january of 2007. Both indexes will more or less offer the same filtering, that is half the table. And scanning half the table is very, very long.

Adding paired index like (startdate, enddate) or (enddate, startdate) won't be of any used, as far as I can tell.

Is there any other index-based strategy to make things faster ?


texts: SQL Server - Physical Table Design - Column Order - Null and Non Null Columns

texts:
We are in the process of building out a database for a new application.  I got some feedback from the architect that he read somewhere there is a memory utilization benefit for having all of the non-nullable columns in a table first, then all the nullable columns. If there are nullable columns in-between non-nullable columns that some kind of memory or storage benefit is forfeited because those nullable columns are between non-nullable columns.

The columns were ordered in a slightly ambiguous order but in general it was prioritized for columns which were more likely to be used first.

I was under the impression that SSMS orders the columns on the page file by its own logic instead of the specific order they are created on the table.  That the order I put the columns in on the table was totally independent from how the data was stored in the page file.

Is there any documentation somewhere that would support this finding/understanding?

EDIT:

Having some more conversations around figuring out where this idea came from.  The request was an over simplification of having high density of data, or columns used most frequently in SELECT or JOIN operations in the front of the table to prevent them from moving to the Overflow section of the page file.

That the order of the columns assigned to the table is the same order the data is added onto the page file.  For a given row, the data is stored on the page file in the order the columns are set on the table.  If the row holds more data than the page file allows (assuming we are not using anything that would go into a LOB IMAGE or VARCHAR(MAX) or something like that) then the remainder is put onto the overflow file.  If that overflow data is needed than there is extra effort and time spent doing the lookup to that overflow file.  That can result in noticeable impacts to performance in high row environments (10's or 100's of millions of rows +).  So we would want to prioritize the column order in how often we think that column will be accessed, not necessarily if it is NULL or not.

Does that understanding/analysis sound right?


texts: How to get snapshot of changed data and store them on daily basis

texts: I have a requirement from my client. He wants to store a snapshot of what data has changed for some given tables, mainly financial transaction tables, on daily basis. This is for audit purposes. Data is to be stored table wise, so that we can develop some reports based on it to keep track of changes on daily basis. What are the possible techniques for this in SQL Server 2008 R2 Enterprise Edition? I am also interested in the latest available version as we are planning to migrate to latest SQL Server version.


texts: Accessing old version of referenced table in trigger function - Postgresql

texts:
In my database I have a table named "layer" and a table named "grouplayer". The table "layer" is referencing the table "grouplayer" by a foreign key constraint with Cascade DELETE.
I have added a trigger function to the table "layer" which is getting the value of another column of the table "grouplayer" by a SELECT query.  The function is working as intended when I delete a row of the table "layer". However if I delete a row of the table "grouplayer", the function doesn't work as the result of the SELECT query is NULL.

CREATE OR REPLACE FUNCTION some_func() RETURNS TRIGGER AS $$
DECLARE
    pro TEXT;
BEGIN
    SELECT project into pro from grouplayer WHERE id = old.grouplayer_id;
    -- Query = NULL. How can I access project from grouplayer before deleting?

    RETURN NEW;
END
$$  LANGUAGE plpgsql;

I can't access the grouplayer even though I have included the keyword Before in the Create Trigger Statement:

CREATE TRIGGER some_trig BEFORE DELETE ON layer FOR EACH ROW EXECUTE PROCEDURE some_func ()


texts: Copy database fails while trying to copy login

texts:
I'm trying to copy a database from a server that lives in a unique domain, call it AMCO.

The destination server, which is on domain BCO, is running sql server agent, and as far as I can tell everything is set up properly there.

When I use the copy database feature of management studio, I can set everything up just fine. During the actual job execution however, this appears in the log:

  Started:  5:27:19 PM  Progress: 2012-07-31 17:27:20.04     Source:
  192_168_50_11_SQLSERVER2008_localhost_SQLSERVER2008_Transfer Objects Task      Task just started the execution.: 0% complete  End Progress  Error: 2012-07-31 17:27:27.34     Code: 0x00000000     Source: 192_168_50_11_SQLSERVER2008_localhost_SQLSERVER2008_Transfer Objects Task      Description: ERROR : errorCode=-1073548784 description=Executing the query "CREATE LOGIN [AMCO\AMCOAdmin] FROM WINDOWS WITH DEFA..." failed with the following error: "Windows NT user or group 'AMCO\AMCOAdmin' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.   helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}

Why does this user need to be copied first? I am removing the option of "copy logins" from the additional data to copy, and yet this error still comes up.


texts: I've been doing some index usage reviews (using DMV seek, scan and lookup stats) and have identified a number of 'unused' indexes in our biggest and busiest DB.  I am interested in the opinions of this forum on what checks or confirmations other than reads should be evaluated before performing the index DROPs.  One that comes immediately to mind is whether the index is being used to enforce a necessary unique constraint.  What other factors are important to consider?


texts: Is it possible, to schedule a simple Query to run Daily and export the received data into an excel file?


texts: Deleting near duplicates based on one varchar field

texts:
I'm getting around to cleaning a database but I need some help with deleting near duplicates in MySQL database but is only off by one character in a column.  I've searched and found ways to remove true duplicates but each off the solutions I've seen for near duplicates (timestamp, deleting without worry about which record stays) don't seem to apply.

Certain data must remain based on one column.

id  |   plyrID |  year |  pos | teamID
1   | 111548   | 2005  | OF   | 367
2   | 111548   | 2005  | RF   | 367
3   | 111548   | 2006  | RF   | 332
4   | 592743   | 2012  | SS   | 99
5   | 592710   | 2012  | SS   | 111

If there is a near duplicate based on plyrID, year, and teamID and if pos = "OF", I want to keep the record where the pos is either "RF", "LF", or "CF".  The result should look like:

id  |   plyrID |  year |  pos | teamID
2   | 111548   | 2005  | RF   | 367
3   | 111548   | 2006  | RF   | 332
4   | 592743   | 2012  | SS   | 99
5   | 592710   | 2012  | SS   | 111

where the record with pos = "OF" is deleted and the other remains.

Any help would be much appreciated.


texts:
Recently I wanted to drop a FK on table X referencing the userid of the "user" table. The user table is of course very frequently read from, but still I assumed it would be safe to drop the FK without a database downtime. My reasoning was that the user table should not have to be locked while removing the FK from table X.

When executing

ALTER TABLE x drop constraint fk12345;

though, the query took very long, the database load increased significantly and the query had to be aborted.

So my question is: Does removing a FK lock the referenced table? If not, what else might be the explanation for the long duration?

Extra info: The query was run against two postgres9.3 instances running behind pgpool.


texts: How to use password with special chars in postgressql .pgpass file

texts:
How to use password with special chars like repl#stg$12 in postgressql .pgpass file?
db1:5433:replication:repl:repl#stg$12
I added entry like this in .pgpass but it's prompting for password when I connect.


texts:
I am designing a database for an application, in that I need to consider both BUYER (BUYER_ID) and VENDOR (VENDOR_ID) as a USER (USER_ID). I have designed this logic in a following design.

USER

User_ID (PK),
BUYER_ID(FK),
VENDOR_ID(FK).

I am confused, whether is this good method, while considering the following things,

Buyer may be a vendor.
Vendor can also be a Buyer.

I have created the USERROLE Table to achieve this, but when a user is only a buyer or vendor will the above mentioned relationship table work? Will this accept Foreign key as NULL?
I need the explanation for this - "A order could be placed by a Buyer or Vendor, but a single order cannot be placed by both buyer and Vendor at the same time".
Thanks in advance.


texts: AND conditions within unbracketed ORs work but why?

texts:
I have very similar code to the first block below. It is in production in a critical process, and I am not confident that it will always work. I would like to know why it does. It uses or (1=1) to group sets of and conditions. I am concerned that the returned results will change if the SQL version is changed.

Here is the simple table I am using for testing:

Id  Description
1   One
2   Two
3   Three
4   Four
5   Five
6   Six
7   Seven
8   Eight
9   Nine
10  Ten
11  Eleven

This code works, but I don't know why:

SELECT [Id] ,[Description]
FROM [Seminars].[dbo].[CodeTable]
    where (1=1)
    and Id = 1
    and Description = 'One'
    or (1=1)
    and Id = 7
    and Description = 'Seven'
    or (1=1)
    and Id = 3
    and Description = 'Three'

It returns:

Id  Description
1   One
3   Three
7   Seven

Here is the traditional code that uses parentheses (that I would expect to work):

SELECT TOP 1000 [Id], [Description]
FROM [Seminars].[dbo].[CodeTable]
where
    (Id = 1
    and Description = 'One')
or
    (Id = 7
    and Description = 'Seven')
or
    (Id = 3
    and Description = 'Three')

Returns the same result.


texts: Will an SSRS report on Azure SQL Database work?

texts:
Can we build an SSRS report using Visual Studio 2019 with Azure SQL server as PaaS mode?
We migrated to Azure Database from VM SQL Managed instance.


texts: How to create a view that refresh automatically

texts:
I was triying to create a View by the simplest way like this:

Use SoccerDB;
GO
CREATE VIEW ExampleDBaseII
AS
SELECT ID, Cast(Name AS Varchar) as Name,Cast(City AS Varchar) as City,
FROM Team
GO

How can I do, so that view keeps its link to the table so if a change the table the view also changes, without creating it again or creating a new one. Is that possible? Im working with Sql Server 2008 R2 thanxs


texts: SQL Server WMI Event Alert and BROKER_TASK_STOP waits

texts:
In SQL Server 2008 I created a Sql Server Agent Alert last week to monitor deadlock events. The Alert calls a job with no schedule sending the xml deadlock graph token obtained through WMI SELECT * FROM DEADLOCK_GRAPH. The job calls a SP that stores the graph and an email is sent.

I have been monitoring the Sql Server Waits and have found that after I implemented the alert described above, the wait categories for BROKER_TASK_STOP and SQLTRACE_WAIT_ENTRIES have sky rocked. Those values went from not even being on the radar to contributing 42.59% and 5.11% of total waits respectively.

Should I be concerned? I read somewhere that high BROKER_TASK_STOP's may not be of real concern.


texts:
I've spent a good amount at time at looking at related posts regarding products for rent but still have a few unanswered questions.

If we consider the just the requirements at hand which is how to model ski rental product packages and ski rental products, the scenario is this.

If we consider a ski rental package that consists of skis, poles, ski boots and insurance, number of days rental, how should I model this taking into account the addition of ski boots and insurance is optional and priced differently.

For example, a client may wish to rent skis for 6 days but a different client may want to rent the same skis with ski boots and insurance for 7 days.  The pricing for each choice is dependent on the items within package as well as the duration.  The pricing is typically on a sliding scale where the longer you rent, the better saving is made (when braking it down into a daily cost).

I've got the following model as a start :

customer
--------
customer_id
customer_name

orders
------
order_id
customer_id
order_date
order_type (purchase/rental)

order_items
-----------
order_item_id
order_id (FK in orders table)
product_id (FK in product table)
price
discount

product
-------
product_id
product_name
sale_price (if product available for sale)

rent_detail
-----------
order_id (FK in orders table)
date_out
date_return

product_rental_price_category
-----------------------------
product_rpc_id
name (e.g. : standard, returning customer, student)

product_rental_price
--------------------
product_id (FK in product table)
product_rpc_id (FK in product_rental_price_category)
number_of_days
price

Now, the real problem I have is with the inclusion (or exclusion) of the optional parts within a package, such as boots, helmet or insurance.

Any thoughts would be greatly appreciated.


texts: Unable to Execute Script files (.sql) in MySQL using DBeaver plugin, in Eclipse CDT with macOS Catalina (Shows, "No Active Connection")

texts:
Unable to execute the script files (.sql).
It shows "No Active Connections".
But, test connection is complete, and localhosr has MySQL Icon with connection check mark, and I refreshed the projects.
I am using:
GUI: DBeaver: 21.0.1
Database: MySQL Community Server: 8.0.23.
IDE: Eclipse CDT 2020-12.
OS: macOS Catalina


texts: amazon small instance mysql query too slow

texts:
I have mysql database with 1.5 millions records with 2 million categories records. when i do query on categories and city where clause it takes 304989 milliseconds. on localhost it too fast.

i tried to modify my.cnf settings. when i try to restart mysql service does not restart. it might the configuration that i'm missing. all table of database use Innodb engine.

please help me to fix mysql configuration

here table structure businesses and businesses_categories have 1.5+ millions records and query that too slow.

CREATE TABLE IF NOT EXISTS `businesses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`slug` varchar(250) NOT NULL,
`business_name` varchar(300) NOT NULL,
`category` text NOT NULL,
`categories` varchar(5000) NOT NULL,
`reviewList` text NOT NULL,
`images` text NOT NULL,
`opening_hours` text NOT NULL,
`logo` varchar(300) NOT NULL,
`street_address` varchar(300) NOT NULL,
`address_locality` varchar(300) NOT NULL,
`address_region` varchar(300) NOT NULL,
`postal_code` varchar(300) NOT NULL,
`latitude` varchar(300) NOT NULL,
`longitude` varchar(300) NOT NULL,
`description` varchar(300) NOT NULL,
`phone` varchar(300) NOT NULL,
`website` varchar(300) NOT NULL,
`canonicalUrl` varchar(300) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `businesses_categories` (
`business_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent` int(11) NOT NULL,
`category_slug` varchar(250) NOT NULL,
`category_name` varchar(250) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


texts: How to find XXth day of previous month in SQL server?

texts:
I have 2 parameters,

an input date (DATETIME)
input day of month (TINYINT)

If I enter 11 MAR 2014 as the DATETIME and 26 as the input day of month, I would like to select 26 FEB 2014 as the output DATETIME.

In other words, I would like to select the Xth day of the previous calendar month.
I am then going to use DATEDIFF to find the current fiscal day of month.


texts: Is it possible to use the same directory for 2 MySQL servers?

texts:
I have 2 OS-es on my machine: Windows XP and Linux Slackware. Both of them have mysql installed:

Windows - 5.5.25
Linux - 5.5.27

Is it possible to use the data directory from Windows on Linux? So the databases will be shared between MySQL instances.


texts:
I need some advice about our new database deployment plan.

We have 6GB of total mysql data(all innoDB) and innoDB_buffer_pool is set to be 16GB
Ans we have 2 database servers.(plus 1 server for the nginx and php). Mysql Version is 5.6.20

Server #1 - 1 Qual Core CPU , 48GB ram
Server #2 - 2 Quad core CPUs, 96GB ram

We currently have 1 master for server #1, and 1 slave for server #2 and using mysqlnd_ms plugin to do real/write splitting for better performance.

But since we do have plenty of ram spaces we are seeking some ideas to boost our database performance up.

Our question is if we have plenty of ram space like this, would it be beneficial if we have multiple mysql replications each server and use mysqlnd_ms plugin to distribute traffics?

For instance,

for Server #1 - 1 replication slave and 1 master.
for Server #2 - 4 replication slaves
Plus load balance using mysqlnd_ms plugin.

We just assume it could bring some performace advantage, but this is unser production so we can't tweak these servers freely.

If this idea is meaningless, could you provide some ideas?


texts: Get all rows from table where JSON column contains a certain value

texts:
I'm struggling to get data from a JSON column in my PostgreSQL database.
In our users table, we have a known_ips column, which is a JSON column, holding a flat array of IP addresses known for the given user, like this:

# select email, known_ips from users limit 3;
       email       |          known_ips
-------------------+-------------------------------
 user1@example.com | ["192.168.1.1","192.168.1.2"]
 user2@example.com | ["192.168.1.3"]
 user3@example.com | ["192.168.1.2"]
(3 rows)

What I'm trying to do is to select all the users that have an IP address from an array of IP addresses (in this example, either 192.168.1.1 or 192.168.1.2) in their known_ips column. So in this case, user1 and user3 have IP address .1 or .2, so I want to have them returned, but not user2, since I'm not looking for the .3 address.

I've tried the following queries, all to no avail:

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in("192.168.1.1","192.168.1.2");
ERROR:  column "192.168.1.1" does not exist
LINE 1: ... json_array_elements(known_ips) as ip where ip in("192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in('192.168.1.1','192.168.1.2') limit 5;
ERROR:  invalid input syntax for type json
LINE 1: ... json_array_elements(known_ips) as ip where ip in('192.168.1...
                                                             ^
DETAIL:  Token "." is invalid.
CONTEXT:  JSON data, line 1: 192.168....

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in(192.168.1.1,192.168.1.2) limit 5;
ERROR:  syntax error at or near ".168"
LINE 1: ...array_elements(known_ips) as ip where ip in(192.168.1.1,192...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in("192.168.1.1","192.168.1.2");
ERROR:  column "192.168.1.1" does not exist
LINE 1: ... json_array_elements(known_ips) as ip where ip in("192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in(array["192.168.1.1","192.168.1.2"]);
ERROR:  column "192.168.1.1" does not exist
LINE 1: ...array_elements(known_ips) as ip where ip in(array["192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip = any(array["192.168.1.1","192.168.1.2"]);
ERROR:  column "192.168.1.1" does not exist
LINE 1: ...ay_elements(known_ips) as ip where ip = any(array["192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip in(unnest(array["192.168.1.1","192.168.1.2"]));
ERROR:  column "192.168.1.1" does not exist
LINE 1: ...lements(known_ips) as ip where ip in(unnest(array["192.168.1...
                                                             ^

database=# select email, ip from users, json_array_elements(known_ips) as ip where ip = any(array["192.168.1.1","192.168.1.2"]);
ERROR:  column "192.168.1.1" does not exist
LINE 1: ...ay_elements(known_ips) as ip where ip = any(array["192.168.1...
                                                             ^

I'm feeling like I'm missing something obvious, but I just don't see it. Why does Postgres think that I'm defining columns instead of strings? How do I properly get the data?


texts: How to install two mysql instance on same machine

texts:
I want to run two MySQL instances on the same machine. The reason behind this is, I will be needing two databases with entirely different configurations.

The specifics of the configuration of the both the DB are as follows,

Fault tolerant, high-intensity IOPS, RAID 10.
Highly available, Low-intensity IOPS, RAID 1.
This two instance will have separate HA policies.

Currently, I am only installing once and running the first instance using systemdctl whereas I am running the second instance manually using separate cnf files.

So now, how can I install and run two separate instances of MySQL on same machine?

DO I need to create separate instances of the MySQL-server binaries for both instances? And do I need to install MySQL-server RPM for a second time into an alternate folder?

If yes, then how? I also want to understand the implication of using the single binary for running two instances. Does it affect the other instance in any way during recovery process or any other db intensive task?

My mysql version would be 5.6.34

I have gone through this link(https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html#systemd-multiple-mysql-instances), for installation of multiple instances on single machine but this support is enabled from 5.7.7 version whereas I am using 5.6.34.


texts: How to model entities in an entity-relationship diagram about a car service scenario?

texts:
I'm a beginner Database designer and wondering if anyone can help me understand this.
Let's say we want to design a system that has, among other entities, an entity called Vehicle, and another one called Service.
A service is a repair/maintenance.

How would you look at the cardinalites of that relation of:

A car can be serviced multiple times.
A service is for one car.

Should the minimum cardinality be 0 or one from the car side?

Also, a service involves parts, labor, and consumable.

How would you model this?
As a separate entity?
Or in the service entity or part of the relation (intersection entity) between car and service ?


texts:
I need to have the results of a query show featured items first for a given date before listing the day's other items. You can ignore the categories that are being pulled from another table.

Currently the results list all of the events by date. There is a column in the eventlisting table that is called featured and has values 1 (featured) or 0 (non-featured). I want the exact same results except that all featured items for that day will list before the non-featured items, for each day. Thank you for any suggestions.

SELECT a.*, c.id as catid, c.cat_name FROM eventlisting a
LEFT JOIN categories c ON a.cat_num=c.id
WHERE a.startDate >= "2015-08-21"
AND a.endDate <= "2016-08-21"
AND a.status=1
GROUP BY a.id
ORDER BY a.endDate
ASC LIMIT 0, 50

If this is my table data:

id featured status  startDate    endDate
_________________________________________
1      0      1    2015-08-21  2015-08-21
2      0      1    2015-08-21  2015-08-22
3      1      1    2015-08-21  2015-08-25
4      0      0    2015-08-21  2015-08-23
5      1      1    2015-08-22  2015-08-27
6      0      1    2015-08-22  2015-08-31
7      1      1    2015-08-22  2015-08-31

Then I need the results to show this order:

id featured status  startDate    endDate
_________________________________________
3      1      1    2015-08-21  2015-08-25
1      0      1    2015-08-21  2015-08-21
2      0      1    2015-08-21  2015-08-22
4      0      0    2015-08-21  2015-08-23
5      1      1    2015-08-22  2015-08-27
7      1      1    2015-08-22  2015-08-31
6      0      1    2015-08-22  2015-08-31


texts:
I'm trying to learn how to normalize to 3NF but the example that my teacher gave us doesn't really help.

The algorithm I always used is this one:

Find a canonical cover F_c for F
For each FD X->Y in F_c create a relation with schema XY
Eliminate a relation if its schema is a subset of another
Merge those remaining relations into a relation which has all the keys for the db

I also tried the algorithm found in this other question but I'm still failing for this particular example.

So, here's the example (starting from the canonical cover):

R<A,B,C,D,E,F,H>
C->DF; A->H; HF->C; B->E; AD->B; E->D; AB->F
Keys = {AC, AF, AD, AB, AE}

Since we already have the canonical cover, let's follow the second step:

<R1(C,D,F), {C->DF}>
<R2(A,H), {A->H}>
<R3(C,F,H), {HF->C; C->F}>
<R4(B,E), {B->E}>
<R5(A,B,D), {AD->B; B->D; A->D}>
<R6(D,E), {E->D}>
<R7(A,B,F), {AB->F; F->B}>

Before going on, here's the first question: he takes some of the projections from the schema, but there's one that really doesn't make any sense to me. I'm talking about A->D from R5.

The paths leading to D are the ones with E or C, so let's see if we can use them:

C is reached by HF, where H is reached by A and F by AB, meaning that A can't get to C without B
E is reached by B, where B is reached by AD, meaning again that A can't get to E without D

So the first question is: why does he take that A->D projection if isn't there a way for A alone to reach D? Am I missing something?

Please note that the same question applies to F->B in R7, because I don't think that F can reach B without A, that's why I think it should be AF->B

Coming back to the example, let's go on with the algorithm. As we can see there are no relations sharing attributes, so there's nothing to merge/eliminate at step 3.

It's now time for step 4, but his result is the following (he didn't write the FDs here):

R1'(A,B,D,E) - merged R4, R5, R6
R2'(A,B,C,F,H) - merged R2, R3, R7
R3'(C,D,F) - R1 was not merged and didn't change

The problem here is that we're presented with this final result without any explanation on how we got here. He just merged those relations and I really don't understand why.

Can you please try to explain how this works?


texts: regexp_replace() vs. replace() to replace all matches in a string

texts:
By default, in PostgreSQL, regexp_replace() replaces the first occurrence of a substring in a string, while replace() replaces all occurrences of a substring in a string. Why is the default behavior different?

(I know that with regexp_replace one can use the g option to replace all occurrences of a substring in a string.)

Examples:

SELECT regexp_replace('hello world', 'o', 'z');  -- returns "hellz world"
SELECT regexp_replace('hello world', 'o', 'z', 'g'); -- returns "hellz wzrld"
SELECT replace('hello world', 'o', 'z'); -- returns: "hellz wzrld"


texts: More than 20 columns in database table MySQL

texts:
I have an object, that is described by 40 properties, is it good a idea to create a table with 40 columns? I think that I cannot break these properties into another table (such as table_properties_1 and other).

What do you think, professionals?

upd:

Offer table will be contain more fields...


texts: How to maintain production database with less data and report database with all data

texts:
We have a large database. We want to remove previous months data from Prod DB and keep only 3 months data. We also want a Report DB with all data.

For Example: we have restored complete Prod DB backup on Report DB on 01-JAN-2018 00:00:00 on a separate server. Now on 1st of each month we want to restore previous month data on Report DB from Prod DB. From 01-Apr-2018, we will start removing oldest month data from Prod DB.

We are planning it do with MySql Queries and PHP. We will get backup from  Prod DB using table date fields and execute SQL queries on Report DB for each month. But is it the right way to do it?

Does MySQL provide any facility to achieve this with efficient way.

EDIT:

I want to know that, is there any MySQL mechanism similar to master/salve replication that run database structure/data changes queries on 'Report DB' at a specific time(daily or monthly) but does not DROP/DELETE from 'Report DB' when we DROP/DELETE from 'Prod DB'. So all 'Prod DB' queries(INSERT, UPDATE, ALTER) executes on 'Report DB' but DROP/DELETE queries are restricted on 'Report DB'.


texts: Python MySQL prepared statements slower than regular queries

texts:
I'm trying to use prepared statements with python to improve query performance, but my prepared statements are slower (by a factor of ~7.5 no matter how many iterations I set in my benchmark script).

Here is a small benchmark script I wrote:

In essence what I do for regular queries:

cursor = connection.cursor()
cursor.execute("INSERT into test (id,name) VALUES (1,'Value 1')")
... multiple inserts ...
connection.commit()

And for prepared statements:

cursor = connection.cursor(prepared=True)
sql = "INSERT into test (id,name) VALUES (%s,%s)"
cursor.execute(sql, (1, 'Value 1'))
... multiple inserts ...
connection.commit()

The script runs 2 loops with same number of iterations
1st loop runs with the default .cursor() while 2nd with .cursor(prepared=True) as per above

And here are the results:

As you can see MySQLCursorPrepared=9746ms while MySQLCursor=1299ms.

Why are my prepared statements slower than regular queries? Is it an issue with the python mysql.connector driver or do I need to tweak something in my MySQL config to fix this?


texts: Justify NOT using (nolock) hint in every query

texts:
Have you ever had to justify NOT using a query hint?

I am seeing WITH (NOLOCK) in every single query that hits a very busy server.  It is to the point that the developers think it should just be on by default because they hate seeing it in their code thousands of times.

I tried to explain that it is allowing dirty reads and they will end up with bad data eventually, but they believe the performance tradeoff is well worth it.  (Their database is a mess; no wonder they have performance issues.)

If you have a clear example of how to present the case against this abuse of the NOLOCK hint, that would be appreciated.


texts: How dangerous is it to have a user's password in plaintext in a cgi script?

texts:
I am setting up a MySQL database and am providing a web interface for it. Now, the DB is extremely simple and the interface just runs certain SELECT statements.
I have created a user for my script to use when connecting with these commands:

CREATE USER 'foo' IDENTIFIED BY PASSWORD 'bar';
GRANT SELECT ON dbname.* TO foo

I then have these lines in my Perl script:

my $user = "foo";
my $pw = "bar";

NOTES:

The data on the database are completely public and users can use/redistribute them at will. I have no interest in blocking anyone from accessing it, it is to provide a dataset to the scientific community.
The data are static, there is no need for anyone to be able to modify them in any way and the only user who has the right to is root (the only other user on the system).

So, my question is, how dangerous is this? Given that the user only has SELECT rights, what bad things can happen with this, clearly insecure, setup? Is there a better way of allowing access to my script bearing in mind the two notes above?


texts: Availability Group Upgrade 2012 to 2016 Data Move

texts:
I'm working with a team to upgrade a four node AlwaysOn Availability Group from SQL Server 2012 to 2016. Two of the nodes are local and two are in Azure. The database holds a large amount of data (2 Tb) and it can take hours to sync the nodes.

We're trying to minimize the downtime during the changeover. We were thinking that we could take a full backup of the primary and install it on the other three nodes right before the 'go live' but we're not sure if that will work. If you have experience setting up AG's what would be your advice on how to best handle this?


texts:
I'm wondering if I have to
store a XML information inside a column (just storing, not for querying ) (SQL SERVER)
which Datatype is better to use XML or Varbinary? I did an example:

DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>Juan</FirstName>
<LastName>Perez</LastName>
</Person>
<Person id="98765">
<FirstName>Alfredo</FirstName>
<LastName>Domiguez</LastName>
</Person>
</People>'

Declare @ClientListBinary Varbinary(max)
set @ClientListBinary = CONVERT (varbinary(max),@clientlist,1)
print datalength(@ClientList)
print datalength(@ClientListBinary)

and the result is that Varbinary uses more space... any thoughts on that


texts:
Creating a group or mailing list is not an option, and this may not be possible, but I need a way of scanning EmailMe and pulling the email address(es) to email from there.  If two email addresses have the same `vendorID ONE email should be sent not multiple.  Below is DDL - and using this as an example purple and green should both be the recipient on one email and blue should be sent it's own email.

How can I perform this?  I am going to use dbsendmail to send the message.

Create Table EmailMe
(
  ID  int IDENTITY(1,1) PRIMARY KEY NOT NULL
  ,name varchar(100)
  ,vendorid int
  ,email varchar(500)
)

Insert Into EmailMe (ID, name, email) VALUES
('purple', 12, 'purple@hsbcglobal.com')
,('green', 12, 'green@hsbcglobal.com')
,('blue', 1, 'blue@hsbcglobal.com')


texts:
I am trying to insert a large amount of data into a table.  The source for the data has the potential to repeat data in what is suppose to be a UNIQUE column.  I created the following to insert the data into my table:

DECLARE @OrderNum VARCHAR(20), @JobNum VARCHAR(20), @Supplier VARCHAR(100), @OldOrderID int, @UserNum int,
        @OrderID int, @JobID int, @SupplierID int, @ContactID int, @UserID int

DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
    SELECT order_id, job_num, supp_name, user_num
    FROM _po_temp_b

OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @OldOrderID, @JobNum, @Supplier, @UserNum
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @OrderNum = order_num
    FROM _po_temp_a
    WHERE order_id = @OldOrderID

    IF NOT EXISTS(SELECT order_number FROM purchase_order WHERE order_number = @OrderNum)

        SELECT @JobID = j.job_id
          FROM job AS j
         WHERE j.job_number = @JobNum

        SELECT @SupplierID = s.supplier_id
          FROM supplier AS s
         WHERE s.supplier_name = @Supplier

        SELECT @ContactID = sc.contact_id
          FROM supplier_contact AS sc
         WHERE sc.supplier_id = @SupplierID

        SELECT @UserID = ul.user_list_id
          FROM user_list AS ul
         WHERE ul.user_order_number = @UserNum

        INSERT purchase_order(order_number, job_id, supplier_id, contact_id, user_list_id, status_id, notes)
        VALUES (@OrderNum, @JobID, @SupplierID, @ContactID, @UserID, 2, NULL);

    FETCH NEXT FROM MY_CURSOR INTO @OldOrderID, @JobNum, @Supplier, @UserNum

END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

Now my thought was that the IF NOT EXISTS line would stop me from getting a bunch of data repetitions.  However, I am still getting repeat data entries, preventing me from setting the column up as UNIQUE, which is what it needs to be.

What exactly is wrong with my setup?  Am I missing something somewhere?


texts: Are all matching indexes (indices) searched for a query?

texts: If I have multiple indexes that include a common attribute - will each index be searched upon a query involving this attribute? Or is only one index searched?


texts: optimize design for a table with 50 columns

texts:
I have a table with 50 column and one of my friend has told me to split the table in 4 different table to save 12 column in each table. He proposed a design to have Master table and refer the PK of that master table in other four table, and then create NONCLUSTERED INDEX on the new table in all column as the limit is 900 bytes.

The idea was to improve the performance and convert key lookup to seek. I am not sure that it will really improve performance using join rather I think it hamper the performance, Please suggest me what to do here.

EDIT

Existing Table Test

CREATE TABLE Test
(
  A BIGINT,
  B BIGINT - FK --From some other table in DB
 ,C1 -Varchar(100),C2 -Varchar(50),C3 -Varchar(50),,C4 -Varchar(50),C5 -Varchar(50),C6 -Varchar(50),C7 -Varchar(50),C8 -Varchar(50) ,C9 -Varchar(50),C10 -Varchar(50)
 ,D1 -Varchar(100),D2 -Varchar(50),D3 -Varchar(50),,D4 -Varchar(50),D5 -Varchar(50), ,D6 -Varchar(50), D7 -Varchar(50),D8 -Varchar(50),D9 -Varchar(50),D10 -Varchar(50)
 ,E1 -Varchar(100),E2 -Varchar(50),E3 -Varchar(50),,E4 -Varchar(50),E5 -Varchar(50),,E6 -Varchar(50), E7 -Varchar(50),E8 -Varchar(50) ,E9 -Varchar(50) ,E10 -Varchar(50)
  F1 -Varchar(100),F2 -Varchar(50),F3 -Varchar(50),,F4 -Varchar(50),F5 -Varchar(50), ,F6 -Varchar(50),F7 -Varchar(50),F8 -Varchar(50) ,F9 -Varchar(50),F10 -Varchar(50)
)

Now the proposed design is to have four table with column name as

CREATE TABLE TestC
(
  PkId BIGINT,
  A BIGINT - FK --From the Master table Test
  ,C1 -Varchar(100),C2 -Varchar(50),C3 -Varchar(50),,C4 -Varchar(50),C5 -Varchar(50),
   C6 -Varchar(50),C7 -Varchar(50),C8 -Varchar(50) ,C9 -Varchar(50),C10 -Varchar(50)
)

Similar way other tables are, and the these tables are used in reading data. With big table Test here, I think covering index to use with big table Test.

EDIT2

I have requirement to read the data from these table after there are added and and in some case they will be update with status column set to 0 after some days and these rows will be no longer used and the table will have new fresh entry for the same user.

The target is to optimize the data read operation and also minimize the time in insert and update, which one would be a better choice here? Please help.


texts: I use the PL-SQL developer tool. I  log in by using Prodcollops as user name to access the database. How can I find out if  my DB user is Prodcollops, too,  or if  it is just a login name?


texts: Mysql takes up to 50% cpu after aborting DELETE query

texts:
I have a problem I have never met before. I was clearing yesterday client's db and table with stats of visits that had 15GB of data. I decided to delete all rows older than 1 Feb, because we have all that data stored in daily stats and there's no need to keep those old single stats.

I tried to run DELETE FROM sv WHERE datetime < ? (index on datetime) and DELETE FROM sv WHERE id < ? (primary index on id), but both queries where too slow. They took more than 15 mins. So I have just aborted those queries by Ctrl+C.

Then I thought a better idea would be copying the table structure and February rows and remove the old table. But today I found out that mysql process on server is running up to 50% cpu usage with normal traffic on our site.

After moving the sv table out of the database folder (MyISAM table) usage is still up to 50%.

First I thought that table must have been corrupted, but phpmyadmin does not show it marked as crashed.

How can I debug this one? What have happened there?


texts: Is there a way to allow a proxy connection to use passed authentication in a public database link?

texts: I think the answer to this is no, but I could be wrong.  If nothing else it might be nice for others to be aware of this limitation of proxy connections.