texts:
I have two tables, want to join them by an non unique column.

CREATE TABLE `order_item` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `soi` char(1) DEFAULT NULL,
  `product` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `inventory` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `inventory` char(1) DEFAULT NULL,
  `product` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `order_item` (`id`, `product`)
 VALUES
    (1,'aaaa'),
    (2,'aaaa'),
    (3,'aaaa'),
    (4,'bbbb'),
    (5,'cccc');

INSERT INTO `inventory` (`id`, `unitid`, `product`)
VALUES
    (1,'aa-002','aaaa'),
    (2,'aa-00e','bbbb'),
    (3,'aa-00w','cccc'),
    (4,'aa-00d','aaaa'),
    (5,'aa-003','cccc'),
    (6,'aa-003','bbbb');

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

order_item table
----------------
|id |product|
|1  | aaaa  |
|2  | aaaa  |
|3  | aaaa  |
|4  | bbbb  |
|5  | cccc  |

inventory table
---------------
|id| unitid | product|
|1 | aa-002 | aaaa   |
|2 | aa-00e | bbbb   |
|3 | aa-00w | cccc   |
|4 | aa-00d | aaaa   |
|5 | aa-003 | cccc   |
|6 | aa-004 | bbbb   |

i'm trying to get all the orders that can fulfill by inventory like below

|order_item_id| product | inventory_id | unit_id | product|
| 1           | aaaa    | 1            | aa-002  | aaaa   |
| 2           | aaaa    | 4            | aa-00d  | aaaa   |
| 4           | bbbb    | 2            | aa-00e  | bbbb   |
| 5           | cccc    | 3            | aa-00w  | cccc   |

I tried with inner join + group by like below its only returns one match from each table

select * from order_item
inner join `inventory` on order_item.product = inventory.product
group by inventory.product


texts: Correct Model for Users in a Collection with Items in that Collection

texts:
Sorry for my vague title, but I didn't really find how to succinctly formulate my issue. I have the following story I want to model:

There are Users that belong to a certain Group. These Users can place Items in that group, and they are 'visible' to all other Users in that Group only.

How I would initially do it: have a table for User, Group and Item. I would then place a UserGroup mapping table to map the Users in their respective Group, and the Item would have a Foreign Key to Group and User. Now I'd like to enforce also that when a User places an Item within a Group, that that user would actually belong to that Group in the first place, as to not have that Items could be placed in a Group by Users who don't belong to that particular Group, and I am having trouble wrapping my head around how to cleanly do this.

Can/should I enforce this on the DB level? If so, what would be an elegant solution to this? I am using SQL Server if this matters.

Edit: after thinking a little more on it, I would also add the following requirements: an Item can only be created by a User, so it shouldn't be able to be spawned into existence on its own. However, upon deletion deletion of a User, the Item should still exist, showing the User has been deleted. On deletion of a Group, the Item shouldn't necessarily 'stay alive'.


texts: Do I relate entities that are indirectly related when creating a student-course ERD?

texts:
I'm trying to create an ERD for a tutor center, this is what I understood from their operations:

There is the tutor center director, then under her there are sections/departments. It, math/calculus, languages etc.
Each section has its own section manager.
Each section has full time employees that analyse the section's data (no. of registered students, teachers etc).
Students that want to get tutored contact the appropriate section manager to get registered in a class
The section manager contacts one or more teachers from outside or inside of the tutor center to teach a class that many students requested
Some classes offer tests at the end (Ex. It classes offer ICDL tests while other classes don't).
At the end of the class students submit a teacher evaluation paper to the section manager.
For some classes many teachers could cooperate to teach that class

Now in a way I think all the entities are kind of related. For example the students didn't actually contact the teacher but they will be taught by the teacher so do I establish a many to one relationship between these two entities?

The other question is I ended up with many to many relationships and I don't know what kind of associate entity I should add between them.

This is the ERD I created:


texts:
There is a table in our database

tblCustomText(
  ParentID int NOT NULL,
  CustomFieldID int NOT NULL,
  Value char(50) NOT NULL,
  Log_CreateDate datetime NOT NULL
)

(ParentID+CustomFieldID) is unique.

Read operations are like: SELECT Value WHERE ParenID=@p1 AND CustomFieldID = @p2 (sometimes instead of WHERE, JOIN is used)

Write operations: 85% is INSERT, 13% is UPDATE, 2% DELETE. (By ParentID+CustomFieldID)

Now, I'd like to know what indexes should be defined for this table? (It contains ~6M rows)


texts: How to manually invalidate a pull merge replication snapshot from the publisher

texts:
XY Problem background info:

I have a pull replication publisher in which I want to add or alter a index and have those changes be applied to the subscriber.

The solution to that problem I would like to do is to generate a new snapshot and re-init the subscription.

My Question:

How do I, from the publisher, mark a pull merge replication publication as having a invalid snapshot such that if I did sp_helpmergepublication the snapshot_ready column would return 0.

Doing exec sp_reinitmergesubscription @upload_first = 'true' will cause the subscriber to re-initialize but it does not mark the snapshot as invalid.

I know I could change a publication or article property then change it back and cause to invalidation to happen that way but I would really like to invalidate as the "primary action" instead of having the invalidation be a side effect of some other action.

I am looking for something similar to transactional replication's sp_reinitsubscription procedure which has a @invalidate_snapshot parameter, but I could not find the equivalent for merge replication.

Is there any way to invalidate a merge replication snapshot only without making some other kind of change that has snapshot invalidation as a side effect?


texts: How to properly find duplicate rows with spatial and temporal criteria

texts:
I have a table with historical rows indicating the positions in which ocean samples were taken. From this table, I would like to find duplicates based on spatial and temporal criteria. Thus, the duplicates will refer to those rows whose time difference are less than 25 hours and spatial distances less than 5 km.

I tried the following code

CREATE TABLE dups_v2 AS SELECT DISTINCT ON (t1.cast_id) t1.cast_id, t1.date, t1.date_time ,t1.lat, t1.lon, t1.nlevels, t1.instrument
FROM good_dates t1 JOIN good_dates t2
ON ST_DWithin(t1.geom::geometry, t2.geom::geometry, 5000.0, true)
WHERE AGE(t1.date_time, t2.date_time) < interval '1 days 01:00:00' AND t1.cast_id <> t2.cast_id

In this case the result looks like:

cast_id   |      date_time      |   lat   |    lon    | nlevels | instrument
----------+---------------------+---------+-----------+---------+------------
 943046   | 1950-02-03 05:00:00 | 25.9200 | -114.5500 |      25 | mbt
 177584   | 1950-02-03 08:00:00 | 25.8200 | -114.8080 |      14 | osd
 943053   | 1950-02-03 08:06:00 | 25.8200 | -114.7700 |      24 | mbt
 943059   | 1950-02-03 10:00:00 | 25.7000 | -114.9700 |      22 | mbt
 4369553  | 1950-03-05 00:00:00 | 25.7720 | -114.8570 |      40 | mbt
 4369511  | 1950-03-05 04:00:00 | 25.9500 | -114.5000 |      38 | mbt
 4369514  | 1950-03-05 05:00:00 | 25.8700 | -114.6500 |      39 | mbt
 178401   | 1950-03-05 07:54:00 | 25.8580 | -114.7500 |       8 | osd
 4369529  | 1950-03-05 07:58:12 | 25.8200 | -114.7500 |      42 | mbt

However, the distance does not seem to fit my duplicate definition (< 25h and < 5km apart).

For example, the following two points seem to be farther than 5 km:

944388   | 1950-03-19 19:00:00 | 21.1700 | -108.3300 |      13 | mbt
944390   | 1950-03-19 22:00:00 | 20.7800 | -107.9000 |      13 | mbt

Could you help me to identify my error?


texts: MySQL SELECT .. WHERE .. ORDER BY ... LIMIT 1000000,10 performance

texts:
I have following Innodb table with ~2.500.000 rows:

ID | param1 | param2 | param3 | param4 | sort_field1 | sort_field2 | sort_field3 | sort_field4

ID - int, PK
param1-4 - tinyint(1), INDEX (it has only 0 OR 1 value)
sort_field1-4 - int, INDEX (it could be any number in INT range)

Each param(1-4) has value distrubution about 40% of 0 and 60% of 1.

I am trying to optimize fetching rows from the middle-end of table in this super simple query:

SELECT id FROM table WHERE param1=0 ORDER BY sort_field1 LIMIT 1000000,10

It took about 1.7sec to fetch 10 rows and EXPLAIN show that only param1 index is being used.

I can create index (param1,sort_field1) and it will reduce time to 0.17sec, but i can't create indexes for each and every combination of params and sort_field.

Is it possible to optimize this query and/or table to achieve time similar to one i got with combined index?


texts: Grouping data by year based on two timestamp columns

texts:
My data table has the following columns:

id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP

I want to product a report of the count of each name (which can appear several times in the table) that is active each year.
(also, the deleted timestamp may be null, if it's currently still active).

So far I’ve managed to do this by manually entering the years in a long list of union statements (see below). I’m sure there’s a better way! And I have many more similar queries to perform. I tried to create a PL/pgSQL function, but couldn’t figure out how to get the year as a variable and also how to get the correct output. I would be very happy with a statement, or PL/pgSQL function to achieve this.

((select '2016' yr, name, count(*) from data
where (((deleted - '2016-01-01'::timestamp) > '0 secs') or (deleted is null))
and (created - '2016-01-01'::timestamp) <= '0 secs'
group by name
order by count desc)
union all
((select '2015' yr, name, count(*) from data
where (((deleted - '2015-01-01'::timestamp) > '0 secs') or (deleted is null))
and (created - '2015-01-01'::timestamp) <= '0 secs'
group by name
order by count desc)
etc..

I got the years using:

select distinct date_part('year',created) from data order by date_part('year',created);

And then manually entered them in the very long union statement. (In my case 2007-2016!)


texts:
After we tried configure a database with service broker into an always on environment and failed (the service broker is not working properly) we are now considering to try mirroring for high availability.

The client demands it'll use automatic failover which i know needs a witness server. i have configured mirroring many times before but never with service broker.
Has anyone tried and it worked? I tried to find an official documentation for it but didn't find anything.
What are the steps to configure it on the mirrored database? will it cause performance issues to use service broker with mirroring which is also synchronized?


texts:
I have been appointed the DBA for our organization. I have been asked by senior officials to allow the two divisions of IT to have full control of their databases. They have asked for the following layout:

      Admins
SubAdmin  SubAdmin

These SubAdmins should be capable of performing all the functions possible on their own database, but should not be able to perform any functions on any other database.

That does not include server-level things like killing a session, building a logon trigger, changing Resource Governor settings, etc, but they should be able to create databases.

I have created a server role for the SubAdmin group which grants CREATE ANY DATABASE. This works well, except that when the SubAdmin attempts to change the owner of the newly created database to sa. They cannot because they cannot impersonate the sa account.

Would it be easier just to write some powershell script that changes the db owner to sa each night?

I use the sa account in this example just as a demonstration. We want the database owner to be a generic, low-privilege account. Therefore when the SubAdmin creates a database they:

Create the db
Grant their admin group db_owner rights
then should perform ALTER AUTHENTICATION to change the database owner to the generic, low-privilege account

The databases are all on the same server and same instance.


texts: How can I get the maxium number of concurrent connections to MySQL over a specific time interval?

texts:
I have a monitoring app that checks MySQL's Threads_connected  and Max_used_connections variables every X seconds.

Neither of these variables tells me what I want to know: The highest number of concurrent connections since the last check (X seconds ago).

In the past, I've used FLUSH STATUS to reset Max_used_connections some time after the critical level is reached, but this method isn't ideal for a number of reasons, and according to the docs:

  This is something you should use only when debugging a query.

Before I head down the path of a complicated analysis of the query log, is there anything I'm overlooking?


texts: How to optimize query for text array in PostgreSQL using `@>` operator

texts:
I have a column of type text[] and I want to search over this column using  this SQL operator @>

Note: @> operator, it filters the data as such ['a','b'] in ['a','b','c']

    The returned objects will be those where the values passed are a subset of the data

The problem though is that integer comparison is better than string comparison.
I'm thinking of another way maybe if Postgres hashes the values and only then it compares.

Note, I cannot make use of indexes because there is not only one column, moreover, the query will first filter on some id and then it needs to filter those multi-valued columns.

My question, Is there is some feature in Postgresql that supports comparing integers instead of strings.


texts: Supressing the Confirm box in an access vba function

texts:
I've got an Access database and in there I have macro which outputs a query to a csv file ready for import into another system we are using.  As part of this macro it makes a backup of the base table and then later changes the contents of the original table.  When it does the backup, I get a dialog box asking me to confirm that I want to overwrite the old backup table.

I don't want this dialog box coming up because the macro will be run by an end user eventually.  I'm using

DoCmd.CopyObject to do this.  Is there a way I can supress this dialog box?

Thanks

Kieran


texts:
I'm not sure if I used the right term in my question.

I am creating a prototype, which allows users to type in a raw SQL query, which will query the backend DB. I want to also make sure that users only apply SQl to the tables that they are allowed to.

So when a user types in something like SELECT * FROM t1 WHERE id > 10, I feel like I need to

Analyze the query to make sure that the tables are ok - in this case "t1"
They cannot access any other admin-type tables. For example, I don't want them to access "information_schema (i.e. for Postgres)"

What do you think the best strategy would be for this?

FYI, I'm NodeJS, so the primary programming language would be Javascript.

Would I need to "parse" the query to make sure that the tables being accessed by the query are ok?


texts:
How can I disable binary logging on MySQL 8.0?

I am needing to import a large MySQLDump file, and don't want to fill the Binary logs with many GB of data. In the past I would have set log_bin=0 in the my.cnf and restarted the database.

But with MySQL 8.0 I have tried various settings, but everytime I log onto the database, and run:

SELECT @@global.log_bin;

and it keeps returning 1

I have tried:

log_bin = 0
log_bin = OFF
#log_bin = 1

Is it possible to disable to the binary logs?


texts:
I have a sales reporting system with incoming data like this

date,editionID,outletId,Measure1,Measure2
2013-01-01,2,532,11,24

etc...

I am new to designing data cubes (for the pentaho BI suite) and when I was building the schema, everything was working fine until I wanted to define date as a date dimension. The pentaho schema designer needs this to be a foreign key reference to a date dimension table in order to use it as a dimension.

My question is, why do I need a separate table? How would I structure it? how would I reference it in the fact table?

Thanks!


texts: How to setup a MongoDB replica set for the connector

texts:
I want to keep MongoDB and Solr in sync. The mongodb-connector (python) is the tool I want to use to do that.
Now a replica set in mongodb is required (see ReadMe), but I don't want (and can) setup a set/instance.

So can I have a replica set setup with only the primary and without secondaries? Means: A replica set setup with only one instance? And how does this

Meanwhile I found a solution:

Add in the config-File following:

replSet=rs0 (name is variable) (necessary because starting mongod with replSet option didn't work)
oplogSize=[MemSize]

Start mongodb-instance:

sudo /etc/init.d/mongod start
mongo

Set replicaset:

rs.initiate({"_id" : "rs0","version" : 1,"members" : [{"_id" : 0,"host" : "localhost:27017"}]})
Check with rs.status()

In mongodb 3.2 "replSet" is changed to "replSetName"
https://docs.mongodb.com/v3.2/tutorial/upgrade-config-servers-to-replica-set/


texts:
I want to reduce the size of the ibdata1 and ib_logfile0 files on my MySQL server 5.7. However, I do not want to activate the innodb_file_per_table option.

What would be the best option to reduce the size of above mentioned files without activating innodb_file_per_table?


texts: PostgreSQL error: "subquery uses ungrouped column"

texts:
I have a query with subqueries that use only elements from the GROUP BY clause:

SELECT
        DATE(cm.created_at) as "date",
        COUNT(1) as total_message,
        room_id,
        (SELECT seller_id FROM chat_room cr WHERE cr.id = cm.room_id LIMIT 1) seller_id,
        (SELECT count(1) FROM "order" o WHERE o.room_id = cm.room_id) total_order,
        (SELECT sum(total_amount) FROM "order" o WHERE o.room_id = cm.room_id and DATE(o.created_at) = date(cm.created_at) )  total_order_amount
    FROM chat_message cm
    GROUP BY DATE(cm.created_at), cm.room_id
    ORDER BY DATE(cm.created_at)

Error:

ERROR: subquery uses ungrouped column "cm.created_at" from outer query

This part of the query causes the error:

(SELECT sum(total_amount) FROM "order" o WHERE o.room_id = cm.room_id and DATE(o.created_at) = date(cm.created_at) )  total_order_amount

How to solve this?


texts: PostgreSQL, How to keep only one schema?

texts:
I don't want some users be available to create schemas, and only use the public schema.

I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 documentation. How can I do this?


texts: SQL Query with multiple conditions on date

texts:
I want to write a query with condition on dates for below table and sometimes it requires great thinking.

StartDtm                             StopDtm
2013-11-03 00:00:00.000               NULL
2013-11-05 08:00:00.000               NULL
2013-11-18 09:00:00.000               NULL
2013-11-18 08:00:00.000               NULL
2013-11-19 08:00:00.000              2013-11-26 07:59:00.000
2013-11-20 08:00:00.000              2013-11-27 07:59:00.000
2013-11-19 08:00:00.000              2013-12-19 07:59:00.000

The table shows the task start date and task end date. Start date can never be null.
Last record shows that the task starts at 19th Nov. and will end at 19th Dec. So if I pass any dates, it should show all the tasks which need to be performed on any date between passed date range.

Conditions are as below:

1.Any date or both date parameters may be null. If so all data should come.

2.If @StartDtm is null and @StopDtm is passed, then it should bring all data based on StopDtm field only but it should not bring those data whose StartDtm is greater than @StopDtm. Same for @StopDtm parameter

3.If StopDtm field is null, It should bring all data based on StartDtm field only

I tried few ways but not succeeded. I to keep condition as short as possible. If possible, then please provide multiple queries with little explanation.

My query is as below:

DECLARE @StartDtm datetime='2013-11-15'
DECLARE @StopDtm datetime='2013-11-21'

select * from #TempTbl
where  ((StartDtm<=@StartDtm OR @StartDtm IS NULL)  AND (StopDtm>=@StopDtm OR StopDtm IS NULL))
    OR((StartDtm>=@StartDtm OR @StartDtm IS NULL) AND (StopDtm>=@StopDtm AND StartDtm<= @StopDtm OR StopDtm IS NULL OR @StopDtm IS NULL))
    OR((StartDtm>=@StartDtm OR @StartDtm IS NULL) AND (StopDtm<=@StopDtm OR StopDtm IS NULL OR @StopDtm IS NULL))

Thanks in advance..
The question is already asked in StackOverflow but I cannot find how to migrate it here. So asked here again.

Created another query which seems working fine:

DECLARE @StartDtm datetime='2013-11-20'
DECLARE @StopDtm datetime='2013-11-30'

IF(@StartDtm is null)
begin
    SET @StartDtm='1900-01-01'
end
IF(@StopDtm IS NULL)
begin
    SET @StopDtm='9999-12-31'
end

select * from #TempTbl
where (startDtm<= @StartDtm and ISNULL(StopDtm,@StopDtm)>=@StopDtm )
  OR(StartDtm>=@StartDtm and ISNULL(StopDtm,@StopDtm)<=@StopDtm )
  OR(StartDtm between @StartDtm AND @StopDtm and ISNULL(StopDtm,@StopDtm)>=@StopDtm  )
  OR(StartDtm<=@StartDtm AND ISNULL(StopDtm,@StopDtm) between @StartDtm AND  @StopDtm)

Suggest if any logical error/change/improvement.


texts: mysql replication and innodb_file_per_table

texts: Is it possible for the master (5.1) to have innodb_file_per_table = 0 and for the slave (5.1 or 5.5) to have innodb_file_per_table = 1 ?


texts: Build replication from backup not publication source

texts:
I have an interesting scenario I am trying to find a solution to.

We currently have the current setup.

ProductionDatabase (publisher) is the publication for transnational replication to the following:
  WarehouseDatabase (subscriber)
  NewWarehouseDatabase (subscriber)

My issue is that WarehouseDatabase has had triggers and other SP's added to it.  When I build replication for NewWarehouseDatabase I built it from a backup of ProductionDatabase.  So NewWarehouseDatabase does not have the triggers or SP's that had been added to WarehouseDatabase.

Doing a SQL Compare generated a list that had a lot more things displayed than I really want to manage.

So I thought I can build replication by backing up WarehouseDatabase and restoring to NewWarehouseDatabase and then enabling replication from ProductionDatabase.  Then I thought that will not work as the LSN and other internals that might not sync up for replication to work.

I am wondering if anyone can suggest how I can accomplish this, if you can understand my babbling...

Regards,
GarryB


texts: How to get the date part from a date with timestamp column in SQL Server?

texts:
I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').

I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.

Edit:  I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:

concat
(
  CONVERT(varchar, DATEPART(dd,columndate)),
  CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
  CONVERT(varchar, DATEPART(year, columndate))
) AS trial

This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.

Is there any other way to achieve this?

Any ideas/suggestions much appreciated.


texts: Running select affects performance of stored procedure called from code

texts:
In what scenario would running the same select statement in SSMS as that in a stored SP which is currently timing out start making the SP (which is being called from a service every minute (30 sec timeout)) start working?

The select in SSMS doesn't return any results and can be canceled after 60 seconds. At which point the previous failing SP is now returning results. If you run the select while the SP is being called from code it takes about 2 seconds to run.

No parameters are passed into select, there is a join to a busy event table.

To sum I'm looking for explanation as to how running one select can affect (for the better) another select.


texts:
I have 3 MySQL servers say A,B,C.

What I want to do is that I want to make all of them master as well as slave.If there is update on any of the MySQL Servers it should be replicated to all of the Servers.

I have studied about the circular replication and found it can be implemented with it.

Can anybody please give me all the steps to accomplish the Replication as stated between three servers.

One More Point i want to ignore some of the tables from a database also.

Also What are caveats of this type of replication?


texts: SQL Server - select column_name from (multiple tables_name stored in a variable)

texts:
Let's consider 4 Tables: [Starter], [Meal], [Dessert], [Drinks]. I need to check in each table if the column [Price] exceed at least one time 15. And retrun the value into the table [My_table] to insert the boolean into the row with the table name.

 update [DB].[dbo].[My_table]
          set [Check_column]=
             case when [price] > 15 then 'OK'
             else 'WRONG'
             end
    from @multiple_tables_names

I would like to use this update statement for checking the content of the column [Price] in multiple tables (which have the same structure).

This would be the table My_Table:

 [Type] |               [Check_column]
----------------------------------------------------------
Starter |                   OK
-----------------------------------------------------------
Meal    |                  WRONG
-----------------------------------------------------------
Dessert |                    OK
-----------------------------------------------------------
Drinks  |                  WRONG


texts:
I am trying MySQL Workbench 6.0 and when I execute a query the results are displayed with a tab called "Result 1". When I fire another query the same tab is reused, now named "Results 2".

I found no way to open a new results tab (not query tab). What am I missing?


texts: Collect all Similar Persons to One Group

texts:
I have a person with several Id's.
Some of them in Column Id1 and Some of them in Id2.
I want to collect all the same persons Id's to one group.

If id1=10, is in the same row with id2=20. so it's mean that person with id1=10 he is the same person like id2=20.

The Input and Output example:

Input

Id1     Id2
---     ---
10      20
10      30
30      30
10      40

50      70
60      50
70      70

Output

NewId   OldId
-----   -----
1       10
1       20
1       30
1       40

2       50
2       60
2       70


texts: How to do per-tenant backups for multi-tenant databases?

texts:
Are there any best practices, experiences or guidelines when it comes to do per-tenant backups/restores in a microservice multi-tenant application?

In my case I have multiple Postgres servers which are regularly backed up as a whole. Each of them contains a single database which is migrated using Flyway.
The single database contains one schema with all the tables with the data of all tenants. Tenants are identified by UUID and all the data has foreign key relations so that e.g. one tenant can easily be deleted.

Now there's the requirement to be able to do restores for an individual tenant. I have found very little about the entire topic so far, but the only process I have come up with so far is this:

Restore all the backups to a different set of databases or different database servers
Spin up a new instance of each microservice so that it can start in isolation and is able to perform the necessary database migrations, if required
Delete the tenant from each of the production databases
Execute script or code in each service that identifies the data in the respective restored database and copy exactly these rows over to the production database

However doing or automating this seems like a disproportionate effort to me. I'm specifically looking for alternative (simpler) approaches, tools or databases that can simplify this process or actual experience how other people approach this problem.


texts: What versions can I have for setting up SQL Server Transactional replication

texts:
Can anybody help me knowing what versions can I have replication for Publisher , Subscriber and Distributor.
Do I need to have all 3 servers as same sql server version or can I mix the versions ? what are Limitations if I have lower and higher versions and what are the recommendations?

Question 1 :
What possible SQL Versions can I have for Publisher   ?
What possible SQL Versions can I have for Subscriber  ?
What possible SQL Versions can I have for Distributor ?

Question 2:
Also, is there can I have mix of Enterprise and Standard and Express Editions for Publisher, Subscriber and Distributor?

Thanks in advance.


texts: Can't create database even with all privileges


texts: Prevent SysAdmin from doing a Database Restore

texts:
I am a sysadmin on my SQL Server 2008, and I need to be, but I would like to setup my security on the Production server to prevent me from accidentally restoring to the production database.  I restored databases to a test machine often for developers to debug/test and while I am always really careful, after about ten years, today I wasn't.

My thought was to put in a DENY on the restore permission and if I ever need to really restore, remove the DENY, but I can't find anything like this.

Does anyone know how to do this or maybe have a better idea?


texts: Access denied when connecting to mongosqld with MySQL

texts:
I'm trying to connect to my MongoDB using MySQL through mongosqld. I have mongosqld running with a config file that looks like

security:
  enabled: true
mongodb:
  net:
    uri: test-db
    auth:
      username: usertest
      password: pass
      source: admin
schema:
    path: schema.drdl

I have it hosted on a mongo ODBC manager with SERVER=127.0.0.1, PORT=3071, DATABASE=test_db, UID=usertest?source=admin, PWD=pass. I am able to connect to and query this MongoDB through Excel using Mongo's tutorial for that, but I am not able to do the same with MySQL using Mongo's tutorial. When I try to connect from terminal with mysql 'user=usertest?source=admin' --default-auth=mongosql_auth -p I get ERROR 1045 (28000): Access denied for user 'usertest' andhandshake error: unable to saslStart conversation 0: Authentication failed.` from the mongosqld side. I am doing this on macOS. What could be causing this problem only for trying to connect from MySQL?


texts:
I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly uses the pattern of preparing a static cursor for which it will only use the first few entries of, and often that takes tens of milliseconds. There are thousands of these queries in a continuous stream.

Truth of the matter is that due to the curious design of the application data integrity isn't an issue at all with any of these queries, so the fact that they're static is miserable since performance would be about thirty times faster if they were dynamic.

After dealing with the vendor for weeks I've been getting nowhere. Truth is all I really need to do is change one parameter in the way the application uses sp_cursorprepexec. I could even do it in a global way across the entire server if necessary.

I'm looking at any and all solutions, whether it be using a feature in SQL Server 200x that I haven't been able to find, writing a small app that sits in between SQL Server and the client and modifies the data (although I wouldn't be looking forward to figuring out how to deal with the TDS protocol in such a way), or somehow renaming sp_cursorprepexec and replacing it with a wrapper.

Sky's the limit.

(FYI, we're running SQL Server 2005 right now, but if there's a convincing reason to shell out the extra money to upgrade the license it could happen.)


texts: Can I hide my password for data at rest encryption key?

texts:
For MariaDB encryption at-rest, my setting is
[mysqld]
# at-rest encryption
plugin_load_add              = file_key_management
file_key_management_filename = /etc/mysql/rest/keyfile.enc
file_key_management_filekey  = FILE:/etc/mysql/rest/keyfile.passwd
file_key_management_encryption_algorithm = AES_CBC

However, if hackers steals the database, they has access to both keyfile.enc and keyfile.passwd and the point of encryption at-rest is useless.
Can I tell the key manager not to read the super password from the file. Instead, read it from the user at the startup?


texts: ORA-01652: gaining an understanding of tablespace and datafiles

texts:
I've looked at other posts (1)(2) around this error, but what I don't understand is where certain objects are located. I am unsure about where the tablespace (TEMP in my case) is exactly, and where the datafile that is added to it to extend its size would be located.

In several posts, I saw a file path for the datafile, but I don't understand where that's stored. To give some details, I am on macOS, and connecting to an Oracle database by using a bridge. Where would this datafile go in my case?

I've had a look at resources like this, but I am not the admin for the database I am using, and I am not sure what the equivalent tables are named in my database. How can I find out?


texts: Recommendations on creating user-objects on system database

texts:
I want to setup a bunch of Event Notifications on SQL Server using Service Broker for our production database.

I've setup a new database on my testing machine, and everything works well.
The problem is that I'd rather not have to deploy a whole database for this notification purposes and I can't use one of the user databases already in place (that would be a mess, anyway).

If possible, I'd preferably deploy the notification objects on master, but first I'd like to be sure this is actually a good idea (if it actually is).

From Books Online:

  Do not create user objects in master. If you do, master must be backed up more frequently.

Books Online recommends I do not create user objects in master, but in this case, I don't think it's that much of a problem since I don't really care if I eventually lose some notification data. Besides, we already do backup our master database frequently.

Should I use the master database or something else in this case?

Is there something I should know about the master database, Service Broker or Event Notifications before setting this up?


texts: Combine relational DB and Elastic search

texts:
We have a large quanitity of text files we want to free-text/full text search, combined with relational structured metadata about the text file.
So, a search could be "Give me all files that belong to group X(or sub groups of X), have author (Ari and Bari and Mari), belongs to organization Y, and contains the text "synthetic". The latter part being a full-text search, and the other being already stored as relational data in our existing db.

In our database(which is rather complex), there are stored a way to ID the files, and a ton of various metadata about the file, spread among tens of tables, ranging from simple 1-1 relationships, to 1-many sets pr file, and even tree-structure relationship(things like "this file is type X, type X is a subgroup of type Y, etc).
And this metadata may change over time, all over the application(which is huge).

Now, I as a database admin, thought this could be solved by using SQL Server to do the search for structured metadata already in the DB, constraining the search to candidate-files, and then passing the candidate file id's to elastic search for full-text searching. (Re-indexing the file on elastic when a file is added or commited is trivial in our code)

However, the elastic-guys in our project naturally had a different idea:
To extract all the meta-data as well as the full-text content from the files, to elastic-search, and run the search exclusively in elastic.

This allows them to run full powered lucene queries easily, and load is taken off the database, which is nice. However, this also to me, introduces a nightmare to keep the structured metadata in sync, and blindly re-indexing/syncing everything periodically is not possible due to the scale of data.

I can see merits/concerns to both options. Is there a best practice for this kind of thing?


texts: Odd SQL Server Studio error, but not on SQL Server Express?

texts:
I'm running a query that I got today from a previous question, and I just noticed an error that only happens when I run this query ON the server (RDC + MS Studio -- not express) vs on my machine which has MS Studio Express.

Here is the Error:

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '('.

It refers to this line in my script (full script here on DBA):

FROM sys.dm_db_index_physical_stats
    (
        db_id(@DB),  <---
        OBJECT_ID(@Table),

The odd thing is, that IF I run this off my local machine, using MS Studio Express AGAINST that same SQL server (dev box), it runs just fine and I get results.

HOWEVER if I remote into the SQL dev box, and paste the VERY SAME sql into the MS Studio (non-express) it throws that error msg 102... above.

I'm scratching my head why that is, my only thought is that the Studio Express is not validating it the same way as the non-studio client?

Here is the version info for the client on the SQL server:

Microsoft SQL Server Management Studio  9.00.4035.00
Microsoft Analysis Services Client Tools    2005.090.4035.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML             2.6 3.0 4.0 6.0
Microsoft Internet Explorer         8.0.6001.18702
Microsoft .NET Framework            2.0.50727.3625
Operating System                5.2.3790

and my client:

Microsoft SQL Server Management Studio Express9.00.4035.00
Microsoft Data Access Components (MDAC) 6.1.7600.16385 (win7_rtm.090713-1255)
Microsoft MSXML             3.0 4.0 5.0 6.0
Microsoft Internet Explorer         8.0.7600.16385
Microsoft .NET Framework            2.0.50727.4963
Operating System                6.1.7600

Any ideas?

EDIT 1:

Per @gbn's suggestion I ran the version check on both (SELECT @@VERSION, @@SERVERNAME):

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition
on Windows NT 5.2 (Build 3790: Service Pack 2)

(server name is same -- not posted)

EDIT 2:

Per @DaniSQL's suggestion I ran SELECT compatibility_level FROM sys.databases WHERE name = 'DATABASE_NAME' and the result was 80 (from both SMSS Express and while RDC'd to the box and connected with non Express SSMS)


texts:
We have a database using the AlwaysEncrypted feature of SQL Server 2016.

We need to create SSIS packages (deployed on SSISDB) for certain application tasks.

What options we have to store the key securely so those SSIS packages can use that key to decipher the data.  Assuming the application DB and SSISDB are on same server

Alternatively, Would it be mandatory/good practice to have the application DB and SSISDB both on different servers ?


texts: Is it normal to UPDATE STATISTICS this frequently?

texts:
I have a stored procedure that processes data from a source table and is executed many times (about 20,000 executions) throughout the day.

I believe it's getting choked up sometimes, due to parameter sniffing and I've been updating statistics on the relevant tables (8 tables) in the procedure to fix this.

When it gets choked up, a backlog of unprocessed data builds up (which I'm able to monitor the count on.)

Once I update the statistics the backlog is instantly cleared.

I find myself updating statistics about 10 times a day (almost once an hour).
Does this seem normal or is there a bigger issue I should be looking to fix?


texts:
Is there a way to cause every statement to be sent to the server immediately, or in its own batch, in SSMS?

I am working on a large set of statements within which I alter tables, disable/enable temporal history, change values, etc., and SSMS seems to be getting in the way in a big way. If I ran the commands as normal in a CLI, or I select-and-run manually, it works. However for some reason in SSMS I need to insert go statements in between everything in order for it to work.

For example, adding a column and then subsequently updating its values, without go it tells me the column name is invalid, with go it works as expected. I can only imagine that SSMS is somehow interpreting the commands and comparing to its idea of the tables and causing the "error"--not the server.

I would prefer a statement like go away at the start of the whole set/file, to make it actually run each line as-is. Is this possible?


texts: MySQL slave stuck at 'System lock' most of the time, high CPU usage

texts:
Let me describe what we are facing now:

We have a MySQL Master-Slave, Row Based Replication setup and at the moment, the slave is stuck with a perpetual System Lock. The Master is an active server with many updates and deletes running, but the slave will just not replicate anything. There are no errors in the log and the slave is reading binary logs fine from the master, but it does nothing on the slave. The Seconds_Behind_Master value keeps increasing. This is what show processlist on slave shows:

mysql> show processlist ;
+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                            | Info             |
+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
| 10 | system user |           | NULL | Connect |  4985 | Waiting for master to send event | NULL             |
| 11 | system user |           | NULL | Connect | 53715 | System lock                      | NULL             |
| 14 | root        | localhost | NULL | Sleep   |  2958 |                                  | NULL             |
| 16 | root        | localhost | NULL | Query   |     0 | init                             | show processlist |
+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
4 rows in set (0.00 sec)

And show slave status shows:

SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock

The only sign of life is that Relay_Log_Pos from the slave status is changing values but very slowly. Does it mean that it is running the queries from the binary log, but just that it is too slow?

Pasting some info from show engine innodb status :

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 14, OS thread handle 0x7f7824698700, query id 216193 localhost root init
show engine innodb status
---TRANSACTION 230426904, not started
mysql tables in use 1638, locked 1638
MySQL thread id 11, OS thread handle 0x7f7824920700, query id 216192 System lock
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)

Also noticed that the mysqld process for this particular mysql instance has high CPU usage. What could be causing the Slave_SQL_Running_State: System lock and blocking the slave from running the queries? There are no disk issues with the machine and there is another MySQL instance running which is a slave to another MySQL instance (myisam) on the same master and that is running fine.

MySQL version : 5.6.20.
OS : RHEL 6.5
We have some tables that are partitioned (if that matters).

Edit: Just noticed that Slave_SQL_Running_State is occasionally changing to 'Reading event from the relay log'. So it looks like the replication is just too slow.


texts: Who all people have made life changing contributions to database research? I want to know more about them.


texts:
In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable:

    define my_schema_name = 'schema_1';

    CREATE OR REPLACE PROCEDURE my_schema_name.updateDBUSER(
         p_userid IN DBUSER.USER_ID%TYPE,
         p_username IN DBUSER.USERNAME%TYPE)
    IS
    BEGIN

      UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

      COMMIT;

    END;


texts: How to enable JMX monitoring in Oracle 19c

texts:
I have installed Oracle 19c DB in windows 10 PC
Installation was successful and added a user, Followed the official documentation to setup JMX
https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdev/using-JMX.html#GUID-22A8F168-81B8-4CFF-B2F9-1EE281BC1502
Procedure call was successful, still i'm not able to monitor my oracle instance in Jconsole
What i'm missing here?
Thanks in advance


texts: modeling data dimensions as columns - some dimensions are highly variable while others have low variability

texts:
Please excuse my question if it is a bad one - I am not a DBA...

I would like to model the following data:

Let's say that I have one column that is the row id as generated by a sequence. Let's also say that I have 3 columns: A, B & C that have low data variability. Let's also say that I have 4 more columns I, II, III, IV with high data variability.

As a concrete example, lets say that the values of A, B & C can be any number between 1 - 50, and the values of I, II, III & IV can take any textual data, as another example, any number between 1 - 1B.

The question:
Should I model a single table with columns: row_id, A, B, C, I, II, III, IV. Or should I model two tables the first to contain the low variability data columns: row_id, A, B, C and the second to contain the rest of the columns while referencing the row_id of the first table: row_id, first_table_row_id, I, II, III, IV.

I am using postgresql. What answer best fits that database? What answer is the generic best?

Also, there might not be a clear cut answer, if so, what are the pros and cons to each modeling scheme?


texts: Getting to know SQL via the "short questions/simple answers style of learning"

texts:
Looking for a incremental SQL course that would likely be similar to the this Ruby Koan course; which is in fact inspired by the book, "The Little Lisper", which uses a short questions/simple answers style of learning.

Basically the format is short questions are within a well defined context, with a simple answer, that incrementally building and leveraging the student's knowledge within the subject matter, while keeping the to the short questions/simple answers style of learning.

Also, I just wanted to note that I know there are a number of ways to learn SQL, but I'm looking for an existing implementation of this way. This style provides a fixed-path with stepping stones, and if you do not finish a set of questions, you're not allowed to go on, and even if you were able to find a way to cheat this, it would just make things harder, not easier; meaning it's not just free-form learning with information present small packets.


texts:
I'm been beating my head up against a wall with this all day.

I have a SQL Server table called Accounts

CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL)

    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson')
    GO
 INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0)
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall')
    GO
 INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0)
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole')
    GO

I'm trying to eliminate the duplicate rows with 0 as the FirstName and LastName.

The non duplicate rows with zeros I want to keep. I want to eliminate only the rows that contain zeros that have a duplicate IDNum.

I can't add an a primary key column.

My first approach was to find the duplicates, put in table variable, filter the zero rows then union that back with the table excluding those ID numbers. Then I thought it has to be a better way. Any help?

End Result


texts: Jobs view is not visible to read-only account users

texts:
I am the admin of one of the database, and I have created a view in SYS schema using below code which returns the status of the running jobs:

CREATE OR REPLACE VIEW CAF_DB_REFRESH AS
SELECT *
FROM all_scheduler_job_run_details
WHERE OWNER = 'CAFANALYST'
  AND log_id > 2573
ORDER BY log_date;

I have created public synonym for this view and given access to read only account which will be used by all users in my team.

When they perform the following query, they are getting only column names with no rows:

select * from CAF_DB_REFRESH;

Could you please guide me what is the problem and if any special privileges required for read only account? If yes, I request you to guide which one is the best to see but not to modify by read only user.


texts: Issue patching SQL Server 2016 to SP2 CU 15

texts:
Trying to update all our SQL 2016 servers to SP2 CU 15, but am running into:
The version of SQL Server instance MSSQLSERVER does not match the version expected
by the SQL Server update. The installed SQL Server product version is 13.1.4001.0,
and the expected SQL Server version is 13.2.5026.0.

SSMS shows the server to be at 13.0.4259.0
@@Version shows:
Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64)   Jun 15 2019 19:20:12
   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016
   Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

What do I need to do to get the SP2 CU 15 patch applied to this server?


texts:
I have an OleDb Command component in a dataflow that does an update(yes, I'm aware of the performance implications).

Running the pack in BIDS shows me that the component is being reached and runs correctly, turns green when complete, etc. However, I don't see the update in the database.

I am logging all events on the component, but there does not seem to be a way to get the actual command text sent to the server. I can see the ICommandText::SetCommandText, ICommandPrepare::Prepare and ICommandWithParameters::GetParameterInfo events complete, but nothing changes in the db. What else can I look at to find out what is really happening between these two systems?


texts: Is it good practice to rebuild index on FileTable?

texts:
We are using Ola Hallengren scripts, both to backup our databases and for index maintenance.
We have a FileTable in our database and from time to time, REBUILDING its PK index takes close to three (3) hours, the rest of the time it is very fast.
Questions :
is it a good practice to reindex a FileTable ?
which is abnormal, taking only a few seconds or taking a few hours ?
I have done a few searches about reindexing a filetable but have not really found anything useful.


texts: how to create an explain plan for the below query? I'm getting ORA-00900: invalid SQL statement error

texts:
I'm getting ORA-00900: invalid SQL statement error when i tried. but the sql query is running fine without explain.

Query: create or replace view TaskView as
   select T1.PersonID,T2.ID ,T2.Task from Table1 T1,Table2 T2
   where T1.TaskID=T2.ID;


texts: Oracle 11.2.0.4 -- "sofar" increasing slowly in V$SESSION_LONGOPS

texts:
I am running a long-running query, and it seems to be "stuck" on this operation here:

http://i.stack.imgur.com/MPnFB.png

From v$session_longops, the value of "SOFAR" seems to be increasing at 2-3 blocks per minute on this sort operation. The "time remaining" is misleading as it has been stuck at 34 seconds. I don't see any other contention in the database that may be causing it.

I would appreciate some pointers as to where I could look next. I don't see any abnormal level of wait events.
Thanks


texts: CASE expression returns wrong value when using CEILING

texts:
I've run into an issue where a CASE expression does not return what I expect.

As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the results as I would expect (rounding the value up when IsGun=1. But when I run that same CASE expression against another decimal value, it always returns the value with the CEILING() function and never returns the original value.

Here is the SQL code:

DECLARE @Num decimal(8,2);
    set @Num = 12.54;
    WITH PQ AS
    (
        SELECT
            UPC,
            Price1,
            DBID,
            AVG(Price1) OVER (PARTITION BY UPC) AS Price1Avg
        FROM
            vProducts_PriceQty_Union
    )
    SELECT
        PQ.UPC,
        PQ.Price1,
        PQ.Price1Avg,
        (CASE WHEN p.IsGun = 1 THEN CEILING(@Num) ELSE @Num END) AS UsingVar,
        CAST(
            (CASE WHEN P.IsGun = 1 THEN CEILING(PQ.Price1Avg) ELSE PQ.Price1 END)
             AS NUMERIC(8,2))
        AS PriceAdj,
        PQ.DBID,
        P.IsGun
    FROM
        PQ
     INNER JOIN
        products P ON PQ.UPC = P.UPC

Here is a snippet of the results:

UPC             Price1      Price1Avg   UsingVar    PriceAdj    DBID  IsGun
942000899195    14.9900     14.990000   12.54       15.00       1       0
980420671300    29.9900     29.990000   12.54       30.00       1       0
980420671310    29.9900     29.990000   12.54       30.00       1       0
980426713020    29.9900     29.990000   12.54       30.00       1       0
980426713120    29.9900     29.990000   12.54       30.00       1       0
000998622130    319.0000    319.000000  13.00       319.00      1       1
000998624730    314.0000    314.000000  13.00       314.00      1       1
000998624970    419.0000    419.000000  13.00       419.00      1       1
008244284754    1015.0000   1015.000000 13.00       1015.00     2       1
010633012288    267.0000    267.000000  13.00       267.00      6       1

And here is the data it comes from vProducts_PriceQty_Union:

UPC             Price1  Price2  Quantity    DBID
942000899195    14.9900 0.0000  2.00        1
980420671300    29.9900 0.0000  3.00        1
980420671310    29.9900 0.0000  1.00        1
980426713020    29.9900 0.0000  2.00        1
980426713120    29.9900 0.0000  1.00        1

As you can see from the first five, where IsGun = 0, the first CASE expression using the fixed variable returns the UsingVar value as what we would expect, 12.54. And for the last five, it also returns the value we would expect, 13.

But in the second CASE expression (exactly the same logic), the PriceAdj uses the CEILING function on every single one of them, regardless of whether IsGun = 1 or not.

Why isn't the query returning the expected results?

In some of the tables used for the union view the data types for Price1 and Price2 were smallmoney and decimal(8,2). I have since changed them all to be decimal(8,2), but that did not affect the results.


texts: Database T-Log is full due to 'Active_transaction'. SQL server is using new T-log file first rather than old

texts:
I had a SSIS process which failed due to T-log file Full for the Stage database. T-log file max growth is limited to 510GB however only 180-200 GB of t-log file space is getting used everyday.
Even though T-log file had an enough space but it was failing with T-log FULL error. in order to troubleshoot I've added second T-log file (549 GB) to the new drive.
My understanding is that SQL server will use first T-log file first and once it is reached to max limit then only it will start using 2nd T-log file however I noticed that it started using new T-log file rather than old first. It does not look like normal behavior.
How does SQL server decide to use old T-log file or new T-log file first?
SQL server version:- SQL server 2012 SP4


texts:
So I was trying to change some settings in my Microsoft SQL Server Management Studio 2019. I change the login mode from mixed (Windows and SQL authentication) to only Windows and now I am not able to login with my sa account and I don't have another account which has admin rights.

What should I do? Any suggestion will be highly appreciated.


texts: Does MAXDOP affect the number of tempdb data files I should create?

texts:
From what I have read, best practices for configuring tempdb includes creating multiple data files.  The rule of thumb as I understand it is if you should create the same number of data files as you have logical processors up to a max of 8.  Then if you have tempdb contention add an additional file until either the contention goes away or you have added an additional file per 4 additional logical processors.  So if you have 16 logical processors then you would go up to a max of 10 files.

What I'm curious about is if MAXDOP affects this at all?  So if for example I have 12 logical processors.  I have set my MAXDOP to 6 because they are broken into 2 NUMA nodes.  Does this mean I should stick with 6 data files for tempdb?  Or would I still have the 8 or 9 that is recommended for the total number of processors?


texts: Who should own the staging database right

texts:
At my company we are re-assessing which developers will take on the deployment for xyz ticket. At the moment when a developer is done with his ticket, a deployment ticket is created and another developer will take it, do the code review and test the change(s).

We have 3 databases

Development
Staging
Production

The problem is that we don't really have a real DBA, only someone assigned to validate and run the databases changes. In the development environment we(developers) own the DB and have all the rights, but when we make a ticket for the deployment(which requires the staging DB for testing) we don't have the necessary rights and this is where, i think, there is a problem since we need to create a new ticket to have our SQL changes run on the staging DB and it may take some time before those changes are made. After all the review, test and feedback processes we're ready to push the new changes into production, however we're blocked again and need to make another ticket to have our SQL run in the production environment.

My 2 cents on this is that since developers do the deployment and the staging database is for the deployment we should be the owners and making ticket after ticket is just a waste of time because it slows down the deployment process and has side effects for our clients because new features/bug fixes don't come as fast as they should because we're stuck with ticket after ticket.

Who should own the staging database and why?


texts: Maximum number of columns per index key in Ms SQL Server 2014

texts:
I have a large table with about 200 million records. I want to define a composite nonclustered key on it.
What is the maximum number of columns per index key in Ms SQL Server 2014?


texts: Can I get a tree-structure from a self-referenced (hierarchical) table?

texts:
Given a hierarchical table like this:

CREATE TABLE [dbo].[btree]
(
  id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);

I would like to obtain the whole tree structure.

For instance, using this data:

INSERT INTO [btree] VALUES (1, null, '1 Root');
INSERT INTO [btree] VALUES (2,    1, '1.1 Group');
INSERT INTO [btree] VALUES (3,    1, '1.2 Group');
INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group');
INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group');
INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group');
INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group');
INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items');
INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items');
INSERT INTO [btree] VALUES (11,   5, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items');
INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items');
INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');

I would like to obtain:

+----+-----------+---------------------+
| id | parent_id | description         |
+----+-----------+---------------------+
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  4 |     2     |     1.1.1 Group     |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.2.2 Items |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
+----+-----------+---------------------+

I'm fetching records using a recursive query like this:

;WITH tree AS
(
    SELECT c1.id, c1.parent_id, c1.name, [level] = 1
    FROM dbo.[btree] c1
    WHERE c1.parent_id IS NULL
    UNION ALL
    SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1
    FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.level, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description
FROM tree
OPTION (MAXRECURSION 0)
;

And this is the current result:

+----+-----------+---------------------+
| id | parent_id | description         |
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  4 |     2     |     1.1.1 Group     |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.1.2 Items |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
+----+-----------+---------------------+

I can't figure out how to order it by levels.

Is there a way to set a rank for each sub-level?

I've set up a Rextester


texts: There is a column named in table but it cannot be referenced from this part of the query

texts:
I've been learning Postgres (coming from SQL Server), and this error really confuses me.

Here is the code with some sample data:

create table T (
ID serial primary key,
A varchar(1),
B varchar(1),
C varchar(1)
)

↑ Testing table.

insert into T (A, B, C)
values('A', 'B', 'C'), ('A', 'B', 'C')

↑ Insert duplicates

delete from T
where ID in (
    select t.ID
    from (  select ID, row_number() over (partition by A,B,C order by A,B,C) as rn
            from T) as t
    where t.rn < (select max(t.rn) from t)
    )

↑ Delete duplicate keeping the last entry.

The problem is in the (select max(t.rn) from t) I am assuming this is a noob mistake related to not knowing postgres syntax when it comes to referencing columns with aliases?


texts: Is it possible to get seek based parallel plan for distinct/group by?

texts:
An example from this question shows that SQL Server will choose a full index scan to solve a query like this:

select distinct [typeName] from [types]

Where [typeName] has a non-clustered, non-unique ascending index on it.  His example has 200M rows but only 76 unique values.  It seems like a seek plan would be a better choice with that density (~76 multiple binary searchs)?

His case could be normalized but the reason for the question is that I really want to solve something like this:

select TransactionId, max(CreatedUtc)
from TxLog
group by TransactionId

There is an index on (TransactionId, MaxCreatedUtc).

Re-writing using a normalized source (dt) does not not change the plan.

select dt.TransactionId, MaxCreatedUtc
 from [Transaction] dt -- distinct transactions
 cross apply
   (
        select Max(CreatedUtc) as MaxCreatedUtc
          from TxLog tl
         where tl.TransactionId = dt.TransactionId
   ) ca

Running just the CA subquery as a Scalar UDF does show a plan of 1 seek.

select max(CreatedUtc) as MaxCreatedUtc
 from Pub.TransactionLog
 where TransactionID = @TxId;

Using that Scalar UDF in the original query seems to work but loses parallelism (known issue with UDFs):

select t.typeName,
       Pub.ufn_TransactionMaxCreatedUtc(t.TransactionId) as MaxCreatedUtc
  from Pub.[Transaction] t

Rewriting using an Inline TVF reverts it back to the scan based plan.

From answer/comment @ypercube:

select TransactionId, MaxCreatedUtc
 from Pub.[Transaction]  t
  cross apply
   (
        select top (1) CreatedUtc as MaxCreatedUtc
        from Pub.TransactionLog l
        where l.TransactionID = t.TransactionId
        order by CreatedUtc desc
   ) ca

Plan looks good.  No parallelism but pointless since so fast.  Will have to try this on a larger problem sometime.  Thanks.


texts: Do not replicate Delete operations AWS DMS

texts:
I am using AWS DMS to replicate ongoing changes with SQL Server as a source and target endpoint. The tasks are running and replicating data with low latency. However, I need the tasks configured not to replicate DELETE operations from the source database.

I did read the whole documentation of the tool and I wasn't able to find such a configuration option to exclude DELETE statements for replicating. There is only one place in the whole user guide where it states that if one is performing a Full Load plus CDC or a CDC-only task, it is recommended the migration to be paused and secondary indexes to be created that will support filtering for update and delete statements. But no more, no further explanation.

Any help or experience is highly appreciated, please.


texts:
I have a list of customer IDs in table newsletter_subscriber. I have an EAV table that contains a lot of information about these customers, table customer_entity_varchar.

For every entry in newsletter_subscriber, I'd like get a specific value from table customer_entity_varchar where the customer ID (named entity_id in this table) is the same as the customer_id in newsletter_subscriber, and the attribute ID is 1.

Here's what I've tried

SELECT newsletter_subscriber.customer_id, customer_entity_varchar.value
FROM newsletter_subscriber
JOIN customer_entity_varchar
    ON newsletter_subscriber.customer_id = customer_entity_varchar.entity_id
    AND customer_entity_varchar.attribute_id = 1

This does not work, and customer_entity_varchar.value contains a wrong value, one that does not have an attribute ID of 1.


texts: How to increase idle time of "LaunchPad" for SQL Server external scripts?

texts:
sp_execute_external_script can take a while to initialize when not run for a short time.  How do I set the idle time on the controller Launchpad process to keep it from shutting down too soon?

Note I am not talking about reusing the same Python session as I know each call uses its own session. What I am referring to is that when you execute sp_execute_external_script for a language it takes a while to start up. If you call sp_execute_external_script again in a short time then it runs immediately.  However, if you wait a while and execute it again it takes a while to execute as (I think) the Launchpad controller has reached an idle time and shutdown.  See this article about SQL Server using the Launchpad process to created links to Python, R, and soon Java.

Is there a way to increase this idle time?


texts: Transformation of row data based on a grouping of rows preceding it

texts:
The basis for this question is: Transformation of row data based on preceding row for special cumulative sum which I had asked recently.

I will now raise the level of complexity a bit.

First, I will quickly reiterate over the make up of the data.

This is the initial data:

id      creation                    operation       value       running sum
SyJw-c  2016-09-01 00:11:08.307419  positive_op_1   1.33        28.82
SyJw-c  2016-08-21 08:32:54.431662  negative_op_1   -1          27.49
SyJw-c  2016-08-18 07:38:33.878365  positive_op_2   1           28.49
SyJw-c  2016-08-14 18:12:03.599797  negative_op_1   -1          27.49
SyJw-c  2016-08-02 15:44:29.693303  positive_op_1   1.33        28.49
SyJw-c  2016-07-31 12:08:50.659905  override_op_1   4.66        27.16
SyJw-c  2016-06-26 06:53:54.537603  negative_op_1   -3.5        22.5
SyJw-c  2016-05-31 13:34:08.005687  negative_op_1   -1          26
SyJw-c  2016-05-31 13:34:04.776970  negative_op_1   -1          27
SyJw-c  2016-05-31 11:27:09.502983  override_op_2   28          28

As clarified by @Erwin Brandstetter

This is the definition of the table

CREATE TABLE tbl (  -- no PK
  id text NOT NULL
, creation timestamp UNIQUE NOT NULL
, operation text NOT NULL
, value numeric NOT NULL
, running_sum numeric  -- optional (not needed for task)
);

This is the transformed data:

id      creation    oper      transformed_op   value   transformed_value       running sum
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.98
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_2                    1       1                       10.65
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.65
SyJw-c  2016- ...  ovr_op_1   new_rel_op_1     4.66    (4.66-22.5) = -17.84    4.66
SyJw-c  2016- ...  neg_op_1                    -3.5    -3.5                    22.5
SyJw-c  2016- ...  neg_op_1                    -1      -1                      26
SyJw-c  2016- ...  neg_op_1                    -1      -1                      27
SyJw-c  2016- ...  ovr_op_2   new_rel_op_2     28      (28-0) = 28             28

I run on postgresql 9.5

I wish to be able to calculate the running_sum and the transformed_value.

In contrast to the question asked and answered above, the predicate for calculating the transformation is not based solely on the row previous (here the table, as in the other question, is ordered by creation DESC).
The logic is as follows:

If the operation is an override_op_2 then the running_sum gets the value of the value column and the transformed_value is the value of the value column subtracted by the value of the running_sum of the previous row` (Nothing different than the original question)
If the operation is an override_op_1 then the logic follows that of the override_op_2 unless an override_op_2 had previously been encountered (for the id grouping), in this case it should be disregarded (or more explicitly the transformed_value is 0)

What seems more difficult here than in the original question is that the calculation is not based directly on the row preceding it but on a number of rows which is unknown in advance.

While I guess you could do that within a function (that stores a perviously encountered override_op_2 value (or NULL)), I wonder of it could be done in simple sql.


texts: What are the implications of using quite large partitions (rows) in Cassandra?

texts:
I am looking to store data in Cassandra, due to the fact that I have many writes that need to perform well, and all my reads can be done based on primary key. While doing the data modeling, however, I have come across a question.

I am planning to store (potentially) large chunks of text in my rows, and for some rows, there will potentially be several (or in extreme cases, as many as hundreds) of these columns in a single row. In the most extreme of these cases, it is possible that a row could comprise up to hundreds of MB of (uncompressed) text.

I am wondering what the performance implications of this arrangement are, and whether I would be better off changing my data modeling or going with a completely different storage strategy. I understand (from [1]) that an SSTable, once written to disk, will contain whole partitions, but I'm also wondering whether the same is true for the commit log; would adding a column to the row result in the entire row being written to the commit log?

[1] https://docs.datastax.com/en/cassandra/2.2/cassandra/dml/dmlHowDataWritten.html


texts:
I was looking at SQL Server performance and noticed that CPU utilization goes high when the number of connections go high(I believe it is normal).Usually number of connections  on an average was approximately 120 but from past 1 week the number of connections on an average goes to 200+ and sometimes even peaks to 400+. This is making the CPU utilization go high and in turn memory pressure starts occurring subsequently resulting in plan cache flush.(I might have to turn on LPIM as a long term solution)

This is causing performance issue. So I checked open sessions for the database and below is result:

Those 70 connections/sessions are from actual users accessing the application over network. Few connections are from SSRS, last 4 set of connections are of remote desktop connections from developers/DBAs.

I have no idea as what those 120 connections are, these connections don't even have LoginName.

Captured more details from sys.dm_exec_session.
Seems, those sessions without a loginname doesn't do anything. It uses 0 cpu time,0 logical reads etc.

I am not sure where else I should look at.

Result from sys.syslogins for the sid.
The login is the same login from actual users.

The 70 connections are using the generic sql login for the .Net application.
Other 120 connections with no login name has the same SID.

Does it happen when the user logs into application and switches to another module and afterwards comes back to the same module and a duplicate session is created and leaving the actual session open?

Here is the result of

  SELECT con.*, '--' AS [---], ses.*, '--' AS [---], req.*
  FROM sys.dm_exec_connections con
  INNER JOIN sys.dm_exec_sessions ses ON con.[session_id] = ses.[session_id]
  LEFT JOIN sys.dm_exec_requests req ON req.[session_id] = con.[session_id] WHERE ses.[login_name] = N''

Finally found out what those sessions without a login name is going.
They are running the functions which gets called form the main queries/stored procedures.

Here is the query i used.

SELECT
  con.connection_id,ses.status,ses.host_name,ses.host_process_id,ses.program_name,ses.last_request_start_time,ses.last_request_end_time,req.status,req.command,
  Substring(st.TEXT,(req.statement_start_offset / 2) + 1, ((CASE req.statement_end_offset WHEN -1 THEN Datalength(st.TEXT) ELSE req.statement_end_offset END - req.statement_start_offset) / 2) + 1) AS statement_text

  FROM sys.dm_exec_connections con
  INNER JOIN sys.dm_exec_sessions ses ON con.[session_id] = ses.[session_id]
  LEFT JOIN sys.dm_exec_requests req ON req.[session_id] = con.[session_id]
  CROSS APPLY sys.Dm_exec_sql_text(req.sql_handle) AS st
  WHERE ses.[login_name] = N''

Those sessions runs only functions.
Not sure why this is happening. Is that a normal behavior?


texts:
SELECT person.firstnames,
       person.surname,
       crm_question.label,
       crm_answer.value
       FROM crm_interaction
    JOIN person ON person.id = crm_interaction.person_id
    JOIN crm_answer ON crm_answer.interaction_id = crm_interaction.id
    JOIN crm_question ON crm_question.id = crm_answer.question_id

my output looks like this

firstnames | surname | label | value  |
|----------|------   |-------|-----   |
|     name | surname |label 1| value 1|
|     name | surname |label 2| value 2|
|     name | surname |label 3| value 3|

But I need it to look like this

firstnames | surname | label 1 | label 2 | label 3 |
|----------|------   |-------   |-----    |
|     name | surname |value 1   | value 2 | value 3 |
|     name | surname |value 1   | value 2 | value 3 |
|     name | surname |value 1   | value 2 | value 3 |

I created a quick SQL fiddle to build my Schema here:

http://sqlfiddle.com/#!9/18e20/1

I have tried to impliment a procedure into my code but keep getting errors that the syntax is incorrect I have never used MYSQLand this is greek to me.

Is it possible to pivot the original statement using a procedure to output the data in this way?

I know that tables can join and inner join but how can one go about changing the way that the data is displayed and connected?


texts: In having a debate with a coworker it was posed that a flat file, in this case a CSV, could be considered a database. This would include XML and JSON as well. I'm interested in differing views on this.


texts:
I came across this term reading my lecture notes; I can't find it on the internet.

My notes say the following:

  Given a schema R(A,B) and a query of the form "A=a", or "B=b", or
  "(A=a AND B=b)". Two indices over "A" and "A,B" provide a
  cover-to-cover indexing of the schema.

  If the schema contains N attributes, then the binomial (N, N/2) is the
  number of indices necessary to provide a cover-to-cover indexing.

Can someone shed some light on this topic? What is cover-to-cover indexing?


texts:
I use this code to create a user:

CREATE USER "${domain}"@"localhost" IDENTIFIED BY "${dbuserp}";
CREATE DATABASE ${domain};
GRANT ALL PRIVILEGES ON ${domain}.* TO "${domain}"@"localhost";

But what if a user with that name already exists and I want to replace the existing one, with the new "blankslate" one? I thought of coping with that in the following way, but I'm not sure it's the right way:

DROP USER IF EXISTS "${domain}"@"localhost";
DROP database IF EXISTS ${domain};
CREATE USER "${domain}"@"localhost" IDENTIFIED BY "${dbuserp}";
CREATE DATABASE ${domain};
GRANT ALL PRIVILEGES ON ${domain}.* TO "${domain}"@"localhost";


texts:
I'm revising our customer database from a one-to-one relation to a one-to-many. Specificaly we are introducing a new billing address and a shipping address. When researching I came across this post Storing a Billing Address Best Practice in Orders Table and this post Is there common street addresses database design for all addresses of the world?

Thinking about this I have some questions. I'm seeking answers so to implement the data model as provided by both answers, but currently I have doubts about implementing it this way, even though I understand from a technical point of view this is the way to do it. At the moment my main question is about the relation between the Address and CustomerAddress table.

We will have customers enter there own address. If they make a mistake and not receive anything it's their bad. This means two people can live at the same physical address, but have a slightly different printed address. One customer could for example abbeviate 'boulevard', but another doesn't have to do that. We want to implement it this way so when someone changes his address the address for different people on the same physical address doesn't change as well. So there will be no standardized address table. (If I would want that and still not want the aforementioned scenario I would my self have to organize an administrative process to create standardized addresses. This is not going to happen.) Every address record will be unique to a customer. So I think there is a one-to-one relation between Address and CustomerAddress.

The one-to-many relationship for addresses is provided, as in both data models in the provided links, through multiple records in the CustomerAddress table. The CustomerAddress table provides different purposes for each address through either a bit (data model 1 ) or a type (data model 2). Changes to these can be recorded in the CustomerAddressHistory table.

Due to the one-to-one relation between Address and CustomerAddress I thought about merging Address and CustomerAddress, but that would mean more storage when changing only a 'purpose' bit or type in the CustomerAddressHistory table.

So the main question is what would be the scenario that is the main objection to not implement a one-to-one relation between Address and CustomerAddress?


texts: Pyspark error inserting into mysql database table that exists

texts:
I am trying to insert into an existing mysql table using Pyspark JDBC connection however I get the following error(picture attached)
Can I get assistance on this error. The table exist in the MySql Database, I was successful in Inserting with a Dataframe when selecting data form another table , however i would like to Insert directly to the Mysql datadase table.

Below is the Pyspark code I am using to insert into the table:
import os
from pyspark.sql import SQLContext
from pyspark import SparkContext
from pyspark.sql import SparkSession
import logging

sc = SparkSession.builder.appName("TestConnection").enableHiveSupport().getOrCreate()
sqlContext = SQLContext(sc)

Provide your Spark-master node below
hostname = "localhost"
dbname = "dev_db"
username = "******"
password = "*******"
jdbc_url = "jdbc:mysql://{0}/{1}?user={2}&password={3}".format(hostname,dbname,username,password)

 sqlContext.sql("insert into test values ('3','Names',123654)").write.format('jdbc').options(url=jdbc_url,driver='com.mysql.jdbc.Driver').mode(append).save()
logging.info("***********Write to MySql Completed***********")


texts:
I am new to MySQL and I'm having problems creating a BEFORE INSERT trigger.

I get an unexpected END error.

I have a table named verlof_aanvragen with a column datum (and 6 more columns).

What I want to accomplish is only rows with a date between now()+8 and now()+365 or  CURDATE() + INTERVAL 8 DAY AND CURDATE() + INTERVAL 365 DAY)
can be inserted. An insert with a date outside that interval should fail.

I am using MySQL version 5.5.24.

This is the code:

CREATE TRIGGER chk_dates
BEFORE INSERT ON verlof_aanvragen
FOR EACH ROW
BEGIN
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY)
THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message'

END IF
END


texts:
I need to query duplicates in a table from 2 columns (agents_name) and (estates_name) based off the (display_name) column.

I tried the following:

SELECT id, display_name, author_table.agents_name, author_table.estates_name, agents_name, estates_name
FROM author_table

INNER JOIN(
SELECT agents_name and estates_name
FROM author_table
GROUP BY agents_name and estates_name
HAVING COUNT(agents_name) >1 and COUNT(estates_name)>1
)temp ON author_table.agents_name and author_table.estates_name = temp.estates_name;

The last 'sentence' should create a temporary table but I'm getting Error Code: 1054, unknown column temp.estates_name


texts: joining tables with full outer join and... COALESCE?

texts:
I have a few tables in an Oracle database linked by id - fk_id, if I do:

select RAP.ID_PCT_LUCRU,SES_RAP.AN,
        TIS.CANT as c1,'R12' as modc1,
        TIT.CANT as c2,'D13' as modc2,
        TIMB.CANT as c3,'D8' as modc3,
        TIFC.CANT as c4,
        TIA.CANT as c5
     from ch_trat cht
        full outer join raportor rap on cht.fk_id_raportor = rap.id
        inner join sr ses_rap on ses_rap.id = CHT.FK_ID_SR
        full outer join sortare tis on TIS.FK_TRAT_ID = CHT.ID
        full outer join transfer tit on TIT.FK_TRAT_ID = CHT.ID
        full outer join mec_bio timb on TIMB.FK_TRAT_ID = CHT.ID
        full outer join fiz_chm tifc on TIFC.FK_TRAT_ID = CHT.ID
        full outer join altele tia on TIA.FK_TRAT_ID = CHT.ID

and the result is something like this:

ID_PCT_LUCRU    AN     c1    modc1  c2   modc2  c3   modc3  c4  c5
275382          2013         R12         D13         D8
280933          2013         R12    222  D13         D8         44
295740          2013         R12         D13         D8    111  73
297089          2013         R12         D13    333  D8         124
281137          2013         R12         D13         D8    222  19
281137          2013         R12    222  D13         D8         123
281137          2013         R12         D13         D8         14
301410          2013   5103  R12         D13    444  D8         880

So I'd want that the result doesn't show me the first row with no value on c1,c2,c3,c4,c5. I know I should use COALESCE but I don't know how to use it in this case.
Many Thanks!


texts:
Is there a way to revoke access for ANALYZE TABLE which is granted by needed Insert privilege?
I have an application that throw multiple ANALYZE TABLE queries per second. And am looking for a way to decline these queries from the DB side.
Update
It can't be blocked from the app side, since tracing which part of the code is doing so will cost days searching between millions line of code.


texts: Can `listen_addresses` system configuration setting in Postgres stop pre-authentication exploits?

texts:
You can set various configuration parameters for Postgres by either editing the postgresql.conf file manually or by calling ALTER SYSTEM commands.

One of those config settings is listen_addresses. To quote the documentation:

  Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. The entry 0.0.0.0 allows listening for all IPv4 addresses and :: allows listening for all IPv6 addresses. If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it. The default value is localhost, which allows only local TCP/IP “loopback” connections to be made. While client authentication (Chapter 20) allows fine-grained control over who can access the server, listen_addresses controls which interfaces accept connection attempts, which can help prevent repeated malicious connection requests on insecure network interfaces. This parameter can only be set at server start.

➥ Does this mean listen_addresses theoretically stops pre-authentication exploits?

Is there any engagement between the Postgres server and the incoming connection which might possibly result in an exploit? Or will the forbidden incoming connection be blocked without any engagement?

Of course, ideally, a firewall on the host operating system would also be in place to block unwanted incoming connections. But let’s ignore firewalls for the purpose of this Question.


texts: Does the Query Optimizer have the right to create Temporary (Materialized) Views if it enhances performance?

texts:
Does the Query Optimizer have the right to create Temporary (Materialized) Views which can enhance performance? Sorry if this question seem very trivial.

Or in other words, does the query optimizer consider plans which involve creating a temporary table, storing the object on disk and then using it for query execution.


texts: How to represent a many to many relationship with Crow Feet diagram

texts:
I'm designing a database and I want to represent the relationship between users and groups:

A user can be on zero or more groups.
A group can have one or more users.

Using Microsoft Visio 2013 I've drawn this diagram:

Is correct?


texts: How to find the columns and tables used in a stored procedure (SQL Server)

texts:
I want to find all Tables alongside the columns used in a special Stored Procedure?

Can anyone help me here?

By the code below I found the tables but I want columns too

SELECT DISTINCT
o.id ,
o.name AS 'Procedure_Name' ,
oo.name AS 'Table_Name' ,
d.depid FROM
sysdepends d ,
sysobjects o ,
sysobjects oo WHERE
o.id = d.id AND
oo.id = d.depid ORDER BY
o.name ,
oo.name;


texts: Cannot see Green Button with white arrow in object explorer; DTC config problems?

texts:
I have SQL Servers across locations setup for replication. One of the SQL Server instances that I installed, running locally I can see the green button with the white arrow in the object explorer when I connect to it. However, when I connect to it from any other location, I cannot see that or neither can I start or Stop the SQL Serer Agent; even though locally I am able to do that.

Additionally, the MDTC doesn't has only 2 items sent both with were rejected, where as other servers have 100's committed and sent. Is there something wrong with the DTC settings?

Please help.


texts:
I'm trying to design a schema for a user having multiple accounts.

Example:

User ID 1 has Apps A and B
User ID 2 has App B only

More App's will be created, i.e. C, D, etc., in the future.

My first thought was to, then, create a schema of:

create table account (
  primary key user_id,
  string app_a,
  string app_b
);

However, what I don't like about this approach is that my app_* columns will have NULL marks.

So, then, I thought it'd be worthwhile to do:

create table users (
    primary key user_id
);
-- note: I'm not sure if it's necessary to include the 'primary key id'
create table app_a (
    primary key id,
    foreign key primary key user_id
);

create table app_b (
    primary key id,
    foreign key primary key user_id
);

What I like above approach is that I'm no longer dealing with NULLable columns. In addition, to create a new app, I simply need to create a new table; rather than adding another NULLable column.

Yet, it seems that, in the second approach, I'm simply treating the tables user, app_a and app_b as lists.

What's the best approach (including any I've not mentioned) to capture a relationship from 1 user to 0 to N apps?


texts: Installing an older CU over a Security Patch?

texts:
According to this Microsoft link, SQL Server 12.0.6372.1 requires any system with at least SQL 2014 SP3, so my predecessor installed it over SP3.  Now the version is "2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1".
Since that security patch only fixes a specific problem with Reporting Services, and is smaller than the CU4 package, ISTM that I should also install the CU4 patch.
But how?  Do I first uninstall KB4535288, then install KB4500181 (CU4) and then reinstall KB4535288, or can I just slap KB4500181 on top of KB4535288 (which seems like it would overwrite the KB4535288 files.


texts: Define log shipping file locations when using AWS for the secondary server

texts:
We are in the process of using an instance within AWS to act as our secondary server.  This instance will act as the secondary server in our log shipping process.

How would the "drop" (written by primary) and "pickup" (read by secondary) locations be defined if they are within one of the AWS S3 buckets?  Or is there some other place to put them instead of the buckets?


texts:
We recently started getting the following error while executing a stored procedure:

System.Data.SqlClient.SqlException (0x80131904): **Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation**.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal

When checking online and observing stored procedure we found that in the stored procedure developer was creating #Temp tables and that the issue could be resolved by creating the #Temp table with correct database default collation.

We would like to know how this collation got changed as the stored procedure was working properly earlier and we received this sort of error for the first time. We continuously keep getting windows/security patch updates for the servers; could these updates modify the collation property of the database?

CREATE table  #temp( Code varchar(5) COLLATE DATABASE_DEFAULT null, Amount decimal(11,2) null )
INSERT into #temp select PT.Code,(PT.Percentage/100*(@PAmount+@ICharge+@FCharges+(select CC from @temp where [IN] = @MyVar))) from PTax PT inner join tax T on PT.Code=T.Code where Code = @strCode and PId=@PId AND T.Flag =@CFlag
INSERT INTO #temp SELECT T.Code,0 from Tax T where Flag =@CFlag AND T.Code not in (select Code from #temp)
drop table #tempTaxes

@Solomon Rutzky Output to 1st Query:

0   <Instance>      SQL_Latin1_General_CP1_CI_AS
1   master          SQL_Latin1_General_CP1_CI_AS
2   tempdb          SQL_Latin1_General_CP1_CI_AS
3   model           SQL_Latin1_General_CP1_CI_AS
4   msdb            SQL_Latin1_General_CP1_CI_AS
6   CustomerDB      Latin1_General_CI_AI

2nd Query output:

Latin1_General_CI_AI

3rd Query Output:

Tax 2017-09-15 01:46:39.217 2017-09-15 01:46:39.217

Any suggestions are welcome.


texts: Migration from Postgres to MySQL using MySQL Workbench (Error in Reverse Engineering catalog. A task has failed while execution)

texts:
When I am trying to migrate database from Postgres to Mysql, I am facing an error in the Reverse engineering source step in which the log shows
Starting...
Connect to source DBMS...
- Connecting...
Connect to source DBMS done
Reverse engineer selected schemas....
Reverse engineering acl, audit, crm, doc, dunning, finance, mediation, mst, policy, public, railwire, reports, srv, unifyepg from railtel
- Reverse engineering catalog information
Traceback (most recent call last):
  File "/usr/lib/mysql-workbench/modules/db_postgresql_re_grt.py", line 350, in reverseEngineer
    return PostgresqlReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context)
  File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 237, in reverseEngineer
    catalog = cls.reverseEngineerCatalog(connection, catalog_name)
  File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 397, in reverseEngineerCatalog
    cls.reverseEngineerSequences(connection, schema)
  File "/usr/lib/mysql-workbench/modules/db_postgresql_re_grt.py", line 76, in reverseEngineerSequences
    min_value, max_value, start_value, increment_by, last_value, is_cycled, ncache = cls.execute_query(connection, seq_details_query % (schema.name, seq_name)).fetchone()
  File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 76, in execute_query
    return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs)
pyodbc.ProgrammingError: ('42703', '[42703] ERROR: column "min_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')

Traceback (most recent call last):
  File "/usr/share/mysql-workbench/libraries/workbench/wizard_progress_page_widget.py", line 192, in thread_work
    self.func()
  File "/usr/lib/mysql-workbench/modules/migration_schema_selection.py", line 175, in task_reveng
    self.main.plan.migrationSource.reverseEngineer()
  File "/usr/lib/mysql-workbench/modules/migration.py", line 369, in reverseEngineer
    self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection, self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData)
SystemError: ProgrammingError("('42703', '[42703] ERROR: column "min_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
ERROR: Reverse engineer selected schemas: ProgrammingError("('42703', '[42703] ERROR: column "min_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
Failed

Same error can be seen when i migrate the complete Postgres database or only a single schema. So what would be the reason for it?