texts: postgres database user grants to query database owned by other user

texts:
The postgres learning curve is very steep for me I need help. (I come from a mysql background and have the bad habit of trying to translate my mysql knowledge to pg)

here is the premise:

I want to import a sql dump of a pg database into a new (clean) instance.
I want to have 2 users:

dba which will own the database and be able to do DDL.
app will be only able to SELECT, INSERT, DELETE and that sort of things.

With the research I did this is what I tought should work:

I psql -U postgres and do the following

CREATE ROLE "app" WITH ENCRYPTED PASSWORD '${APP_PASSWORD}' LOGIN;
CREATE ROLE "dba" WITH ENCRYPTED PASSWORD '${DBA_PASSWORD}' LOGIN;

REVOKE ALL ON SCHEMA public FROM PUBLIC;

GRANT ALL ON SCHEMA public TO "dba";

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON ALL TABLES IN SCHEMA public TO "app";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "app";
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO "app";

ALTER DEFAULT PRIVILEGES FOR USER "dba" IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON TABLES TO "app";
ALTER DEFAULT PRIVILEGES FOR USER "dba" IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO "app";
ALTER DEFAULT PRIVILEGES FOR USER "dba" IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO "app";

Then I login with dba and do

CREATE DATABASE "mydb";

Then I import my sql dump with psql

psql -U dba -c mydb < dump.sql

Then I see my data and tables with the user dba.

With the user app tho, I can connect with psql to mydb but when I do \dt I don't see my tables and a select returns:

mydb=> select * from mytable limit 10;
ERROR:  relation "mytable" does not exist


texts:
How do I insert values with leading zeros in it?

Example.

db2 "insert into db2inst1.asd_currency
(currency_code, currency_name, currency_decimal_count, currency_code_desc)
values ('007', 'afghani', 2, 'afa'), ('009','lek', 2, 'all')"

Its output would give me something like below

db2 "select * from fcm_currency"

CURRENCY_CODE CURRENCY_NAME        CURRENCY_DECIMAL_COUNT CURRENCY_CODE_DESC
------------- -------------------- ---------------------- ------------------
            4 afghani                                   2 afa
            8 lek                                       2 all
            7 afghani                                   2 afa
            9 lek                                       2 all

  4 record(s) selected.

However, I need to retain the leading zeros because it's a code and I'll be needing that for my application.

EXPECTED OUTPUT:

CURRENCY_CODE CURRENCY_NAME        CURRENCY_DECIMAL_COUNT CURRENCY_CODE_DESC
------------- -------------------- ---------------------- ------------------
          004 afghani                                   2 afa
          008 lek                                       2 all
          007 afghani                                   2 afa
          009 lek                                       2 all

  4 record(s) selected.


texts: Stock price database optimization in MySQL

texts:
I am downloading a bulk of stock data:

Ticker symbol, company name, industry sector,
Open, high, low, close

I was wondering performance-wise if separating the ticker, company name, industry sector
from the price data would be optimal, as the tickersymbol and companyname just repeat over and over again.
Or would inner joining the ticker+price every time be slower?
So:

single table (ticker/price), or
two tables (ticker)(price)?


texts: Tuning PostgreSQL for minimum reliability and bulk updates

texts:
I'm using PostgreSQL to host some data that comes from external source, the normal operation of the database is read-only with periodic updates. Updates though are kinda huge e.g. in a 50 million row table 10 million rows will be updated and 1 million inserted. Inserting via COPY is fast enough but updating takes a very long time. Since the database can be easily recreated what could be done to increase update performance (the query is basically UPDATE items SET name = items_import.name FROM items_import WHERE items.id = items_import.id) by reducing reliability?

Query plan, nothing unusual here (both tables have primary key)

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Update on items_import_full i  (cost=487140.65..5258198.17 rows=8124429 width=210)
   ->  Hash Join  (cost=487140.65..5258198.17 rows=8124429 width=210)
         Hash Cond: (i.id = ii.id)
         ->  Seq Scan on items_import_full i  (cost=0.00..1753813.88 rows=45392988 width=178)
         ->  Hash  (cost=322112.29..322112.29 rows=8124429 width=36)
               ->  Seq Scan on items_import_inc ii  (cost=0.00..322112.29 rows=8124429 width=36)


texts: Storing data in PostgreSQL: One table or two?

texts:
I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month.

Question: Because every product can have different prices over time, I am thinking of storing all the data in a single table, each row having full details of the product, its price at that time, and the time.

id  | name           | color  | manufacturer | date      | price
123   Socks 123        black    nikki          12-12-2012   123.70
124   Socks 123        black    nikki          12-01-2013   145.67
125   Graphic Tee xXx  red      departed       13-01-2013   250.80

My reason for storing everything in one table and have duplicate data in certain columns like name, color, manufacturer is to facilitate doing queries like fetching all products' prices in a particular month (can return 10k rows) will avoid costly table joins if I have a table with the product details and a foreign key in another table with just the time-cost data.

Is this a good reason for doing what I've described? Will using hstore make it easier to do such queries? Or did I get it all wrong?

Thanks!!


texts: Implications of a second index on a timestamp field in a sequential table

texts:
I have a MySQL table of a few gigabytes with roughly ~100M rows. I store data sequentially, such that the timestamps increase as the ID also increases. Because ordering and filtering by the timestamp is so tremendously slow, I often simply use the ID to filter date ranges. If I know that Wednesday's data started at ID=87000000 and that Thursday's data started at ID=90000000, I can find all of Wednesday's data by filtering between those two IDs.

Everything works fast enough, except for the part where I need to find the ID range. This part requires filtering by the timestamp, and that is the slow part.

My question is this: If I add a second index on the timestamp field, what would be the implications? Would my insert rate be severely impacted? Would the size of my table increase dramatically? Would this improve performance enough to justify the action? If I were to add this index, would I even need to use the ID range trick anymore?

I've also been toying around with the idea of creating a function that performs a binary search on the table to find an ID near a target time. My assumption is that MySQL scans and compares the most of the table contents with the target time. This means, on average, 50000000 rows scanned. A binary search would take, at worst, Log2(100000000)=~27 queries for a single ID.

Any comments would be appreciated. I'll get to work on my binary search algorithm and post the results.

EDIT: My schema is:

CREATE TABLE `tracker_snapshot` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tracker_id` int(11) NOT NULL,
  `time` datetime NOT NULL,
  `value1` decimal(13,4) NOT NULL,
  `value2` decimal(13,4) NOT NULL,
  `value3` decimal(13,4) NOT NULL,
  `value4` decimal(13,4) NOT NULL,
  `value5` decimal(13,4) NOT NULL,
  `value6` decimal(13,4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tracker_snapshot_c9837659` (`tracker_id`),
  CONSTRAINT `tracker_id_refs_id_68c52750` FOREIGN KEY (`tracker_id`) REFERENCES `tracker_tracker` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=77107026 DEFAULT CHARSET=utf8


texts: What is the difference between a field and a column?

texts:
Although I have always regarded the terms as interchangeable, I have read somewhere that they’re not the same. I suppose this means that a record is not exactly the same as a row either.

I teach that the main difference is how you visualise the data — it is a column if you think of a table, but a field if you think of a form.

What is the real difference between them, and to what extent does it matter?

Thanks


texts: Is there a way to delete duplicate from a cell value (value of a given row and column) using Postgresql

texts:
In my Postgres db, I have a table with some columns where one column B has values as follows:

A      B
a1     b0, b1, b1
a2     b2, b3
a3     b4, b4, b5, b5

My expected output should be like this:

A      B
a1     b0, b1
a2     b2, b3
a3     b4, b5

Is there a simple UPDATE postgresql to do this task?


texts: Multi-Tenant (SaaS) Database Schema with Sharing

texts:
Guys I just need your opinion which is the best approach to design a schema of a database for a SaaS application. I want it all on a single database,

At first I think I can simply put a context_id to all the tables and table values. This context_id is the id of the SaaS subscriber. And all my queries will be including a where clause to the context_id.

However I also want it flexible which I can provide sharing between subscribers. Say my application is a clinic management system. One practitioner is subscribed then he/she is actually collaborating to another subscriber in which they are sharing their records.

So bottomline is what can be the best design with the schema? Thank you so much


texts: Does calling a stored procedure implicitly start a new transaction?

texts: If I'm in a transaction, will calling a stored procedure implicitly commit the current transaction and start a new one?


texts: SQL Server Replication with Temporal Tables

texts:
After a bit of advise. I have an existing database that extensively uses Temporal tables (i.e. System versioned tables). We are looking at an environment where we have a near to real time replica of production for the development team. This requirement is to get the developers off production and still allow them to do their work.

I now find out that temporal tables are not supported with transnational replication. This is the first time I have had to set up replication so apologies if part of my question is naive.

Looking at a blog, it suggests that it might be possible by;

For the publisher for each temporal table
1. turn off System Versioning.
2. run exec sp_addarticle
3. run exec sp_articlecolumn for all columns except the SysStartTime and SysEndTime columns
4. Turn on System Versioning.

For the Subscriber
1. Build a schema for each temporal table excluding the SysStartTime and SysEndTime columns and removes the FOR PERIOD directive.

Question is - will this work? Or is there more to it or another way to achieve the same result?


texts: mysql selecting a distinct summed column while having

texts:
I have a one-to-many relationship between two tables, where I need to get distinct values out of a summed column b while limited by another summed field a

SELECT
    t1.a - sum(t2.a) as a,
    t1.b - sum(t2.b) as b
FROM
    t1 inner join t2 using(id)
GROUP BY
    t1.id
HAVING
    a > 0
ORDER BY
    b ASC
LIMIT
    15

Is there any other way then bagging the whole query in another SELECT disctint b (...) as t ?


texts:
I'm trying to setup a testbed for bigdata analytics (basically around 2 to 3 TB base, slight write [only feeding the db], but possibly read intensive queries for analytics purposes).

Since I'm somewhat experienced with vanilla PostgreSQL (but with 10GB base), I was considering trying the 9.1 new synchronous replication to achieve load balancing. The objective is to get Tableau be very responsive. Thus, I could avoid costly MPP GreenPlum, AsterData and others.

However, I was wondering if :
- going straight to MPP Databases, GreenPlum for example, was a good idea ? (for 2-3 TB, is Greenplum really needed?)
- once Synchronous Replication is up and running, how shall I configure my applications ? Is the load balancing done by the master or is another solution need (Pgpool for example) ?

Thank you for you insights !


texts: How can I force Teradata to create a table in memory?

texts:
Teradata has an "intelligent" in-memory engine that dynamically loads some data into memory based on usage.

How can I force it to create or store a table in memory all the time?


texts:
I'm wondering if anyone can find the mistake in my SQL-Query which I can't seem to find.. Been starring at the same lines of code for like 2 hours and it's driving me nuts!

So, here's the query... (Quite long but hopefully it's readable..) I'm working in VBA connected to the DB hence the ComboBox values and variables.

The error I get says

  Incorrect Syntax near RfD_

So, here it comes. Brace yourselves:

SQLString =
"SELECT
RfD_.RfD_ID
, RfD_.RfD_Version
, RfD_Status
, RfD_ProjectRef
, RfD_ProjectName
, RfD_StartDate
, RfD_FinishedDate, " & _

"RfD_ActivityName
, Deliverables_.Deliverables_ID
, Deliverables_.Deliverables_Deliverable
, Deliverables_.Deliverables_AcceptanceCriteria
, Deliverables_.Deliverables_Deadline
, Deliverables_.Deliverables_Delivered
FROM [RFD_New].[dbo].[RfD_] " & _

"Inner join [RFD_New].[dbo].[Deliverables_]
    on RfD_.RfD_ID = Deliverables_.RfD_ID" & _
    "And RfD_.RfD_Version = Deliverables_.RfD_Version
where RfD_.RfD_StartDate >= '" & DateFrom & "'
    and RfD_.RfD_FinishedDate <= '" & DateTo & "' " & _

    "And RfD_.RfD_ID in (
                        SELECT RfD_ID
                        From [RFD_New].[dbo].[Approval_]
                        Where Approval_ID = '" & 1 & "'
                            And (Approval_Name = '" & ComboBox2.value & "'
                            or Approval_Name = '" & TwistName(ComboBox2.value) & "') " & _

    "And RfD_.RfD_ID in (
                        SELECT RfD_ID
                        From [RFD_New].[dbo].[RfD_]
                        Where RfD_ProjectRef = '" & ComboBox1.value & "') "

Formatted it a bit to make it more readable but haven't changed anything.
Funny thing is that it works in Microsoft SQL Server Management Studio but not in VBA.


texts:
So here is my situation. I am trying to write a script which will (among other things) allow me to measure prime time block utilization. For reasons unknown to and unchangeable by me, various departments here consider "prime time" to be different on one day of the week. I've gotten around this by writing the following into SQL:

CASE WHEN DATENAME(DW, OD.CALENDAR_DT) = 'Tuesday'
     THEN CAST(OD.CALENDAR_DT + '08:00:00.000' AS DATETIME)
     ELSE CAST(OD.CALENDAR_DT + '07:00:00.000' AS DATETIME)
END AS PT_BLOCK_START

However, one department has their prime time block measured differently on Tuesdays, but not consistently. Prime Time is measured most days from 7:30-5, but on the 1st and 3rd Tuesday of a month it's 9 to 5 and on the 2nd and 4th Tuesday it is 8 to 5. I am trying to figure out how to write a statement similar to the above that accounts for which Tuesday of the month it is, but the little hamster than runs on the wheel in my skull seems to have keeled over dead.

Any thoughts will be greatly appreciated and will surely bring mounds of good karma, if you believe in that sort of thing.


texts: SQL Server - ensuring a complex relation on insert

texts:
I have some related tables:
Application - stores a list of applications
ApplicationInstall - for each application, multiple installations are present (I have a foreign key ApplicationUid)
SecurityPrincipal - for each ApplicationInstall I have many principals (I have a foreign key ApplicationInstallUid).
I have also a table SecurityPrincipalType, linked to Application through a 1-to-many.
I would like to have a rule that guarantees that when I insert a record in SecurityPrincipal its Type comes from a SecurityPrincipalType linked to the correct Application.
I didn't rename the tables because I usually prefer to have names to think about. But I think the following scenario is equivalent:
Table A n..1 Table B 1..n Table C
And I want to enforce that a column in C contains only values coming from A, but linked via B.
I'm considering:

triggers
foreign keys?
application level checks (but I would prefer to have some check at the db level)

What should I do?


texts: TSQL script to backup database using a driver table and stored procedure

texts: I am in need to fulfill a client request that wants me to create a backup solution that includes a stored procedure that will pick up the backup information from a driver table that will hold all the parameters needed such as backup file path, type of backup, no. of days to retain the backup etc and based on that information the stored procedure will initiate the backup. I spoke to the client regarding the Ola's script for backing up the database but as per them they want an in-house developed script and is adamant about that. Can someone please help. I don't have much experience writing T-SQL code. I have a fair idea about what would be the end result but I don't know about the implementation logic. Please do let me know if someone already has a script that does the same.


texts: Pausing the secondary Replicas for a day in always on availability group

texts:
We are planning to make the DR as primary and do maintenance on the secondary site(Formerly Primary Site)  and hence pausing both the secondaries in always on availability group for a day. I am aware that if both the secondaries are paused then there wont be any failover, but will there be any other problems of pausing the secondaries for a day ...Am i missing something ? Any help will be greatly appreciated !

Can i safely go to the Failover cluster manager and paused both the secondary nodes ?


texts: Automatic create SQL database and table relationship

texts:
For SQL Server 2008, what is the best way to automatically "draw up" the Database relation ship and table relationship too?

Is there any way to visualize database with this.


texts:
I have been reading up on innodb as a storage engine, mainly because I have recently moved to AWS and they do not recommend myISAM which my databases current run on (and have for about 10 years)

So moving to a new engine is a little bit of a scary operation.

I am apprehensive about the move, as it has been on myISAM for so long without issue, but if it gives me peace of mind long term, then it is better for the DB.

I have read the whitepapers on InnoDB and it seems fairly straight forward, The one thing that I want to clear in my mind is the innodb_flush_log_at_trx_commit option

By default this is set to 1, however from my reading this causes additional overheads, if the data was credit card transactions or something like that, I can understand its needs to be there, but it seems when dealing with non life changing data, that innodb_flush_log_at_trx_commit=2 is a better option.

What I want to know is, this does not affect the time that the query is actually committed does it? It only affects its recovery ?

I just want to make sure that when I do an insert or update that the query will run right at the time of processing and not 1 second later, no matter what the flush_log is set to.

My understanding is that in the case of a crash , setting to 1 will allow it to recover all queries run on the server, where as setting it to 2 may lose the last second or two of data when trying to recover from the crash, is this correct?

Also, if there is slow periods of updates/inserts (ie, not much happening on the server) does setting it to 2 add additional overheads on the server, or is it a case of the benefits of applying innodb_flush_log_at_trx_commit=2 to the database when the database is busy outweighs any additional overhead caused during slow times?


texts: Converting a looped INSERT.. ON CONFLICT to a wCTE in postgres

texts:
I have an events table:

       Column        |            Type             |   Modifiers
---------------------+-----------------------------+---------------
 ts                  | timestamp without time zone |
 event_type          | character varying           |
 id                  | bigint                      |
 dimension_1         | integer                     |
 dimension_2         | bigint                      |
 metric_1            | bigint                      |
 metric_2            | bigint                      |
 processed           | boolean                     |

And various dimension tables, with a schema like:

       Column        |            Type             |   Modifiers
---------------------+-----------------------------+---------------
 ts                  | timestamp without time zone |
 id                  | bigint                      |
 dimension_1         | integer                     |
 dimension_2         | bigint                      |
 metric_1            | bigint                      |
 metric_2            | bigint                      |

       Column        |            Type             |   Modifiers
---------------------+-----------------------------+---------------
 ts                  | timestamp without time zone |
 id                  | bigint                      |
 dimension_1         | integer                     |
 dimension_2         | bigint                      |
 metric_2            | bigint                      |

Where the metrics are aggregated in some way (either by timestamp+id+dimension, or by filters etc).

The inserting from the events table -> dimension tables is currently handled by a plsql function which just does:

LOCK events;

FOR item in SELECT * FROM events WHERE processed=false
    LOOP
        INSERT INTO
            dimension_table (...)
        VALUES
            (item.ts, item.id, item.dimension_1, item.metric_1, item.metric_2)
        ON CONFLICT
            (ts, id, dimension_1 /* or similar */)
        DO UPDATE
        SET
            metric_1 = greatest(EXCLUDED.metric_1, dimension_table.metric_1),
            metric_2 = dimension_table.metric_2 + EXCLUDED.metric_2;
    END LOOP;

UPDATE events SET processed=true;

I'm sure this could be done far more efficiently, and I was hoping to get some pointers as to how. Yes, the table can be locked during the insert-fanout process.

In terms of query volumes, the events table usually gets a peak of 400-500k writes/sec.


texts: Ola Hallengren's index rebuiuld script - how to exclude more than one object?

texts:
I'm using Ola's script to rebuild indexes. But after problems with script blocking particular objects that are used by SOLR, I wanted to exclude them and run script for them in another schedule. So, in main job I've set parameter @indexes like this:

@Indexes = 'ALL_INDEXES, -database.schema.object_1, -database.schema.object_2, -database.schema.object_3'

Is this a correct settings for this parameter? Can I add here multiple objects? Also, in second job I've set only this objects to be rebuild (without ALL_INDEXES).


texts: Where does [reflection.assembly] reside?

texts:
In a disconnected DEV box, I attempt to load the reflection assembly, "Microsoft.SqlServer.SMO". Then I try to call for a SMO.Server object, like this

[Reflection.assembly]::("Microsoft.SqlServer.SMO") | Out-Null

Powershell responds with this error message

  New-Object : Cannot find type {Microsoft.SqlServer.SMO.Server]: verify that the assembly containing this type is loaded.

So I get it. I need to find a copy of the assembly, download it to a USB device, and upload it to my disconnected DEV server.  Where do I find the assembly to download?


texts:
I have this example table:

Table1

+-------+----------------+-----------+------------+
| tkno  |      dete      |   shyft   |   Name     |
+-------+----------------+-----------+------------+
| 1251  |     13-04-2018 |    Shift1 |         Foo|
| 1252  |     13-04-2018 |    Shift1 |         Foo|
| 1253  |     14-04-2018 |    Shift2 |         Bar|
| 1254  |     14-04-2018 |    Shift2 |         Bar|
| 1255  |     15-04-2018 |    Shift3 |         Bar|
| 1256  |     15-04-2018 |    Shift3 |         Joe|
| 1257  |     15-04-2018 |    Shift3 |         Joe|
+-------+----------------+-----------+------------+
3 rows in set (0.09 sec)

When I use this query,

SELECT shyft,
(SELECT COUNT(tkno) FROM Table1 WHERE dete = DATE(now())) AS 'Todays Tickets',
(SELECT COUNT(tkno) FROM Table1 WHERE dete = DATE_SUB(DATE(now()), INTERVAL 1 DAY)) AS '1 Day Ago',
(SELECT COUNT(tkno) FROM Table1 WHERE dete = DATE_SUB(DATE(now()), INTERVAL 2 DAY)) AS '2 Days Ago'
FROM Table1
GROUP BY shyft;

I get results like this:

+-------+----------------+-----------+------------+
| shyft | Todays Tickets | 1 Day Ago | 2 Days Ago |
+-------+----------------+-----------+------------+
|Shift1 |             13 |        33 |         34 |
|Shift2 |             13 |        33 |         34 |
|Shift3 |             13 |        33 |         34 |
+-------+----------------+-----------+------------+
3 rows in set (0.09 sec)

I want to display the number of tickets in each shift for current day and last 2 days. For the above query, it is showing the same count on row 2 and 3. Please help me with where am I going wrong. Thanks!


texts: What event information can I get by default from SQL Server?

texts:
I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its own. For example:

Who last executed stored procedure dbo.MyProcedure?
Who updated the salary column in the dbo.Employees table?
Who last queried the dbo.Orders table from Management Studio?

But there are several other events that SQL Server does track temporarily by default, and can natively answer questions about, such as:

When was the last time an auto-grow happened in the AdventureWorks database, and how long did it take?
Who deleted the dbo.EmployeeAuditData table and when?
How many memory-related errors have happened today?

How do I get this information, and how long does it stay available?


texts: Not able to update one of the key value by using JSON_MODIFY function in SQL Server

texts:
I have the following json record and I'm trying to update the 'counter2' value to 2000 using JSON_MODIFY function. The query doesn't throw any exception but at the same time the value is not getting updated.

Table Data:

Json data:

[ {"counter1": 9,
"counter2": 1000,
"counter3": 109 }]

SQL:

UPDATE test01
set json_data = JSON_MODIFY(json_data,'$.counter2',2000)
where test01_id =10

Am I missing something here?


texts: How to avoid using Merge query when upserting multiple data using xml parameter?

texts:
I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL Server database. If the row already exists in the table, we update that row with the information in the given array. Else, we insert a new row in the table. I have basically described upsert.

Now, I am trying to achieve this in a stored procedure that takes an XML parameter. The reason I am using XML and not table-valued param is because, doing the latter, I will have to create custom type in SQL and associate this type with the stored procedure. If I ever changed something in my stored procedure or my db schema down the road, I would have to redo both the stored procedure and the custom type. I want to avoid this situation. Besides, the superiority that TVP has over XML is not useful for my situation because, my data array size will never exceed 1000. This means I cannot use the solution proposed here: How to insert multiple records using XML in SQL server 2008

Also, a similar discussion here (UPSERT - Is there a better alternative to MERGE or @@rowcount?) is different from what I am asking because, I am trying to upsert multiple rows to a table.

I was hoping that I would simply use the following set of queries to upsert the values from the xml. But this is not going to work. This approach is just supposed to work when the input is a single row.

begin tran
   update table with (serializable) set select * from xml_param
   where key = @key

   if @@rowcount = 0
   begin
      insert table (key, ...) values (@key,..)
   end
commit tran

Next alternative is to use an exhaustive IF EXISTS or one of its variations of the following form. But, I reject this on the ground of being of sub-optimal efficiency:

IF (SELECT COUNT ... ) > 0
    UPDATE
ELSE
    INSERT

The next option was using Merge statement as described here: http://www.databasejournal.com/features/mssql/using-the-merge-statement-to-perform-an-upsert.html. But, then I read about issues  with Merge query here: http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/.  For this reason, I am trying to avoid Merge.

So, now my question is: is there any other option or a better way to achieve multiple upsert using XML parameter in SQL Server 2008 stored procedure?

Please note that the data in the XML parameter may contain some records that should not be UPSERTed due to being older than the current record. There is a ModifiedDate field in both the XML and the destination table that needs to be compared in order to determine if the record should be updated or discarded.


texts: Should the referencing table have primary key, auto increment, or index?

texts:
I'm trying to implement a list. Each list item will be associated with one or more tags. And each tag will be associated with one or more list items.

I'm still pretty new to this field. Fortunately, I managed to find a solution in this question, whose answer suggests the following database design:

Articles table
- id
- name
- etc

Relational Tag Arts table
- Fkey tags id
- Fkey article id

Tags table
- id
- name
- etc

I already created my three tables. Now what I don't understand is that, should the referencing table (the one that contains two foreign keys) have primary key, auto increment, or index?

[Solved] And although I'm not 100% sure, I guess the referencing table's content is purposely "repetitive". For example, if I add a new item which is associated with 5 tags, then the referencing table should be added with 5 more records, which all have the same item id but have different tag id's. Am I correct?


texts: Postgres 10.5: Granting full schema access to a user that isn't the owner

texts:
So the underlying problem I'm having is trying to migrate my on premise Postgres 10.5 instance to RDS. RDS does NOT give end users the SuperUser role so I'm hitting tons of permissions issues when trying to import from a pg_dump.

For example, logged in as user postgres - this is the user created when I created the RDS instance. What amazon calls the "root user":

CREATE SCHEMA f_site;
GRANT ALL ON SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON DATABASE mydb to postgres;

Those all get set without error. Now, the owner changes:

ALTER SCHEMA f_site OWNER TO schemaadmin;

Great, however if I try to immediately run another grant, just to test my permissions, still logged in as postgres user, same session:

GRANT ALL PRIVILEGES ON SCHEMA f_site TO postgres;
ERROR:  permission denied for schema f_site

Basically I am trying to create a 'pseudo-superuser' by giving the postgres user full grants on everything it creates, then transfering ownership of that object to another role.

This is because the very first lines in my pgdump backup are essentially creating schema and setting schema owners, then later (thousands of lines later) making changes/additions to it. Normally with a superuser this wouldn't be a problem.

Thanks for any advice.


texts:
I have this neat little query which let me know any ongoing query that stuck running for more than 3 minutes.

SELECT
sqltext.TEXT,
s.session_id, r.status,
r.total_elapsed_time, r.cpu_time, r.wait_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE s.is_user_process = 1
AND s.session_id <> @@SPID AND r.total_elapsed_time > 300000000

I came across this article that show how to set up SQL agent alert and send as notification.  Can I combine with my query so it sends alert email when I have query running longer than 3 minutes?


texts: Add a new collation to a Postgres database

texts:
I have these collations:

postgres=# select * from pg_collation;
  collname  | collnamespace | collowner | collencoding | collcollate | collctype
------------+---------------+-----------+--------------+-------------+-----------
 default    |            11 |        10 |           -1 |             |
 C          |            11 |        10 |           -1 | C           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 en_US      |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 en_US.utf8 |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 ucs_basic  |            11 |        10 |            6 | C           | C
(6 rows)

I installed a new locale on my system and I'd like to use it on postgres. It seems that the only way to install a new collate is using the initdb command, but it requires to make a pg_dumpall, delete the postgres data directory, run initdb and restore all the data from the dump. Isn't there an easier solution?


texts: Missing options in Feature Selection when installing SQL Server 2016 on existing server

texts:
I have a Windows 2008R2 64-bits server that already has 2 instances of SQL Server 2008R2 installed, and working fine. I want to add a new instance, but for SQL Server 2016, while keeping the existing instances in 2008R2. When I get to the Feature Selection, there is no option to install the Database Engine, and all the rest. All I get is this screen:

I can't figure out why the "normal" feature selection doesn't appear, so that I can add a new instance. Any tips? Thanks


texts: SQL Server performance baseline benchmark

texts:
In new SQL Server environment, I am trying to setup an performance baseline for some of the counters. Let me explain what I am doing.

I captured the performance counters through the performance monitor and get the data (in CSV format) for around a month's time and use it to find the average which will give an baseline for monthly performance.

Counters collected:

PhysicalDisk (_Total)\Avg. Disk sec/Read,
PhysicalDisk (_Total)\Avg. Disk sec/Write,
Processor (_Total)\% Processor Time,
Memory\Available MBytes,
SQL Server:Access Methods\Page Splits/sec,
SQL Server:Buffer Manager\Buffer cache hit ratio,
SQL Server:Buffer Manager\Page life expectancy,
SQL Server:SQL Statistics\SQL Compilations/sec,
SQL Server:SQL Statistics\SQL Re-Compilations/sec.

Can someone explain what I am doing to capture the performance baseline is correct or wrong?


texts: What's the difference between a CTE and a Temp Table?

texts:
What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?

CTE

WITH cte (Column1, Column2, Column3)
AS
(
    SELECT Column1, Column2, Column3
    FROM SomeTable
)

SELECT * FROM cte

Temp Table

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable


texts: mySQL workbench Unable to access Performace Schema (sys)

texts:
I'm facing a problem on mySQL workbench (ubuntu 20.04). Apparently I get a message saying that is unable to access Performance Schema helper (sys). Can't open file '/etc/debian_version'.
enter image description here
I have tried this:
cat /etc/debian_version
bullseye/sid
I have also tried:
mysql> select * from user where user='root'\G;
ERROR 1046 (3D000): No database selected
ERROR:
No query specified
mysql> create user root@'localhost' identified by 'root';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
mysql> grant all on *.* to root;
ERROR 1410 (42000): You are not allowed to create a user with GRANT

Then I have changed the 'localhost' with '%' for the above commands it took me somewhere but no a positive result.
Not sure if these methods are related to my problem but please help me.
I have tried for hours to figure out what is wrong but can't manage it.


texts:
I just want to ask about how to optimize my query. I am curious about the efficiency of using join instead of where in sub query. Can someone explain the difference?

SELECT  C.Customer_id,
        C.tran_id,
        t.date
    FROM CUSTOMERS C
    INNER JOIN TRANSACTIONS T ON c.Customer_id = t.Customer_id
    WHERE C.Customer_id IN (
        SELECT Customer_id FROM TRANSACTIONS_DATE WHERE tran_Date <= '2013-01-01'
    )
    AND C.Customer_id NOT IN(1,2,3)


texts:
I need to perform a DB migration from Server A to Server B.

Server A: MySQL version installed 5.0.6
Server B: MySQL version installed 5.6.20
OS: Both are running RedHat Linux 64bit. 16 RAM memory.

There are MyISAM, InnoDB, Archive databases.

Total size about 4TB.
Largest MyISAM database = 3.2TB
Largest InnoDB database = 85GB

key_buffer_size=2GB.
I took a mysqldump on the largest InnoDB table (85GB)
 time /usr/local/mysql/bin/mysqldump -uroot -p --extended-insert --single-transaction ABCEx > ABCEx.sql
After running for 5 hours, the dumpfile has grown to 145GB and yet to finish.
-rw-rw-r--  1 mysql mysql 145019313099 Mar  2 05:12 ABCEx.sql
I am concern on the export time and also import. Any help to expedite this?
As for MyISAM tables migration, I read that I migrate them by coping the .myd .frm .myi files from Server A to B. Can provide some guide or link if any one has done before? We are talking of 3.2TB of MyISAM database.

Please help....much appreciated :)


texts: INSERT / UPDATE to merge data, when empty data is an empty string

texts:
Consider a table of some family members:

mysql> SELECT * FROM some_table;
+--------+--------+--------+
| f_name | l_name | work   |
+--------+--------+--------+
| Meirav | Cohen  | school |
| Dotan  | Cohen  |        |
+--------+--------+--------+
2 rows in set (0.00 sec)

Assume that f_name is the primary key, and that all columns are VARCHAR. Unknown data are not represented as NULL, but rather as an empty string (as can be seen in the work column).

I would like to add some data that I know about a person, but I would prefer to not query the database to see what is there but rather have the database merge the extant value with the new values:

mysql> INSERT INTO some_table (f_name, l_name, work)
    VALUES ('Dotan', '', 'php')
    ON DUPLICATE KEY UPDATE
        f_name = IFNULL(f_name, 'Dotan'),
        l_name = IFNULL(l_name, ''),
        work   = IFNULL(work,   'php');

Of course, IFNULL doesn't fit the job as it only checks for NULL, not for empty strings. Also, there is the disadvantage that each value to add must appear twice in the SQL.

Is there any way to have the database merge the data against empty strings? Also, can it be made such that the strings to insert appear only once in the SQL?


texts:
Trying to get the SQL for a single time zone results in an error message:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo America/Sao_Paulo
mysql_tzinfo_to_sql: Error reading file '/usr/share/zoneinfo' (Errcode: 21)
Problems with zoneinfo file 'America/Sao_Paulo'

What am I doing wrong? Wrong syntax?

Getting the SQL for all time zones works, except for some warnings, and São Paulo is included:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | grep Sao_Paulo
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('America/Sao_Paulo', @time_zone_id);
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('posix/America/Sao_Paulo', @time_zone_id);
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh89' as time zone. Skipping it.
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('right/America/Sao_Paulo', @time_zone_id);
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.


texts: Distinct counts from large table are too slow

texts:
I have a table with 1.2 billion rows. The table has 60-70 columns, the vast majority of which are empty.

The table is indexed on subscription_id, among other indexes.

My query is:

SELECT count(distinct listing_id)
FROM apps
WHERE subscription_id = 1298;

This particular subscription_id is going to have roughly 30M rows to count. Unfortunately I am unable to do so as the query takes in excess of one hour just to  get that count.

I know there must be some way to improve it. Would it be improved if I created an index on subscription_id and listing_id together?


texts: Maintenance in MYSQL when innodb_file_per_table disabled

texts:
I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace.

But what if I need to perform index maintenance on InnoDB tables with ANALYZE command, it will grow the single tablespace also?

What other alternatives are there to increase performance or doing some maintenance in Innodb engine, when using a single tablespace and without grow out of control the single ibdata1.

Regards.
Matthew


texts: Can't find sun.jdbc.odbc package in Java version 8

texts: I am trying to connect a database query to my class while I compile the package name sun.jdbc.odbc package but it is not found in Java version 8.


texts: EN Express, no tablespace selection under storage

texts: I have logged in to EM Express as Sys dba, need to create new tablespace, steps are to navigate to storage and select tablespace where it would navigate me to tablespace window view but however under storage I do not have tablespace selection. Does anyone can give me directions why and if I am able to configure and have tablespace access?


texts: Can I make postgres more verbosely tell me what it's doing?

texts:
I'm trying to use ALTER TABLE on a live db (not in production, just poking around at a dev box and trying to learn some things)

Here is the command I'm trying:

psql -h $IP -p 5432 -U $DBUSER -d $DBNAME \
    -c "alter table request alter column sources set data type text;"

And what happens is... nothing. postgres just sits there silently for many, many minutes, until i give up and cancel it:

^CCancel request sent
ERROR:  canceling statement due to user request

Some reading I've done suggested that this should be fast because it's not adding any onerous constraints like UNIQUE or foreign keys or anything.

There are less than 100 rows in the table so even if this was a heavy command to run, it should still finish relatively quickly. The existing type is varchar(1000), is it just that it's difficult and slow for pg to convert from varchar to text? I would think that would be a trivial conversion, essentially just taking off the 1000 char limit.

Is it possible that the table is somehow locked and my command is just paused, waiting for something else to finish? How would I check that? If so, how would I say "no pg, this one is the important one, lock out everything else".

Generally it'd just be nice if I could get pg to print out some debugging to say whether or not the long pause is due to heavy processing or just waiting around for a lock to free.

There are 29 rows in pg_locks and some of them do say ExclusiveLock but beyond that I'm not really sure how to interpret this information.

Postgresql version is 9.3.9-0ubuntu0.14.04 (that is, the latest version available for Ubuntu Trusty).

Here's the block report: http://paste.ubuntu.com/12243226/

Potentially relevant: my db consumer is flask/sqlalchemy, perhaps it is the one defining the select statements as blocking the table. The fact that 9 statements are blocking and they all say "where request_id = 1" is quite strange because request_id 1 was deleted some time ago. Is this some kind of dangling ghost query? How can I clear this out?

Here's pg_stat_activity: http://paste.ubuntu.com/12243295/


texts:
I'm running Aurora Postgres and noticed that metric Maximum Used Transaction IDs is increasing a lot. Trying to manually vacuum the tables that's was not automatically vacuumed lead me to the following errors:

sql> VACUUM sales.hist_status
[2019-01-06 15:22:44] [58P01] ERROR: could not access status of transaction 247756185
[2019-01-06 15:22:44] Detail: CLOG segment 29 does not exist: No such file or directory.

sql> VACUUM sales.hist_items
[2019-01-06 15:22:55] [XX001] ERROR: found xmin 235941673 from before relfrozenxid 256796328

Googling these erros points me to database corruption. What should I do?


texts:
I need to find out how much disk space a single row is taking up in a table.  I can then use this as an approximate to work out how much disk space I can recover by removing old unneeded data.  Would I have to combine all of the columns in a single SELECT statement, like so?

SELECT DATALENGTH([Id]) + DATALENGTH([Column1]) + DATALENGTH([Column2]) AS 'Size'
  FROM [MyTable]

Or is there a better, more efficient way that I'm not aware of?

EDIT

I have just noticed that if a column has a NULL value, then I get NULL as the result.


texts: Risk of changing clocktime on MySQL server host

texts:
I have a MySQL server host that have a clock that is about 75 minutes wrong. Is there any risk in changing the hosts clock while MySQL is running?

Update: Obviously NOW() and other functions returning current time will return a different result. In my case I am thinking of spinlocks and other scary internals in MySQL.


texts: Trigger table name as variable into stored procedure

texts:
I have several triggers that activate a procedure, I want to identify the table that uses the trigger and use it as a variable to a procedure.

Another option is to pass a variable (the name of the table) from a trigger to a procedure

Is it possible in mysql > 5.7?

I try not to create many procedures, I want a single procedure that creates me other tables from the trigger variable.

What reserved MySQL variables should I use?

Regards.


texts: Multiple columns from one table rely on one single table

texts:
I'm working with PostgreSQL 9.2 and have the following relation between 2 tables:

The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost always included in the same waste water and drinking water perimeters. But a few ones can be included in a waste water perimeter different from the drinking water one.

Table town:

t_code  | t_waste_water | t_drinking_water
--------+---------------+------------------
 001    | 01            | 01
 002    | 02            | 02
 003    | 01            | 03
 004    | 02            | 02
 005    | 01            | 01

Table perimeter:

perim_code | perim_name
-----------+------------
 01        | North
 02        | West
 03        | East

For instance, town 003is included in the North perimeter for waste water and to the East perimeter for drinking water stuff. Perimeters having the same names for waste water or drinking water concerns, I have put them in a single central table, instead of duplicating informations into one waste water table and one drinking water table.

Now, I would like to get perimeters' names together with town code in a single table, as bellow:

town    | perim_waste_water | perim_drinking_water
--------+-------------------+------------------
 001    | North             | North
 002    | West              | West
 003    | North             | East

My problem is that I don't see how to display more than one perimeter name for I can specify one relation (JOIN) between the two tables. The following query is not usefull but is a start...

SELECT
  a.t_code,
  a.t_waste_water,
  a.t_drinking_water,
  b.perim_name AS waste_water_perim,
  b.perim_name AS drinking_water_perim

FROM schema.town a
  LEFT JOIN schema.perimeter b ON a.t_waste_water = b.perim_code AND a.t_drinking_water = b.perim_code;

Is there a solution inside the query or do I have to structure data differently in the database?


texts:
Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly?

For example, suppose I have widgets which users can rate (see schema below).  Each time I display a widget I could calculate the average user rating from the Ratings table.  Alternatively I could store the average rating on the Widget table.  This would save me from having to calculate the rating every time I display the widget, but then I'd have to recalculate the average rating each time a user rated a widget.

Ratings       Widgets
---------     -------
widget_id     widget_id
user_id       name
rating        avg_rating  <--- The column in question


texts: InnoDB error: exceeded log capacity...but only during import

texts:
I see this error in mysql.log:

InnoDb error: the age of last checkpoint is...which exceeds the log group capacity etc..

I think I understand it, but the fact is that it's happening only during a night operation at minimum load,  consisting in a 100MB db import.

I import the result of mysqldump --opt, with the purpose of duplicating a db for testing.

The error suggests an increase in the log file x 10 times.

Considering that in no way I'm reaching this limit during normal daily time, I wonder if there is another way of dealing with the problem, rather than changing the log file size and restarting mysql...

The speed of the import operation is not important.

Mysql v5.1.69


texts:
We have a large database (1.8TB) with eight filegroups created on SQL 2000 and now running on SQL Server 2005 9.0.5057 since early 2012. Simple recovery.

On September 27, we got a torn page error in one of the filegroups and error 824. The vendor of the third-party app that uses this database asked us to restore that filegroup to a new database, which I did over that weekend. The vendor created a new filegroup and copied everything from the restored filegroup to repair the production database. Everything seems fine operationally, but we still have the corruption in the old empty filegroup.

CHECKDB found 254 allocation errors and 0 consistency errors.

There are also 9 page errors like this:
Msg 8906, Level 16, State 1, Server ANNIE, Line 1
Page (5:4114) in database ID 5 is allocated in the SGAM (5:3) and PFS
(5:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT
ALLOCATED   0_PCT_FULL'.

Only one of those 9 pages is listed in suspect_pages.

I ran DBCC PAGE on the 9 pages in the CHECKDB output. 8 pages are type 2 and 1 is type 10. All list object ID as m_objId (AllocUnitId.idObj) = 41899 and OBJECT_NAME(41899) returns null.

Also all the errors are on file_id 5, which is the filegroup that is now empty.

It seems like if we could delete the corrupt filegroup, we would be OK, but SHRINKFILE and REMOVE FILE fail because of the torn page error.

Is there any other way to delete the empty filegroup?

Thanks for any advice!


texts:
I'm trying to get the count all the data for the query and as well as get the limited data (for pagination) but both the console output gives me the count. What is that I'm doing wrong. Pls help

const curFind = fbUser.find(find, 'firstName gender age birthday facebookId profileURL email imageUrl preferences blocked flames likes rejects location reported')

  curFind.count(function(e, count) {
    console.log(count);
    curFind.skip(0).limit(10).hint( { $natural : 1 } ).exec(function(err, data) {
      console.log(data);
    });
  });


texts:
In answer to SQL counting distinct over partition Erik Darling posted this code to work around for the lack of COUNT(DISTINCT) OVER ():

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A
            ) AS ca;

The query uses CROSS APPLY (not OUTER APPLY) so why is there an outer join in the execution plan instead of an inner join?

Also why does uncommenting the group by clause result in an inner join?

I don't think the data is important but copying from that given by kevinwhat on the other question:

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)


texts: SQL Server 2008 and Management Studio - Intellisense is Patchy

texts:
I'm working off an SQL 2008 instance that sits on our intranet. All is fine except that sometimes intellisense is there and working and sometimes it isn't. Its a pretty fast network so I don't imagine its a case that the RDBMS is reactively switching intellisense off because of latency issues etc.

Is there some other mechanism that can be used to ensure that intellisense always works?


texts:
Today, I'm reading about BCNF decomposition algorithm. It says that:

  BCNF Decomposition Algorithm

  Input: a relation R0 with a set of functional dependencies S0

  Output: a decomposition of R0 into a collection of relations, all of
  which are in BCNF

  Method: R=R0, S=S0

  Check whether R is in BCNF. If so, nothing to do, return {R}
  If there are BCNF violation, let one be X→Y
  Compute X+
  Choose R1=X+, and let R2 have attributes X and those attributes of R
  that are not in X+
  Compute the sets of FD’s for R1 and R2, let these S1, S2
  Recursively decompose R1, R2 using this algorithm. Return the union
  of the result of these compositions

I'm trying to apply that algorithm to decompose this relation:

R(A, B, C, D)
={AB→C,C→D,D→A}

As you can see, the key is {AB}, and 2 violations are C→D,D→A.Then:

Compute {}+ = {,,}
Decompose R into R1(C, D, A) and R2(B, C).
In R1, C is the key, so D→Ais a violation.
Compute {D}+ = {D,A}
Decompose R1(C, D, A) into R3(C, D) and R4(D, A)
The final result is: R2(B, C), R3(C, D), R4(D, A)

My first question is: is it correct?

I feel that we can decompose R into 2 relations which are (A, B, C) and (C, D). They are also in BCNF. How do we decompose R into that 2 relations? Which algorithm? Which way is better?

Thanks for your help.


texts: User Permisssions White List vs Black List

texts:
I have Applications, Roles, Users, Permissions, Application_Permissions, User_Permissions, Role_Permissions, Application_Roles and Role_Permissions tables. So, permission can be defined at application level, role level and user level. Let's say User A belongs to Application X and Application X can have XX Permission. So, User A will get XX Permission. Now, I want to give all users of Application B with XX Permission but except User B. How can I fit this with my current design?
CREATE TABLE APPLICATION_PERMISIONS (APPLICATION_PERMISION_ID number(15) GENERATED AS IDENTITY, APPLICATION_ID number(15), PERMISION_DEFINITION_ID number(15)CONSTRAINT PK_SEC_APPLICATION_PERMISIONS PRIMARY KEY (APPLICATION_PERMISION_ID));
CREATE TABLE APPLICATION_ROLES (APPLICATION_ROLE_ID number(15) GENERATED AS IDENTITY, APPLICATION_ID number(15), ROLE_ID number(15)CONSTRAINT PK_SEC_APPLICATION_ROLES PRIMARY KEY (APPLICATION_ROLE_ID));
CREATE TABLE APPLICATIONS (APPLICATION_ID number(15) GENERATED AS IDENTITY, CODE varchar2(30), NAME_AR varchar2(255), NAME_EN varchar2(255), START_DATE date, END_DATE date CONSTRAINT PK_SEC_APPLICATIONS PRIMARY KEY (APPLICATION_ID));
CREATE TABLE PERMISION_DEFINITIONS (PERMISION_DEFINITION_ID number(15) GENERATED AS IDENTITY, CODE varchar2(30), NAME_AR varchar2(255), NAME_EN varchar2(255), START_DATE date, END_DATE date CONSTRAINT PK_SEC_PERMISION_DEFITIONS PRIMARY KEY (PERMISION_DEFINITION_ID));
CREATE TABLE ROLE_PERMISSIONS (ROLE_PERMISSION_ID number(15) GENERATED AS IDENTITY, ROLE_ID number(15), PERMISION_DEFINITION_ID number(15), START_DATE date, END_DATE date CONSTRAINT PK_SEC_ROLE_PERMISSIONS PRIMARY KEY (ROLE_PERMISSION_ID));
CREATE TABLE ROLES (ROLE_ID number(15) GENERATED AS IDENTITY, CODE varchar2(30) NOT NULL, NAME_AR varchar2(255), NAME_EN varchar2(255), START_DATE date, END_DATE date CONSTRAINT PK_SEC_ROLES PRIMARY KEY (ROLE_ID));
CREATE TABLE USER_PERMISSIONS (USER_PERMISSION_ID number(15) GENERATED AS IDENTITY, USER_ID number(15), PERMISION_DEFINITION_ID number(15)CONSTRAINT PK_SEC_USER_PERMISSIONS PRIMARY KEY (USER_PERMISSION_ID));
CREATE TABLE USER_ROLES (USER_ROLE_ID number(15) GENERATED AS IDENTITY, USER_ID number(15), ROLE_ID number(15)CONSTRAINT PK_SEC_USER_ROLES PRIMARY KEY (USER_ROLE_ID));


texts: Alternative to EAV for dynamic fields in a star schema data warehouse

texts:
I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against them in the future (so it is not just storage, so I can't use blob for them)

e.g. http://example.com/?action=test&foo=abc&bar=def...

I need to store all the field => value mappings, i.e. (action => test), (foo => abc), (bar => def), and since the field is such dynamic, the only solution I have found is to use Entity-Attribute-Value, however, people keep saying it is a very bad design.

So, consider my use case above, what would be a suitable alternative to EAV?

My current schema using KAV

Table requests
(id, timestamp, uri)
e.g. (1, 149382220, '/')
Table params
(request_id, key, value)
e.g. (1, 'action', 'test'), (1, 'foo', 'abc'), (1, 'bar', 'def')

Any suggestions?

Update: We run the warehouse on AWS RedShift


texts: in Oracle, which tables/indexes in query are responsible for disk reads?

texts:
Looking at the V$SQL_MONITOR view, I noticed some long-running queries where all the time is on disk I/O.  I can see the total number of disk reads for the query.

But how would I get the next level of detail to see which table/index in the query was responsible for those disk reads?


texts: How can I reproduce InnoDB buffer pool 'warm-up' time? What am I doing wrong with sysbench?

texts:
I've found that a large production DB experiences a severe degradation in performance, after a restart.  It makes sense that this is because after a restart, the innoDB buffer pool isn't yet warmed up - so a high proportion of requests require a disk access.

I want to observe and measure warm-up time in my (lab) DB, and I've been trying, using sysbench oltp_read_write, with --table-size=20000000.

This table size yields a database of about 50G.  I have 128G memory on the machine I'm using, and innodb configured with innodb_buffer_pool_size = 80G.  So the entire table should eventually be handled within the buffer pool after 'warm-up' is completed.  I'm using MySQL v5.7

I also have configured innodb_buffer_pool_dump_pct = 1 so that I can easily reproduce an un-warmed-up buffer pool, simply by stopping & restarting mysqld.

I run a series of short tests, observing after each test:

a Innodb_buffer_pool_bytes_data as a measure of the degree of 'warmed-up-ness' of the buffer pool.
b Innodb_buffer_pool_read_requests
c Innodb_buffer_pool_reads
d buffer pool hit rate - calculated as as b / (b + c)

Results:

I expect that Innodb_buffer_pool_bytes_data should increase after each test, eventually levelling off as it approaches 50GB.  It does.  This shows that the buffer pool is indeed warming up.
I expect that the buffer pool hit rate would also increase after each test, approximately in proportion with the value of 'a'.  But this isn't what I see, and I can't understand why not.  What I see instead is that after 60s of testing, the Pool seems to be loaded with about 25% of the content of the DB, but 92% of requests are already being handled by the buffer pool.

At first, I thought that maybe sysbench isn't generating a truly random set of requests.  This can be varied by --rand-type=gaussian or --rand-type=uniform instead of the default which is --rand-type=special.  I tried using 'gaussian' or 'uniform' [not really random at all].  The results with 'gaussian' were much the same as with 'special'.  The results with 'uniform' were different in that the entire DB was loaded into buffer pool more quickly, but the same in that the hit rate was already over 90% even when only 25% of the data was loaded into the buffer pool.

So my question is:
Why is it that innoDB is satisfying >95% of requests through the buffer pool, without going to disk - even though the reported buffer pool size shows that it only contains 25% of the data in the database?


texts:
I am so confused with this issue,  I have vb.net application and it has a sql database .

Now, I want this application to access the sql server from anywhere , because there are many clients sharing the same database from many regions.

I don't want to make my current sql server available online by opening ports and allowing TCP remote connections, I am looking for a hosting service or solution that provides sql server database hosting.
I just found web hosting services, and they do not allow users to use thier sql databases remotely for storage purposes.

Another words, I want to access it anywhere by some IP or domain or shared server, not by letting my computer working 24 hours to serve clients connections.

Thanks


texts:
I have this SQL request:

SELECT
    m.id,
    IF(CURRENT_TIMESTAMP BETWEEN bc.start_date AND bc.expiration_date, FALSE, TRUE) AS hasFreeAccount,
    CONCAT(e.id, ' - ', e.name) AS establishment,
    bc.start_date,
    bc.expiration_date
FROM establishment e
    LEFT JOIN establishment_owner eo ON e.id = eo.establishment
    LEFT JOIN member m ON eo.member = m.id
    LEFT JOIN business_command bc ON m.id = bc.member
WHERE eo.member = 103
    AND (bc.start_date <= CURRENT_TIMESTAMP() OR bc.start_date is NULL)
    AND (bc.id NOT IN (
        SELECT bc.id
        FROM member m
            LEFT JOIN business_command bc on m.id = bc.member
            INNER JOIN establishment_owner eo on m.id = eo.member
        WHERE CURRENT_TIMESTAMP() > bc.expiration_date
            AND eo.member IN (SELECT m.id FROM member m LEFT JOIN business_command bc ON m.id = bc.member HAVING COUNT(m.id) > 1)
    ) OR bc.id IS NULL);

With this Explain plan:

The request works, I just want to know if I can optimize it.


texts:
I've installed Oracle Database Express 10g on Ubuntu, but the following error occures when I try to connect to sql plus using the sysdba as username and it's password which I gave during installation :

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

These are some iformations I think they may help :

uname -a

Linux aimad-Vostro-1015 3.11.0-17-generic #31-Ubuntu SMP Mon Feb 3 21:53:31 UTC 2014 i686 i686 i686 GNU/Linux

env | grep ORACLE

ORACLE_SID=XE
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

sysctl -p |grep kernel

kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
kernel.shmmax = 1073741824

sysctl -a |grep kernel | grep shm

kernel.shm_next_id = -1
kernel.shm_rmid_forced = 0
kernel.shmall = 2097152
kernel.shmmax = 1073741824
kernel.shmmni = 4096

sysctl -a | grep kernel | grep sem

kernel.sem = 250    32000   100 128
kernel.sem_next_id = -1

df -k /dev/shm

Filesystem     1K-blocks  Used Available Use% Mounted on
none             1014392   156   1014236   1% /run/shm

How can I solve this problem ?


texts:
I'm trying to calculate purchase/profit growth rate in different time periods, This is the query i've come up with (View in SQLFiddle):
Schema:
CREATE TABLE `profit_log` (
  `market_id` smallint(6) NOT NULL,
  `total_purchase` bigint(20) NOT NULL,
  `total_profit` bigint(20) NOT NULL,
  `company_profit` bigint(20) NOT NULL,
  `date` date NOT NULL,
  `tid` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Query:
SELECT a.`market_id`,
    SUM(a.`total_purchase`) as acc_purchase,
    SUM(a.`total_profit`) as acc_profit,
    SUM(a.`company_profit`) as acc_company_profit,
    ((b.`total_purchase` - c.`total_purchase`)/ c.`total_purchase`) * 100 as growth,
    ((d.`total_purchase` - e.`total_purchase`)/ e.`total_purchase`) * 100 as growth_week,
    ((f.`total_purchase` - g.`total_purchase`)/ g.`total_purchase`) * 100 as growth_month,
    ((d.`total_profit` - e.`total_profit`)/ e.`total_profit`) * 100 as growth_week_profit,
    ((f.`total_profit` - g.`total_profit`)/ g.`total_profit`) * 100 as growth_month_profit
FROM `profit_log` as a
JOIN `profit_log` as b on a.`market_id` = b.`market_id`
    AND b.`date` = '2016-05-05'
JOIN `profit_log` as c on a.`market_id` = c.`market_id`
    AND c.`date` = '2016-05-01'
JOIN `profit_log` as d on a.`market_id` = d.`market_id`
    AND d.`date` >= ('2016-05-05' - INTERVAL 7 DAY) AND d.`date` < ('2016-05-05' + INTERVAL 1 DAY)
JOIN `profit_log` as e on a.`market_id` = e.`market_id`
    AND e.`date` >= ('2016-05-05' - INTERVAL 14 DAY) AND e.`date` < ('2016-05-05' - INTERVAL 7 DAY)
JOIN `profit_log` as f on a.`market_id` = f.`market_id`
    AND f.`date` >= ('2016-05-05' - INTERVAL 1 MONTH) and f.`date` < ('2016-05-05' + INTERVAL 1 DAY)
JOIN `profit_log` as g on a.`market_id` = g.`market_id`
    AND g.`date` >= ('2016-05-05' - INTERVAL 2 MONTH) AND g.`date` < ('2016-05-05' - INTERVAL 1 MONTH)
WHERE a.`date` >= '2016-05-01'
AND a.`date` < ('2016-05-05' + INTERVAL 1 DAY)
AND a.`market_id` IN (0,20)
GROUP BY a.`market_id`

The Strange thing is after all this JOINs, SUMs (i.e SUM(a.total_purchase)) return unbelievably large numbers, it seems that values are accumulated over all this joins.
Can you please explain why is that happening (maybe i don't understand JOIN behavior), and help me to rewrite this query the proper way (6 self joins doesn't look right).
Thanks.


texts: Service broker internal spid hogging tempdb

texts:
We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker internal SPIDs (<50) which are still holding ~75% of TempDB space.

Now, I know these are service broker tasks because the CMD column on SysProcesses shows 'BRKR EVENT HNDLR' and 'BRKR TASK', while the last wait types are 'BROKER_EVENTHANDLER' and 'SLEEP_TASK' respectively.  Both have 'BACKGROUND' as a status, 0 open trans and a last batch time of over a month ago.

Currently the service broker implementation has been fully disabled (from a code perspective), the queues are disabled (is_activation_enabled and is_receive_enabled = 0), there is nothing in the transmission_queue, conversation_endpoints or actual queues, but the database in question does still have is_broker_enabled = 1 (as do TempDB and MSDB).

The server is currently running SP2 with a security fix (11.0.5343.0) and the TempDB usage appears to persist even after a cluster failover.  We've not tried restarting as this is a production system, but I would have thought that a failover would have had the same effect.

Has anyone encountered this issue previously and if so, what did you do to resolve it?

Thanks,


texts: Can SQL Server stop me from running an UPDATE statement without a WHERE clause?

texts:
Whenever I have to run an update statement, I always get nervous that I might forget the WHERE clause and overwrite the entire column in the database.  Is there any setting or plugin or addon I can get for SQL Server Management Studio to block certain dangerous statements, unless you explicitly say its ok to run?

I'm working on a development server, so I can always roll back the changes, but still, I feel like I might have only the first two lines highlighted and click Execute or do something else stupid.


texts:
I created a partition for a table. There is a column "archived" (tinyint). 0 goes to PRIMARY data file, 1 to ARCHIVED data file.

ALTER DATABASE MYDB ADD FILEGROUP [ARCHIVED];

ALTER DATABASE MYDB ADD FILE (NAME = N'ARCHIVED', FILENAME
  = N'D:\...\MYDB_ARCHIVED.mdf' ...) TO
  FILEGROUP [ARCHIVED];

CREATE PARTITION FUNCTION FNArchived (tinyint) AS
RANGE LEFT FOR VALUES (0);

CREATE PARTITION SCHEME SCArchived
    AS PARTITION FNArchived
    TO ([PRIMARY],[ARCHIVED]);
GO

The I dropped the primary key and added this :

ALTER TABLE mytable ADD CONSTRAINT PK_mytable PRIMARY KEY NONCLUSTERED  (uid) ON [PRIMARY];
CREATE CLUSTERED INDEX IX_mytable_archived ON mytable (archived) ON SCArchived(archived);

All seems fine there is no error.

But how can I make sure that data is written to the right data file?


texts: MySQL not starting when shut down with mysqladmin

texts:
MySQL server 5.5 fails to start when I use the mysqladmin shutdown command to stop the server. The only option to start MySQL server is to reboot the machine.

Is there is any option so that I can start MySQL server normally with /etc/init.d/mysql start?

I am using Debian 8 jessie.


texts:
I have a CSV file with many integers in the form:

id
---
1
2
3

I need to write a statement that selects ids from a table that are in the list in my CSV.

I could just convert the data 1000 elements at a time into a SQL IN like:

SELECT * FROM TABLE WHERE ID IN (....)

but that would take hundreds of queries, is there a better way I can do this?


texts: How to find foreign keys that don't have corresponding indexes?

texts: I have installed Oracle database 11g R2 with a customized application. In my environment we are experiencing locking on tables. I want to check if there are  foreign keys in my database without the corresponding indexes. Is there any query or procedure to find un-indexed foreign keys?


texts: Can you use "RENAME" instead of "FLASHBACK TABLE" to restore a table in Oracle?

texts:
Just a general question about flashback and the recycle bin in Oracle.  I tried doing this on a demo oracle database I have but the recycle bin does not seem to want to be populated; and I want a quick answer.

One can do the following:

flashback table table_name to before drop;
alter index "BIN$U/9fvJKaASzgQKjAYAIWhw==$0" rename to sys_c1113050;

Can you do this then, to restore a table:

RENAME TABLE BIN$U/9fvJKaASzgQKjAYAIWhw==$0 TO TABLE_NAME


texts: Regnerating an execution plan - is this a valid approach?

texts: If I am optimizing a query, in order to ensure that this query does NOT uses cached execution plan I use OPTION (RECOMPILE) with the query. Is it a good approach?


texts:
I have been struggling with this for a little while.

I want to have a set of calendar records.  The top level will apply to a large group; the next level will apply to a sub-set and the final level could apply to a single person.

The top level might have a record that looks like this:

ID : LinkID : Date     : Venue         :  Training       : Group
1  : NULL   : 26/2/16  : Running Track :  4 x 800m       : AC Seniors

The next level might have a record that looks like this:

ID : LinkID : Date     : Venue         :  Training       : Group
2  : 1      :          :               :  5 x 1000m      : AC Elite Men

The bottom level could look like this:

ID : LinkID : Date     : Venue         :  Training       : Group
3  : 2      :          : Local park    :  5 x 1000m      : Jon Runner

The idea is that the coach can set up a general training programme (top level) that is then edited for a sub group (middle level) and then individualised.

Jon Runner would see this record:

ID : LinkID : Date     : Venue         :  Training       : Group
3  : 2      : 26/2/16  : Local park    :  5 x 1000m      : Jon Runner

If any of the higher level information changes, then there is a need to flag further down the tree.

I have a good idea how to do this in Object Oriented Classes, but struggle to see how to implement it so that it can be stored in a database.

Any ideas or tips would be great.


texts: how to grant select access my sql server

texts:
when I exec the syntax to gain the access I get an error that I cant grant permissions  to my self.
any Ideas please!


texts:
I built a little setup with a raspberry pi and a temperature and humidity sensor. Every 5 minutes it reads sensor values and sends them both + a timestamp and a custom ID (there are multiple pis, each with its own ID) to a nodejs API which then stores them in a single mongodb collection. A GET request to this API then returns all objects from the collection with the id provided via GET.

Here is a json sample object of the data structure from my collection:

{
    "_id": "5c935a046b4fcc27f2a81cd8",
    "id": "livingroom",
    "time": "2019-03-21 10:31:48.601374",
    "temp": "22.0",
    "hum": "46.0"
}

Im pretty new to mongodb and databases in general but my programming experience tells me that just dumping everything into a single collection is not a good way to handle my data (propably, I really don't know).

So my question is: Are there any big gotchas of me handling my data like this or am I fine for this projects scope? Do I need to expect big performance hits if this collection gets too big?


texts: Repartition PostgreSQL tables without losing data

texts:
I already have my table partitioned as follows:

CREATE TABLE customer
(
  c_custkey integer NOT NULL,
  c_name character varying(25) NOT NULL,
  c_address character varying(40) NOT NULL,
  c_city character(10) NOT NULL,
  c_nation character(15) NOT NULL,
  c_region character(15) NOT NULL,
  c_phone character(15) NOT NULL,
  c_mktsegment character(10)
) PARTITION BY List(c_region) ;

create table customer_1 PARTITION of customer for values in ('EUROPE');
create table customer_2 PARTITION of customer for values in ('AFRICA');
create table customer_3 PARTITION of customer for values in ('AMERICA');
create table customer_4 PARTITION of customer for values in ('MIDDLE EAST');
create table customer_5 PARTITION of customer for values in ('ASIA');

My partitions are filled with data. I want to repartition my table without losing data. How to do that with PostgreSQL?


texts: MySQL showing non-linear behavior for a simple insert

texts:
I've come across a symptom in MySQL which got me baffled. First, the time it takes to INSERT a certain amount of records changes non-linearily with the amount of records. Second, the time also depends on the size of table, even when it has no indexes.

First, the tables (both MyISAM)

mysql> desc articles;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| uuid           | varchar(250) | NO   | UNI | NULL    |                |
...snip unrelated columns...
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> desc articles_groups;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| article_id   | int(11)      | NO   | MUL | NULL    |                |
| group_id     | int(11)      | NO   | MUL | NULL    |                |
| seq_num      | bigint(20)   | YES  |     | NULL    |                |
| article_uuid | varchar(250) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

Both tables contain about 15 million records with little fragmentation; I need to combine some data from the tables, so I create a temporary table:

mysql> create temporary table aga (aid int, agid int);

Nothing fancy; no indexes or primary keys.

Then I insert data int aga by combining data from the other tables:

mysql> insert into aga (aid,agid) select articles.id,articles_groups.id FROM
  articles,articles_groups where articles.uuid=articles_groups.article_uuid AND
  articles_groups.id >= 0 AND articles_groups.id < 10000;
Query OK, 9999 rows affected (0.50 sec)

I've limited the number of records because a straight insert takes way too long. Now comes the interesting part. Look at the following table on how long the inserts take, depending on the number of records:

 articles_groups.id        Time (seconds)
 -------------------------+--------------
 0 - 10K                  | 0.50
 10K - 20K                | 0.47
 20K - 30K                | 0.47
 30K - 50K                | 0.92
 50K - 100K               | 2.28
 100K - 200K              | 35.87 (!)
 200K - 300K              | 33.87
 300K - 500K              | 96.76
 500K - 510K              | 8.81 (eh?)
 510K - 520K              | 5.89
 1000K - 1010K            | 4.75
 2000K - 2010K            | 10.14
 2010K - 2020K            | 9.02

Obviously it takes longer to insert more records, but there's a big jump when going from 50K inserts to 100K inserts. And 200K inserts take triple the time, not double. I could understand this if the aga table has indexes, but it doesn't.

After deleting the records from aga and performing the last insert again, it just takes 0.68 seconds, so clearly there is a relation to the size of the table and not the articles_groups.id (which is what I thought at first).

This is on a Linux machine with MySQL 5.1.46; quad core, 8 GB RAM; MySQL uses about 1.3 GB RAM.

Now as to my questions:

Appearantly there is a 'wall' between 50K and 100K inserts for this particular query. Is there an easy way to find the optimal number of inserts, other than trial and error?
If I would split the query into 10K batches as I have done above and run them in sequence, would that be faster than 1 query with no limits?
Why does the time depend on the table size, even without indexes?


texts: Connection to SQL Server failed. A network-related or instance-specific error occurred while establishing a connection to SQL Server

texts:
I'm getting this error while trying to connect to sql server. Now this never happened before and it's really annoying.

I found out in my sql configuration manager that SQL server service has stopped for some reason.

When I try to start it again it gives me error that says: "The request failed or the service did not respond in a timely fashion".

I found some solution for this but none of them works for me. I checked if VIA protocol is enabled, and it was disabled.

I checked

if my server allows remote connections and it does.
if port 1433 is used by some other service, but still it was not used by any other services.

Those are solutions that I found and tried to solve this problem with them, but no success.

Is there any other reason why is this error happening and how to solve it?


texts: Ola Hallengren - DatabaseBackup SQL Version - Backup to Azure requirements

texts:
I see that the DatabaseBackup requires SQL Server 2012 SP1 CU2 (11.03339). I have a Server using SP1 (11.0.3000), and I have verified Backing up to azure blob storage does work.

Is there a reason the Hallengren scripts require CU2?


texts: Update a column based on another's table column value

texts:
I have a table Users

+----------+----------+
|  ID_EMP  | isActive |
+----------+----------+
|   100    +    1     +
|   101    +    1     +
+----------+----------+

and a table UsersInfo

+----------+------------+
|  ID_EMP  |   FRDATE   |
+----------+------------+
|   100    +   null     +
|   101    + 2019-10-10 +
+----------+------------+

I tried to update column Users.isActive with 0 when the UsersInfo.FRDATE is not null. I read similar questions and ended up with the following query, which is not working as expected, as it is updating all the columns with 0

UPDATE [dbMyDB].[dbo].[Users]
SET [dbMyDB].[dbo].[Users].[isActive] = 0
FROM [dbMyDB].[dbo].[Users] USERS
INNER JOIN
    [dbMyDB].[dbo].[UsersInfo] INFO
ON USERS.ID_EMP = INFO.ID_EMP
WHERE EXISTS
(
    SELECT FRDATE FROM [dbMyDB].[dbo].[UsersInfo]
    WHERE FRDATE IS NOT NULL
)

what I am missing?
Thank you!


texts:
We are planning to replicate our Postgres database to Cockroach. We are looking for a relatively simpler solution to achieve that.

Currently, we are working on a Stream based replication (something of this sort) is there any better alternative than this.


texts: Function access query/queries in Postgres

texts:
I'm working on a project in which I need to determine which functions a given account has access to run.

So essentially, if I give this query an ID, can I get a list of functions that ID has access to execute.

Is this possible?

Thanks in advance.


texts: How can a database parameter be used on `sp_addextendedproperty`?

texts:
I have found the MSDN definition and code examples of 'sp_addextendedproperty'.  In Example A on that page, I see an example of how to add an extended property to a database.

USE AdventureWorks2012;
GO
--Add a caption to the AdventureWorks2012 Database object itself.
EXEC sp_addextendedproperty
@name = N'Caption',
@value = 'AdventureWorks2012 Sample OLTP Database';

The thing that is immediately obvious is that you first have to declare a database context, and the stored procedure operates only within that database context.

If I wanted to add a database as a parameter, I suppose I could create a separate script that wraps around sp_addextendedproperty and sets the database context before executing the stored procedure.

For example

-- execute wrapper stored procedure
EXEC WrapperStoredProcedure @DatabaseName = N'Database name',
    @name = N'Extended property name',
    @value = N'Extended property value';
GO

In this case WrapperStoredProcedure probably uses Dynamic SQL to set the database context and execute sp_addextendedproperty.

Am I missing an obvious technique or convention that will allow me to declare the database as part of the EXEC sp_addextendedproperty statement, without having to create a separate wrapper scripts?


texts: how to speed up cpu limited mysql database backup

texts:
I have a database that has a structure that unfortunately requires having a lot of tables (10k+). It's not possible to change the structure now.

The database itself is not very large (<1GB), but the backups are taking a very very long time, something like 40 minutes. It is a CPU limited process as it runs one core on the server at 100% for the entire duration.

The command used to do the backup is:

mysqldump -u root -password --single-transcation --skip-lock-tables --all-databases > dump.sql

Are there any tricks to speed up this backup?


texts: How to query time series with gaps between a given date range in PostgreSQL?

texts:
My problem is I don't know how to query time series data between a specified date range where the data has gaps in it and the last known value which should carry forward for each date falls outside the queried date range.
I have the following data in a PostgreSQL table:
DeviceID    State    Date
1           L7       2020-12-05 15:18:00
1           L1       2020-12-10 00:15:00
1           L5       2020-12-11 15:00:00
1           L3       2020-12-14 13:00:00
2           L2       2020-12-08 14:00:00
2           L1       2020-12-14 05:32:00

I would like to query between 2020-12-09 -- 2020-12-12, but my problem is that I don't know the state of DeviceID 1 on 2020-12-09 because the series data has gaps in it.
In the example above DeviceID 1 state is L7 at 2020-12-09, so I would need to query outside the given date ranges to find the L7 state.
My queried table should look like this for the specified date ranges:
DeviceID    State    Date
1           L7       2020-12-09
1           L1       2020-12-10
1           L5       2020-12-11
1           L5       2020-12-12
2           L2       2020-12-09
2           L2       2020-12-10
2           L2       2020-12-11
2           L2       2020-12-12

My end goal is to calculate the time difference between the state changes for a DeviceID, so I could still do that from data with gaps, my only problem is that I don't know the last state change event that falls outside the queried date ranges, in the example above I don't know the state is L7 for DeviceID 1 and L2 for DeviceID 2 at 2020-12-09.


texts: How to store single result in variable and reuse it in a query (Oracle)?

texts:
I have a query like

SELECT id FROM xyz WHERE ...;

which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a script.
So my goal was to reuse this result in a variable but I don´t come to a solution which works.

Joining this query would not be a good option in this circumstance. So is it possible to tell Oracle to store this single result in a variable and use this variable to build up new queries - explicitly to use in in the conditional clause of other queries?

If so, perhaps somebody could post a simple example - most simple perhaps would be sth. like

SELECT :VARIABLE FROM DUAL


texts:
I am using SQL Server 2012 and was wondering how to profile stored procedures

For instance can profiler capture each individual SQL statement in a stored procedure, what it is, and how long it takes to run etc?

I am trying to diagnose merge replication stored procedures, and this must be captured a part of a full run of the merge agent. It doesn't seem possible to grab the stored procedure with performance issues and run it again because at that point it is not slow.


texts: Attribute: Majority single value, but multiple on occasion

texts:
Here is the situation:
There is a dimension with a natural key that is a good candidate for a primary key. However, there are a few columns that can have (and does have - 10% of the time) multiple values. The majority of the data have only one value per key.

My options:

For the 90%, put it into the table and have "multiple" as the value for the rest; and snowflake the list.
Same as #1, but LISTAGG the values. This will make numeric columns - non-numeric.
Create a surrogate key, and replicate the rest of the columns with a flag to pick one of the rows - to avoid coding DISTINCT is the attribute is not being used.
Just snowflake the column to another table.
Any other ideas?


texts: Backing up database without transaction log

texts:
When I backup a database is there a way to exclude the transaction log in that backup? Or am I thinking about this wrong and to restore the original database the content of the transaction log is required?

I cannot shrink the transaction log because the database is using replication and I would have to break the replication.

If I issue this command and then restore the database it comes back with a 50 GB log file:

BACKUP DATABASE DatabaseName TO DISK = 'c:\temp\Database.bak'

I have to change the restored database to simple and then shrink the log file before the 50GB is freed. I cannot change the original database to simple because it is a replicated copy.


texts:
I ran sp_Blitz and on the top I got:

  Finding:  Poison Wait Detected: RESOURCE_SEMAPHORE

  Details:  0:12:36:38 of this wait have been recorded. This wait often
  indicates killer performance problems.   This means that SQL Server
  ran out of available memory to run queries, and queries had to wait on
  available memory before they could even start…

My questions are:
-Is the time in minutes? Is it a number to worry? Can I conclude just by this that I need to buy more memory for the server?

(Server has 30GB installed RAM).

Instance has only been up for 2 weeks…
EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

EXEC sp_BlitzFirst @SinceStartup = 1

Where should I redirect my efforts?


texts: When migrating all the way down, should I just run VACUUM (FULL) as the last down migration?

texts:
I use node-pg-migrate and Amazon RDS Postgres on a development environment where I'm the process of building an app.

Sometimes I migrate all the way down so that the database is almost as it was when I created the instance.

I found recently that when I did several migrations up and down again, that I ran out of storage (5GB) after the last up migration. After I migrated fully down not the full 5GB is available. That may be because some persistent objects occupy some space.

Nevertheless, I noticed when I ran VACUUM FULL, that more storage was available.

Update. It turns out that more storage may have also become available due to autovacuum which may have coincided with my VACUUM FULL query within graph accuracy in my first test. See the graph below of the free storage space during a second test where I intentionally waited a bit before running VACUUM FULL.

Should I (still) create a down migration file that runs last and runs a VACUUM FULL query to free up storage space? I suspect no downsides since all my objects and data are gone. And autovacuum will do it anyway within minutes.


texts: Secondary nodes in mysql 8.0.15 innodb cluster are not reading group_replication_member_expel_timeout variable from my.cnf

texts:
I have setup a 3 node mysql 8.0.15 innodb cluster. I have been trying to set group_replication_member_expel_timeout variable to 60 via the /etc/my.cnf file. Although the primary node has read the variable properly, secondary nodes are not picking up this variable and are still using the default value 0.

Here are the contents of the /etc/my.cnf file

[mysqld]
default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
group_replication_member_expel_timeout=60
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
collation-server = utf8_unicode_ci
character-set-server = utf8

After restart, here are the variables in the mysqld instance.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.15    |
+-----------+

mysql> select @@group_replication_member_expel_timeout;
+------------------------------------------+
| @@group_replication_member_expel_timeout |
+------------------------------------------+
|                                        0 |
+------------------------------------------+

I've tried replacing group_replication_member_expel_timeout with group-replication-member-expel-timeout in the /etc/my.cnf, with no success.
I've also noticed that all other variables are being read properly, it is only a problem with group_replication_member_expel_timeout variable.


texts: postgres: join tables, order by GREATEST

texts:
Having the following table structure

table1
  id: BIGSERIAL
  updated_at: TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
  ...

CREATE INDEX table1_updated_at ON table2(updated_at);

table2
  table1_id: BIGINT PRIMARY KEY
  updated_at: TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
  ...

CREATE INDEX table2_updated_at ON table1(updated_at);

trying to get results ordered by the most recently updated like:

select * from table 1 a
inner join table2 b on a.id = b.table1_id
order by GREATEST(a.updated_at, b.updated_at)
limit 100

But running into performance issues on large tables. I'm not entirely sure how to approach this problem. The pattern seems common enough to me.

It looks like postgres isn't able to combine the indices for updated_at when using GREATEST. Which seems kind of odd to me because if I have two large ordered lists, and want to get the 100 greatest values across the two lists, I don't have to traverse them completely, right?

edit

As requested, here's a EXPLAIN (ANALYZE, BUFFERS)


texts: How does SQL Server Merge Replication maintain referential integrity?

texts:
Note: This is a curiosity question about SQL Server.

I've been reading about SQL Server's Merge Replication feature, and there's one thing I just don't understand about it: How does Replication guarantee referential integrity without locking the database while replicating?

I know how msmerge_contents and msmerge_tombstone are used to enumerate changes. Let's assume row 1 of table A is listed in msmerge_contents, and has a foreign key pointing to row 2 of table B, which is also listed in msmerge_contents.

Now, both rows have been enumerated and replication has begun. What's to stop a user from creating a new row (row 3) in table B, and pointing the foreign key from row 1 of table A to this new row?

The new row will be saved in msmerge_contents as a new generation that has not been enumerated for this session and will therefore not be replicated. However, row 1 of table A will be replicated, because it belongs to the enumerated generations - and by the time replication gets to it, it may already be updated with the value of row 3 in table B!

Does anyone know how this problem is addressed, if it is addressed?

The following TechNet article does not answer the question, sadly.

How Merge Replication Tracks and Enumerates Changes


texts: What's the best way to use a gist index on tsrange to check if a time occurs after the range?

texts:
table (simplified)

                                        Table "public.events"
      Column      |            Type             |                       Modifiers
------------------+-----------------------------+--------------------------------------------------------
 id               | integer                     | not null default nextval('events_id_seq'::regclass)
 duration         | integer                     | not null
 start_at         | timestamp without time zone |
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "my_idx" gist (tsrange(start_at, end_at(events.*), '[)'::text))

function

CREATE FUNCTION end_at(rec events)
  RETURNS timestamp without time zone
  IMMUTABLE
  LANGUAGE SQL
AS $$
  SELECT $1.start_at + ($1.duration * ('00:00:01'::interval));
$$;

what I am already doing successfully

The index is used for queries like this:

-- check if current time is within the start and end times
-- of event
where localtimestamp <@ tsrange(start_at, events.end_at, '[)')

And it works well.

what I want to do

I want to query for events where the current time is after they have ended. Ways I know of how to do this:

where tsrange(localtimestamp, localtimestamp, '[]') >> tsrange(start_at, events.end_at, '[)'). I'm pretty sure this is the semantics I want, and explain analyze says it's using the index, but it's a bit ugly and I'm wondering if there's a better way to express this (and also am vaguely uncertain it's the semantics I want, as I am new to ranges).
where localtimestamp > upper(tsrange(start_at, events.end_at, '[)')) + a btree index on upper(tsrange(start_at, events.end_at, '[)')). This will work well, but requires keeping another index around.
where localtimestamp > events.end_at. + a btree index on events.end_at. Same situation as above.

Is there a more elegant (or correct) way to achieve the first bullet point above?

Any other ideas for how to go about this?