texts: Microsoft SQL 2008 Cluster with replication

texts:
i am in the process of setting upi a SQl Server 2008 64 bit Failover cluster.
A lot of the database queries that we are running during the days are db intensive and lock a lot of the tables while executing.

I was wondering if there is a "best way" to setup a replication server so we can run the queries on it without locking the main db server


texts: Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

texts:
We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's reasonably fast. When the where clause includes conditions for fields from both tables using an OR the query plan switches to a table scan and doesn't utilize any of the indexes.
All four fields that are being filtered on are indexed on their respective tables.
Fast query plan where I filter on 3 fields from one table: https://www.brentozar.com/pastetheplan/?id=Hym_4PRSO
Slow query plan where I filter on four fields...three from one table and one from another table: https://www.brentozar.com/pastetheplan/?id=r1dVNDRHO
Ideally I would like to understand why this is happening and how to nudge the query engine to utilize all the indexes.
I considered a union but unfortunately this legacy system is using ntext values which can't be unioned. Also note the join between the two tables is one to one so I really expected the optimizer to utilize the indexes, but maybe it doesn't know that?


texts: Is there any reason to maintain audit columns when the target table already has an audit table?

texts:
I'm currently designing a database schema for a small microservice which handles products.
The service contains a simple REST API which allows users to perform basic management of products and also some end points to query the full audit history about products. Schema already contains a snapshot-based audit table for products to support storing the full audit trail per product.
In addition of having separate audit history end points, each product specific REST API response must also include the createdAt, createdBy, updatedAt, updatedBy fields. The thing I'm wondering here is whether I should add these fields into products table or is it better to query information to those fields directly from the product audit table? Somehow I feel like adding these fields into the products table would seem a bit overkill when the same data (and even more) could be queried from the audit table.
Edit
Each CREATE, UPDATE and DELETE operation on the product table will also append a new entry into the audit table. This audit table contains the same columns than the actual product table plus additional columns for the timestamp, user ID and the type (ADD, MOD, DEL) of the audit event. The frequency of the management operations is quite low.


texts: How to prompt sql if there is no exception

texts:
I'm new to SQL advance methods. I'm trying to do some error handling after checking for exceptions.

DECLARE
value_invalid exception;
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM SET sga_max_size=12g';
EXCEPTION
when value_invalid then
null;
END

I'm trying to have a PROMPT for this SQL because I'm calling this script from a shell script. It returns out no prompt at all which is different from when I run the SQL command in sqlplus manually. See, for example,

SQL> ALTER SYSTEM SET filesystemio_options=setall scope=spfile;
System altered.

In my case, once I ran the shell file it gave me this:

SQL> Database Tuning for Static_Parameters
Database Tuning for sga_max_size
13   14  Disconnected from Oracle Database 12c Enterprise Edition Release
12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options

My questions is, was it altered successfully? How can I add a prompt to notify user that the system is altered? I tried ELSE to prompt the user if there's no exception, but still nothing appeared at the prompt.


texts:
In a table i have unique value check for the combination of box_number,price,start_val,end_val

so there can not be a two box number with same number which have same price , start_val and end_val..

table data right now

| box_type_id | box_number | price |start_val | end_val
|  1          |   7        | 5.00  |100       |1
|  2          |   5        | 10.00 |50        |40
|  3          |   5        | 5.00  |100       |1

what i want to do
i want to change box number 7 with 5 for box_type_id 1 and change box number 5 with 7 for box type id 3 ; my query is below... giving me error

MySQL Database Error: Duplicate entry '7-5.00-100-1' for key 'unique_index'

but here i am setting unique key check to 0. before executing query.

My query

SET UNIQUE_CHECKS=0;
 Update `box_types` `bt` SET `bt`.`box_number` = "7" where `bt`.`box_type_id` = "3" ;
 Update `box_types` `bt` SET `bt`.`box_number` = "5" where `bt`.`box_type_id` = "1" ;
SET UNIQUE_CHECKS=1;

how do i solve this problem.. is there any other prblem ? please guide me..


texts: importing Chinese characters into MySQL via SSIS

texts:
I faced a problem when I try to import Chinese characters from Hive into MySQL via SSIS. The setting in SSIS is below

If I use the same setting to import into SQL server the data is correct. But when I change to MySQL then cannot show Chinese character. And if I write Python script to import into MySQL I can also get the correct data.
Can anyone help me?
Thank you.


texts: How can I assign values (-1, 0, 1) to the difference between consecutive rows in a MySQL table having 20 columns and 20 rows

texts:
I have a table from MySQL called References with 20 columns and 20 rows:

CREATE TABLE bop_ppy
( start_time DATETIME (6)
, end_time DATETIME (6)
, high DECIMAL (5,2)
, low DECIMAL  (5,2)
, close DECIMAL (5,2)
, vol DECIMAL (5,2)
, vol_avg DECIMAL (5,2)
, range_ DECIMAL (5,2)
, poc_ DECIMAL (5,2)
, va_h DECIMAL (5,2)
, va_l DECIMAL (5,2)
, va_range DECIMAL (5,2)
, tpot INT
, tpo_ab INT
, tpo_bl INT
, sf DECIMAL (5,2)
, tff DECIMAL (5,2)
, rf INT
, vty DECIMAL (5,2)
, dists INT);

The data is updated daily.

I need to compute the difference between consecutive rows, for each column, (except the start_time and end_time columns, which are incremented by 1 day) such that if the value on day 2 is higher than the value on day 1, we assign +1 to the difference; if the value on day 2 is less than the value on day 1, we assign -1 to that difference; if the value on day 2 is equal to the value on day 1, we assign 0 to that difference. The idea is to generate a new table/view that shows only the tallies, instead of the original values.

Each row corresponds to one day's data. I am using MySQL Workbench.

I have tried to first do a self join of the table and then assign dummy variables to the difference between consecutive rows, but so far this has not worked.I don't know how to make this work anymore. Please could someone help.


texts:
I am using Merge Replication with SQL 2012. I look in the snapshot directory, but the largest file in there is a prc file which is 646 KB.

I know for sure that the biggest of my replicated tables is 25 MB in the database after replicating, so I am not sure I understand why there aren't larger files in the snapshot directory?

Also is there a place I can look for the snapshot files as they are downloaded to the subscriber?

For instance the merge agent outputs messages such as,

Downloaded 100 change(s) in 'tableName' (100 inserts) : 144083 total

I would like to see the data being downloaded and get a feel for how large it is.


texts: (How) does table inheritance interfere with locking in postgres?

texts:
I have a table inheritance setup that might be simplified like this:

CREATE TABLE p (
    id BIGSERIAL PRIMARY KEY,
    type_id BIGINT,
    approved BOOLEAN
);

CREATE INDEX ON p(approved);

CREATE TABLE a (
    a_field VARCHAR,
    PRIMARY KEY (id),
    CHECK(type_id = 1::BIGINT)
) INHERITS (p);

CREATE TABLE b (
    b_field INT,
    PRIMARY KEY (id),
    CHECK(type_id = 2::BIGINT)
) INHERITS (p);

CREATE INDEX ON b(approved);

CREATE TABLE c (
    c_field NUMERIC,
    PRIMARY KEY (id)
    -- this table is missing the check constraint (for no good reason)
) INHERITS (p);

I now have a long-ish running transaction that locks table a. While this is running, I observe queries on table b that wait for that lock to be released, even though they are guaranteed to not use the data in a. For example:

SELECT TRUE FROM p WHERE id = 12345 AND approved = false AND type_id = 2 LIMIT 1;

Running EXPLAIN on that query shows something like this:

                              QUERY PLAN
----------------------------------------------------------------------------
Limit  (cost= ...)
    ->  Result  (cost= ...)
       ->  Append  (cost= ...)
         ->  Seq Scan on p  (cost= ...)
                 Filter: ((NOT approved) AND (id = 12345) AND (type_id = 2))
         ->  Index Scan using b_approved on b p  (cost= ...)
                 Index Cond: (approved = false)
                 Filter: ((NOT approved) AND (id = 12345) AND (type_id = 2))

I am not sure if I'm reading that correctly, but it appears to me that the query planner thinks it needs to go through the parent table because it does not know whether table c might contain the rows it's looking for. In reality, there's more than one table (in fact, a couple of dozens) like c, which lack the CHECK constraint, so it seems plausible to me that the planner thinks a Seq Scan on the parent might be the most efficient route.

Will I avoid locks on a interfering with an unrelated (in terms of data) query on b if I add proper check constraints to c (the c-like tables all could get one, it would be some work, though)?

Are there other remarks on this kind of setup with regard to locking?

Note that one of the reasons why this partitioning exists is that the application running the queries has parts that are agnostic to what child table it is dealing with, hence the query shown goes to p. Additionally, such a query might affect a small number of tables (3-5) instead of just b, i. e., the query plan shows additional Index Scan lines like the one for b shown here.


texts: Determine which SQL drivers an application uses to connect to SQL server

texts:
I want to find out what SQL driver a third-party application (or any application for that matter) uses to connect to a SQL Server instance in production.  It can be tough to get a fast answer from a vendor and we need to know this so that we can enable TLS 1.2 communication.

I've been told that extended events can solve my problem.  Thank you.


texts:
I have a csv file that I would like to import into a phpMyAdmin database (Wordpress). The CSV file is formatted as shown below:

list-id | post_title    | slider                                  | default-image
5595    | Golden Nugget | http://images.travelnow.com/058883A.jpg | False
5595    | Golden Nugget | http://images.travelnow.com/058883B.jpg | False
5595    | Golden Nugget | http://images.travelnow.com/058883C.jpg | False
5595    | Golden Nugget | http://images.travelnow.com/058883D.jpg | true

The current roadblocks are matching posts with their unique ids' (list-id), and having each row overwriting the last. This is how I would prefer the URLs to be formatted for the slider custom field.

http://images.travelnow.com/058883A.jpg|http://images.travelnow.com/058883B.jpg|http://images.travelnow.com/058883C.jpg|http://images.travelnow.com/058883D.jpg

Any help on importing csv's to sql would be much appreciated.


texts: How can I change the data source of a linked report?

texts:
I'm in a situation where multiple "customers" will be running the same report, but against a different database (with the exact same structure).

What I've done so far is created a generic "Deploy" folder, and deployed all reports to it. I then create "customer-specific" folders, and add linked reports to the source folder. However, I don't seem to be able to make the reports use different data sources (there's no option to change it on the linked report). Is there any way to do this?

Alternatively, how could I handle this situation if I can't use different data sources? I want to avoid re-deploying the same report 10 different times to ten different places only to change the datasource.


texts: Good way to store daily train data in MongoDB

texts:
I am preparing to collect large amounts of information about trains in my country. Each train in my country has a number. I've already collected all of these numbers. There are about 4000 of them.

Now, for each train, I want to collect information about its route and its delays, every day.

Another issue is the following: I have to make a distinction between 2 periods. The period before a new train system was introduced and the period after the new system was introduced.

I used MongoDB for all the stations and for the train-numbers, so I would also like to use MongoDB for the daily routes. My initial thought was the following:

I created a database for the old system (lets call it OldDB) and a database for the new system (lets call it NewDB). In these 2 databases, I created a collection for each train-number in the following form: trainxxx with xxx being the train-number. This results in approx. 4000 collections.

Example: Lets say the date is d and this is before the new system. The train-number is x. The information collected about train x on date d will go in OldDB in collection trainx

Is this a good way to store this? Is there a smarter way to handle this?


texts: Is it a good idea to include a value column (e.g. from measurements) in an index?

texts:
Is it a good idea to include a value column into an index for a table which has many million rows?

For example a measurement table which looks kind of like this:

CREATE TABLE measurements
(
    id bigint IDENTITY,
    parameter_id int NOT NULL,
    measuretime datetime NOT NULL,
    value float NOT NULL,
    PRIMARY KEY CLUSTERED (id ASC)
);

ALTER TABLE measurements ADD FOREIGN KEY (parameter_id)
REFERENCES parameters(id)

When I run a simple avg query the execution plan suggest to create an index which also includes the value column.

SELECT AVG(value)
FROM measurements
WHERE measuretime BETWEEN '2015-01-01' AND '2015-02-01'
GROUP BY parameter_id

CREATE NONCLUSTERED INDEX [idx_measurements_mt_pi_v]
ON [dbo].[measurements] ([measuretime])
INCLUDE ([parameter_id],[value])

I get that it's a good idea to have an index on measuretime and parameter_id, but isn't storing every value in an index a bit over the top?

Won't this result in a massive index? As values are probably always unique because of their precision. Or is just my concept of what include does wrong?

Besides is this really necessary, as the query is running only for about 2-3 seconds with 38.000.000 row (but I guess this could have a much higher impact on a long running production database)?

I'm also thankful for any other performance tips regarding this kind of data query!

EDIT: As suggested I've added the index and ran the query again with SET STATISTICS IO ON and SET STATISTICS TIME ON again

Output:

SQL Server parse and compile time:
   CPU time = 15 ms, elapsed time = 17 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 9 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(12 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'measurements'. Scan count 5, logical reads 24212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1391 ms,  elapsed time = 481 ms.

And here is the new execution plan:


texts: Why TRY CATCH does not suppress exception in trigger

texts:
I have a trigger on a table (source) that data should be copied to the other one (target) in other database. I am trying to implement custom synchronization process for data: I want that target database (table) will be up to date to source database (table). I have 3 hundred tables to synchronize. Some of then have different physical data structure. I cannot use standard approaches of Sql Server (replication, DTS...) because of different data schema and other restrictions (time to implement, environment issues...).
My goal is: this trigger should NOT impact on INSERT, DELETE, UPDATE of records in a source table. I tried so solution:

CREATE TRIGGER dbo.MyTrigger
...
   AFTER INSERT
....
BEGIN TRY
     --RAISERROR('Test error', 16, 2)
END TRY
BEGIN CATCH
  -- nothing
END CATCH

I have inserted RAISEERROR to simulate error. I hoped, try/catch suppressed that error and record was been inserted (deleted, or updated) successfully. Don't. It does not work. I got error:

  An error was raised during trigger execution. The batch has been
  aborted and the user transaction, if any, has been rolled back.

Is it possible to realize my solution in this way. How can I catch and hold (suppress) any error in my trigger?

What sense to use try/catch in trigger if it does not work?


texts: How to check for row lock level when FOR UPDATE is used in PostgreSQL?

texts:
Related to previous question here
From the pg_locks documentation, row level lock information is not available when FOR UPDATE is used in a SELECT statement.
Is there any way to check for row level lock when FOR UPDATE is used in a SELECT statement?
If yes, how can I do so with Postgres 11?
Thank you


texts:
I created a small speed test

from misc.Database import Database
import time

db = Database.getDb()

def main():
    test_db = db.test_db.find({})
    return "done"

if __name__ == '__main__':
    start = time.time()
    for i in range(10000):
        main()
    end = time.time()
    print(end - start)

where db is my pymongo client. When monitoring the mongod log, I realized it opened 2 connections when I run the test. When I run Robo3T, it opened 25 connections to the Mongod. Why is the connection not opened per request? How many connections will open each time you query the database?


texts: How to get super parent from table with self relationship

texts:
i need to create Function that will return the super parent of a given id from a table

the table look like table1 (id, parentId, someData)
the parentId is related to the id

for example i have 1 - 2  - 3  - 4 id of each row

id   parentId
1    null
2    1
3    2
4    3

now when i give the Function 4 as parameter it will return 1

i'm using SQL server 2008

how can this be done


texts: Export set of records with all referenced dependencies on postgres

texts:
I have a postgres database with a complex schema with many references.

Simple example of schema:

Deployments(id, name)
Releases(deployment-id, name, id)
VMs(id, deployment-id)
VmInfo(vm-id, info)

I would like to extract from the db all details about Deployments with a name that starts with 'FOO-*'

Is there a easy way/tool to export all data related to these records without manually crafting a query to connect all keys? I am looking to something similar to the 'ON DELETE CASCADE' to follow all references for me.

In this example I would like to see all Deployments that follow my query, with all related releases, VMs AND VmInfo.


texts: Query 2 tables one with 1 row and the other with multiple rows

texts:
I have a part table:

ID,      Description
ABC-123  ABC
DEF-456  DEF

I have a User Defined Fields table that stores information in a single row.

ID,         DOCUMENTID, STRING_VAL,         DATE_VAL
UDF-000021, ABC-123,    TEXT123,            NULL
UDF-000022, ABC-123,    NULL,               6/10/2016
UDF-000023, ABC-123,    JOHN WORKING ON IT, NULL
UDF-000024, ABC-123,    NULL,               7/1/2016
UDF-000025, ABC-123,    YES,                NULL
UDF-000021, DEF-456,    TEXT123,            NULL
UDF-000022, DEF-456,    NULL,               6/10/2016

I need the information to look like this:

PART ID  DESCRIPTION    REVISION     REVISION DATE   NOTES    MARKUP DATE   IM
  -------------------------------------------------------------------------------
  ABC-123  ABC            TEXT123      6/10/2016       JOHN     7/1/2016      YES
  DEF0456  DEF            TEXT123      6/10/2016

I have looked at joins, pivot, etc. and I guess I just cant seem to wrap my head around it.  Any help is greatly appreciated.


texts: Duplicate tables and procedures in master database

texts:
I've just discovered, that master database contains duplicates of some other databases' tables and stored procedures.

For example, I have a database SomeDB with table justTable in it. For some reason, a copy of this table is in master as well, but with no data.

How is it possible? I'm certain that when I was creating those tables and procedures I was using corresponding databases and not master. They are all present in those databases (as they should be), just with a copy in master as well.

I use Microsoft SQL Server Management Studio 2014 with Redgate additions (source control and stuff).


texts: Mysql - latin1 (with latin2 content) to utf8

texts:
I have a database (ubbthreads) encoded in latin1 with content from latin2 (polish characters).

Since latin2 is compatible with latin1 it looks fine on the website, however I cannot convert it in any way to utf8 (want to import the data to nodebb).

It also doesn't render characters correctly in console mysql as well as in mysql workbench.

I tried numerous of ways to do the conversion and all of this failed.

My last try was to convert mysqldump file first from latin1 to latin2, and I planned to do next conversion from latin2 tu utf8.

However, iconv breaks in first polish character attempt with
illegal input sequence at position 11392

I need some tips how to do it in a correct way.


texts: ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX; JOB_SCHEDULER is biggest occupant

texts:
DBMS_SCHEDULER is occupying all of my SYSAUX tablespace. I ran dbms_scheduler.purge_log which deleted 100 million rows from dba_scheduler_job_run_details, however v$sysaux_occupants and the data file size remain unchanged. Is there some additional action I need to take to clear the SYSAUX tablespace of DBMS_SCHEDULER generated data?

Attempting to insert a single row into a newly created table in my regular tablespace fails with:

ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX

I can see that JOB_SCHEDULER is taking up 92.4% of the SYSAUX tablespace by querying v$sysaux_occupants:

SELECT
    occupant_name,
    round (sum(space_usage_kbytes) * 100 / sum (sum(space_usage_kbytes)) over (), 2) Pct
FROM v$sysaux_occupants
GROUP BY occupant_name
ORDER BY 2 desc NULLS LAST
;

I originally had over 100 million rows in dba_scheduler_job_run_details.

Yesterday, I ran the purge command (which took 3.5 hours):

BEGIN
    dbms_scheduler.purge_log;
END;
/

Today, dba_scheduler_job_run_details has less than 1K rows.

However, the query on v$sysaux_occupants is unchanged; today it still says JOB_SCHEDULER is occupying 92.4%. Likewise querying my data file sizes show that SYSAUX is still maxed out:

select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_data_files d,
    v$datafile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

It seems like I have not actually deleted the space consumed by DBMS_SCHEDULER.

Is there some step I am missing to clean up all the space consumed by DBMS_SCHEDULER?


texts: ACID-compliance of MySQL master-master replication?

texts:
I've created two MySQL 5.1 servers, replicating off each other (log-bin) in a low latency environment (different AZs within an EC2 region). All tables that are being replicated are using the InnoDB storage engine.

It appears to work as expected in a number of scenarios I have tested (not brimming with confidence, though), but I am worried that I cannot find any answers on whether this setup guarantees ACID for both nodes on transactions made to either node.

If not, are there steps I can take to meet this requirement?

I've seen various bits and pieces around the mysql.com site such as:

  ... For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1, sync_binlog=1 ...

but I find it rather worrying that I cannot find a definitive answer (or even guide) on the matter.


texts: Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

texts:
Background Information:

I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server.
I have to migrate a lot of Rows (not all), spread over a lot of tables (not all).
The migration will be done table by table.
That means I have written a C# Programm and a lot of Statements to select the needed Records from the Source-DB (proxy Tables) and insert them in the target db/tables.

I have droped all Primary Keys, all foreign keys and all indexes.
This was done to increase performance, to facilitate the table by table migration and to start quicker on developing the Programm.

My Problem:

I need a way to select all Foreign Keys of one Owner, generate their "Create"-Statements and save those to a Table so I can recreate them once the migration is over. (Same Problem for PKs and Indexes)

Currently the fastest way is to use Sybase Central, Click on Indexes, order by Owner/Type, select all foreign Keys, Ctrl-C and Ctrl-V into a Editor. That will give me all "Create"-Statements for the selectet Objects.

I would rather have a script that could generate all "Create"-Statements, but seeing as most information about Foreign Keys is spread over 11 Systemtables/-views I am not able to build a Statement that will give me all Informations that I need.

I also have tried using the Unload-Tool of Sybase Central which will give me all Foreign-Key Statements. But seeing as that is also a long and manual way of doing it, it is not an improvement.

Does anyone have a better way or has anyone maybe written a Script that strips a Database of all foreign Keys to rebuild at a later point in time?


texts: Should the Redo log on a production MySQL server be configured to another location?

texts:
Concerning the innoDB storage engine, should the innodb_log_group_home_dir option (The directory path to the InnoDB log files) be configured to another disk, other than the default directory, in order to separate the log I/O activity from the actual data files?

Are there advantages, like in other DBMS, to put the data directory on a RAID5 storage volume and the redo log on a RAID1?


texts: How can I implement a viewed system for my website's posts?

texts:
Here is my current structure:

// posts
+----+--------+----------+-----------+------------+
| id | title  | content  | author_id | date_time  |
+----+--------+----------+-----------+------------+
| 1  | title1 | content1 | 435       | 1468111492 |
| 2  | title2 | content2 | 657       | 1468113910 |
| 3  | title3 | content3 | 712       | 1468113791 |
+----+--------+----------+-----------+------------+

// viewed
+----+---------------+---------+------------+
| id | user_id_or_ip | post_id | date_tiem  |
+----+---------------+---------+------------+
| 1  | 324           | 1       | 1468111493 |
| 2  | 546           | 3       | 1468111661 |
| 3  | 135.54.12.1   | 1       | 1468111691 |
| 5  | 75            | 1       | 1468112342 |
| 6  | 56.26.32.1    | 2       | 1468113190 |
| 7  | 56.26.32.1    | 3       | 1468113194 |
| 5  | 75            | 2       | 1468112612 |
+----+---------------+---------+------------+

Here is my query:

SELECT p.*,
       (SELECT count(*) FROM viewed WHERE post_id = :id) AS total_viewed
 FROM posts p
WHERE id = :id

Currently I've faced with a huge date for viewed table. Well what's wrong with my table structure (or database design)? In other word how can I improve it?

A website like stackoverflow has almost 12 million posts. Each post has (on average) 500 viewed. So the number of viewed's rows should be:

12000000 * 500 = 6,000,000,000 rows

Hah :-) .. Honestly I cannot even read that number (btw that number will grow up per sec). Well how stackoverflow handles the number of viewed for each post? Will it always calculate count(*) from viewed per post showing?


texts: PAGELATCH_EX occures in update query with UPDLOCK, READPAST

texts:
We have a queue organized with a table on MS SQL Server 2014:

CREATE TABLE [queue].[Messages](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Status] [tinyint] NOT NULL,
    [Data] [nvarchar](max) NOT NULL,
    [CreationDateUtc] [datetime] NOT NULL
)

CREATE NONCLUSTERED INDEX [IX_Messages_Status] ON [queue].[Messages]
([Status] ASC)

Stored procedure to send message:

ALTER PROCEDURE [queue].[SendMessage]
    @MessageBody NVARCHAR(MAX),
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [queue].[Messages]
    (
        Data,
    )
    VALUES
    (
       @MessageBody,
    )
END

And a stored procedure to receive messages:

ALTER PROCEDURE [queue].[ReceiveMessage]
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE TOP (1) m
    SET
       m.Status = 2,
       m.StartDateUtc = GETUTCDATE()
    OUTPUT
       INSERTED.*
    FROM [queue].[Messages] m WITH (UPDLOCK, READPAST)
    WHERE m.Status = 1
END

This stored procedure executes every second to check new messages in queue.
Very often we get blocks (PAGELATCH_EX) on this query even if the queue is empty:

First idea was - diff-backups, but they are scheduled at different times:

Other queries don't make blocks so often.

Am I right that this is due to UPDLOCK and READPAST hints?
Are there other reasons?


texts: SQL Server Error - The connection is broken and recovery is not possible

texts:
I have a .NET application that connects from A Windows 10 client to an SQL Server 2016 Express database running on Windows Server 2012 R2. Whilst a connection is successfully established, it is frequently throwing the following error on subsequent connection attempts:

"The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts."

I have researched this and have not been able to get to the bottom of why this is suddenly occurring after the system has been stable for months.


texts:
I'm designing a schema which includes the concept of availability rules. The rules are periods of a day when a linked entity is available. This can be the opening hours of a business, the working hours of a staff member, or the availability hours of a specific service. This schema needs to allow for multiple periods on the same day. The rules aren't shared across entities, a business has one group of rules, and each of the other entities are the same.
My first run through this, gave me this schema, but the fact that availabilityGroup doesn't actually contain any data makes me feel like this could be done differently.
CREATE TABLE `business` (
  `id` integer PRIMARY KEY,
  `name` varchar(255),
  `availabilityId` integer,
  CONSTRAINT `FK_business_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `staff` (
  `id` integer PRIMARY KEY,
  `availabilityId` integer,
  `name` varchar(255),
  CONSTRAINT `FK_staff_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `service` (
  `id` integer PRIMARY KEY,
  `availabilityId` integer,
  `name` varchar(255),
  CONSTRAINT `FK_service_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `availabilityGroup` (
  `id` integer PRIMARY KEY
);

CREATE TABLE `availabilityRule` (
  `id` integer PRIMARY KEY,
  `availabilityGroupId` integer,
  `day` string,
  `startTime` time,
  `endTime` time,
    CONSTRAINT `FK_availability_rule_to_availability_group` FOREIGN KEY (`availabilityGroupId`) REFERENCES `availabilityGroup` (`id`)
);

Another possible solution could be to make use of single table inheritance with something like this, which is supported by the ORM we're using too https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/inheritance-mapping.html#single-table-inheritance
CREATE TABLE `singleAvailabilityRulesTable` (
  `id` integer PRIMARY KEY,
  `discriminator` enum('business','staff','service'),
  `entityId` integer,
  `day` string,
  `startTime` time,
  `endTime` time
);

Any thoughts?


texts: How to get CPU usage by database for particular instance?

texts:
I have found the following queries to detect the CPU usage by database, but they are showing different results:

WITH DB_CPU_Stats
AS
(
    SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
      SUM(total_worker_time) AS [CPU_Time_Ms]
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY (
                    SELECT CONVERT(int, value) AS [DatabaseID]
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
    GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
       DatabaseName,
        [CPU_Time_Ms],
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
--WHERE DatabaseID > 4 -- system databases
--AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

The above query tells that the issue is with one of my database (almost 96%).

And the query below tells that the issue is with the master and the distribution databases (about 90%):

DECLARE @total INT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
    join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
--WHERE sp.status = 'runnable'
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc

I have checked thet the sys.sysprocesses is decpreated. Does this mean that the results from the second query are wrong?


texts: Can I assign an index to an exploded array?

texts:
I'm doing a LATERAL EXPLODE on an array in Hive, is there a way of reliably assigning a row number based on the array element? It looks like calling row_number() on the results of the LATERAL EXPLODE works, but I don't know if that is dependable.

It does seem to be in practice. We're doing this now and have never encountered a case where the elements have been enumerated incorrectly.


texts:
I have run the below code:

execute sp_configure 'external scripts enabled';

and it is giving me the output as:

name                    minimum maximum config_value    run_value
external scripts enabled    0    1       1              1

I have also started the launchpad services, still when I am running the below code:

exec sp_execute_external_script @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1+1 as SUMOutput'
with result sets (([SUM Output] int not null));
go

It is throwing the error:

  Msg 39011, Level 16, State 1, Line 7 SQL Server was unable to
  communicate with the LaunchPad service. Please verify the
  configuration of the service.   Msg 11536, Level 16, State 1, Line 7
  EXECUTE statement failed because its WITH RESULT SETS clause specified
  1 result set(s), but the statement only sent 0 result set(s) at run
  time.

I have SQL Server 2016 editon and the SQL Server Management Studio is 2017.
i have the following snippet of my services.


texts: Why some rows are missing in my recursive CTE?

texts:
These are my 2 tables id_hierarchy and hierarchy_link:

CREATE TABLE id_hierarchy
(
    id integer NOT NULL,
    hierarchy integer
);
insert into id_hierarchy values (11,2),(22,3),(44,5),(77,8),(170,11),
(190,13),(240,18),(255,20);

CREATE TABLE hierarchy_link
(
    hid integer NOT NULL,
    parent_hid integer
);
insert into hierarchy_link values (1,0),(2,1),(3,2),(5,3),(8,5),(11,8),
(13,11),(15,13),(18,15),(20,18);

With id_hierarchy.hierarchy=hierarchy_link.hid

For the below CTE query:

with recursive rt1 as(
    select id,hid,parent_hid||'->'||hid as str
    from rt2
    where rt2.parent_hid=1
    union
    select t.id,t.hid,s.str||'->'||t.hid as str
    from rt2 t
    inner join rt1 s on (s.hid=t.parent_hid)
),
rt2 as (
    select id, hid, parent_hid, hierarchy
    from hierarchy_link h
    inner join id_hierarchy u on (u.hierarchy=h.hid)
)
select id, hid, str as hierarchy
from rt1
order by 1

I am getting this result:

id;  hid; hierarchy
11;  2;   "1->2"
22;  3;   "1->2->3"
44;  5;   "1->2->3->5"
77;  8;   "1->2->3->5->8"
170; 11;  "1->2->3->5->8->11"
190; 13;  "1->2->3->5->8->11->13"

Rows 240,18 and 255,20 are missing from id_hierarchy.

Where is my mistake?


texts:
I upgraded to MySQL 8.0.13 and tried to add a new field using ALGORITHM=INSTANT but got the following error:

  Error Code: 1845. ALGORITHM=INSTANT is not supported for this
  operation. Try ALGORITHM=COPY/INPLACE

Then I recreated the same table in MySQL 8.0.13 and tried to add the same field using ALGORITHM=INSTANT and it works!

Will this new feature of MySQL (ALGORITHM=INSTANT) only work on newly created tables in the upgraded version?


texts:
I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other fields to filter by that I left out):

SELECT * FROM table WHERE NOT (id IN (ids here)) LIMIT 18;

However, since I have about 150,000 rows in my table, and the amount of ids can be even up to 30,000, this is unbearably slow.

I haven't worked a lot with databases before, so I'm probably doing this completely wrong. Is there any better way to achieve this?

I'm running PostgreSQL 9.3.9

Any help is greatly appreciated!


texts: Database with FILESTREAM enabled gives Error 3056 during backup

texts:
I have a SQL 2014 database enabled for FILESTREAM and I am getting errors 3056 during backup.

The message is

DESCRIPTION:    The backup operation has detected an unexpected file in a FILESTREAM container. The backup operation will continue and include file 'F:\Resources\Staff pictures\Outlook\xxx.jpg'.

Any ideas on how to resolve this greatly appreciated.

Regards

James


texts: Printing comments or statements when executing script to a output file using TOAD/ORACLE

texts:
Coming from a SQL SSMS environment, I would like to duplicate their Print Command which allows you to print commands or variables while executing a query. I am looking for this feature to help identify particular sections of the script during the execution.

For instance we have a .sql script that runs in the evening that checks for discrepancies on particular inventory numbers. These inventory numbers have lets say a store_id of 1, 2, 3, 4, 5.

The script has a select statement attached to each of these store_id. I would like to be able to place a PRINT statement for the select statement of Store_ID 1 so I can see the output.txt file and see the heading 'Store_id 1" then the inventory numbers, then see heading "Store_id 2" etc...

in MSSQL SSMS it would like something like this

PRINT 'Searching Inventory Discrepancies at Store 1'
Select * from table1 where store_id = '1' etc...

PRINT 'Searching Inventory Discrepancies at Store 2'
Select * from table1 where store_id = '2' etc...

If I need to clarify please advise, thank you!


texts: select all tables from dba_table except list

texts:
I'm missing something in my query. I want to select all tables from dba_tables except a list of tables.

For example, show all except HR.DEPT, SCHEMA.TAB

dba_tables:

OWNER    TABLE_NAME
------   ----------
HR       DEPT
HR       TEST
SCHEMA   TAB
SCHEMA   NAMES
TEM      TBA

Result after query:

OWNER    TABLE_NAME
------   ----------
HR       TEST
SCHEMA   NAMES
TEM      TBA

Query I tried:

select owner, table_name from dba_tables
where (owner != 'HR' and table_name != 'DEPT' )  OR (owner != 'SCHEMA' and table_name != 'TAB' )


texts: Exporting Access DB to SQL Server: unexpected EOF?

texts:
How do I correctly export a table from an old Access .mdb to SQL Server 2012?

In Access I do: right click on table, export as text file and use comma or tab separator.
My line endings are CRLF as seen from Notepad++

Then when I try to import into my RDS SQL Server with

bcp dbname.dbo.tablename in C:\AllModels.txt -n
    -S servername -U username -P password -b 10000

I am getting:

  Starting copy...
  SQLState = S1000, NativeError = 0
  Error = [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in BCP data-file

help help


texts: how to sucessfully revoke view any definition (or any other server permissions)?

texts:
I had to grant a server permissions to an active directory group, in order to solve a LIVE Bug:

GRANT VIEW ANY DEFINITION TO [mycompany\webDevSeniors]
GO

after the permission was no longer needed I revoked it:

revoke VIEW ANY DEFINITION TO [mycompany\webDevSeniors]
go

this didn't seem to revoke the permission on all databases
so I have tried it in a different way:

use master
go
revoke VIEW DEFINITION ON SCHEMA::[dbo] to [mycompany\webDevSeniors]
go

still, when I use one of my procedures to check which permissions are active in a specific database, it shows the "VIEW DEFINITION"

sp_getlogindbpermissionsX @db='ATPayment',@login='MYCOMPANY\webDevSeniors'

as you can see on the picture below, the permission is not there at server level:

Problem Solved:

At the end, this is how I solved this problem:

I downloaded the sp_foreachdb stored procedure by Aaron Bertrand

And then I run the following command on my server:

sp_foreachdb @command='use ?; REVOKE VIEW DEFINITION ON SCHEMA::[dbo] to [mycompany\webDevSeniors]'
   --         ,@print_dbname = 1
            --,@print_command_only = 1


texts: How should an object that can reference an object of multiple types be modeled in a relational schema?

texts:
Apologies for the obscure question, it may make more sense with a concrete example:

In my application, I may create a portfolio that contains a set of projects. However, I may also add a 'sub-portfolio' nested within a portfolio (which then may contain projects). Currently, I have my schema drawn out the following way:

+------------+    +-----------------------+    +------------+
| Portfolios |    | Portfolio_project_map |    | Projects   |
+------------+    +-----------------------+    +------------+
|   port_id  |    |   parent_id           |    |   proj_id  |
+------------+    +-----------------------+    +------------+
|            |    |   child_id            |    |            |
                  +-----------------------+
                  |   child_is_portfolio  |
                  +-----------------------+

This seems extremely ugly. What is the proper way of modeling such a relationship in a relational database? Or is there really not one, and such details should be closely enforced in the business logic? Is it even possible to specify foreign key constraints on the Portfolio_project_map such that child_id references either port_id or proj_id?

Also, what exactly is such a problem called? I found 'one-to-either' referenced in a couple places, but I have a feeling that's not completely correct?


texts: Is it possible to reset SA password without stopping SQL Server Service?

texts: I lost a password for sa account. Now I want to reset SA password and at the same time not to restart SQL Server Service or going into single-user mode. I would like to mention that I am windows administrator, so I can run command line as admin. Is it possible?


texts: Mysql 5.1.X How to upload data from dump file without the partitioning commands?

texts:
I need to upload data from a dump file (large one) that includes partitioning info into a database that doesn't support partitioning.

How can I tell the mysql command line to ignore the partitioning information in the dump file?

Notes:
The dump file isn't created by me. Its an auto generated backup so creating it without partitions is not an option.

The file is too large to edit manually. But adding a line to it is an option.

Thanks !


texts:
I am using postgres 9.5, and I was wondering if postgres allows for any type of subnetting or calculations on IP addresses, specifically IPv6. I am aware they have the cidrformat for storing data like IpV4 and Ipv6 formats. However, can I do anything like find out how many client IP's are in the subnet stored in the DB?

For example. If I stored the IPv6 address of 2001:dd8::/64, could I find out how many host IPs there are (18446744073709551616, according to some calculators).

My guess is that the answer is NO, postgres does not support this.


texts: Can i Make Index for all columns(No write operations)?

texts:
Few days I just learned indexes.I my New app(running on node js) There will no write operation.And my server has 500mb of ram.Can I set Index to all of My column.I almost have 500K rows.My query is dead simple as Like this

SELECT * FROM users ORDER BY RAND() LIMIT 1

Limit may vary upto 10k According to users need.Right Now I am getting 0.6s In my 8Gb ram laptop.What would Happen if it is on 500Mb ram Server.Is it worth for Making Indexes in all table


texts:
I have the following schema in SQLite:

CREATE TABLE calendars
  (
     id    INTEGER PRIMARY KEY,
     title TEXT NOT NULL
  );

CREATE TABLE entry_types
  (
     id          INTEGER PRIMARY KEY,
     red         INTEGER NOT NULL CHECK(red BETWEEN 0 AND 255),
     green       INTEGER NOT NULL CHECK(green BETWEEN 0 AND 255),
     blue        INTEGER NOT NULL CHECK(blue BETWEEN 0 AND 255),
     description TEXT NOT NULL
  );

CREATE TABLE entries
  (
     id            INTEGER PRIMARY KEY,
     calendar_id   INTEGER NOT NULL,
     entry_type_id INTEGER NOT NULL,
     date          TEXT NOT NULL,
     FOREIGN KEY(calendar_id) REFERENCES calendars(id),
     FOREIGN KEY(entry_type_id) REFERENCES entry_types(id),
     UNIQUE(calendar_id, date)
  );

CREATE TABLE calendars_entry_types
  (
     calendar_id   INTEGER NOT NULL,
     entry_type_id INTEGER UNIQUE NOT NULL,
     FOREIGN KEY(calendar_id) REFERENCES calendars(id),
     FOREIGN KEY(entry_type_id) REFERENCES entry_types(id)
  );

Each entry_type can only be used for one particular calendar, which is encoded as the UNIQUE constraint in the calendars_entry_types table, restricting that many-to-many pattern to only one-to-many.

  SIDEBAR: Originally I defined the entry_type table like this:

CREATE TABLE entry_types
  (
     id          INTEGER PRIMARY KEY,
     -- SNIP color & description columns
     calendar_id INTEGER NOT NULL,
     FOREIGN KEY(calendar_id) REFERENCES calendars(id)
  );

  This obviated the need for the calendars_entry_types table but then the calendar_id data was in two places: in the row in entries and in the row in entry_types, so I decided to change. Perhaps this was the wrong decision? I needed to keep the calendar_id in the entries table in order to keep the UNIQUE(calendar_id, date) constraint. /SIDEBAR

This is all well and good, but I now have nothing preventing a situation like

INSERT INTO calendars (id, title) VALUES (1, 'test1'), (2, 'test2');
INSERT INTO entry_types (id, red, green, blue, description) VALUES (1, 0, 0, 0, 'entrytype1');

-- entry_type id 1 goes with calendar id 1
INSERT INTO calendars_entry_types (calendar_id, entry_type_id) VALUES (1, 1);

-- this shouldn't work... calendar 2 doesn't go with entry_type 1
INSERT INTO entries (calendar_id, entry_type_id, date) VALUES (2, 1, 'entry1');  -- (I know that's not a date but this is just an example...)

Is it possible to write a constraint that would prevent the insertion of rows to the entries table that match this query?

SELECT id
FROM   entries
WHERE  ( calendar_id, entry_type_id ) NOT IN (SELECT calendar_id,
                                                     entry_type_id
                                              FROM   calendars_entry_types);

If not, is there a better or alternative way to design my schema that takes the "each entry type is only valid for a certain calendar" constraint into account?


texts: are there possible draw backs when setting 'dbo' to users who have default schema as null?

texts:
as part of a bigger operation I have been putting together this script below that basically generates a create user script for each user of the current database.

When the user has a default_schema it comes up in the generated script.

   select  permission_order=110
       ,permission_type = 'User Creation'
       ,db = db_name(),
         _login=[master].[sys].[server_principals].[name] collate Latin1_General_CI_AS,
         role_= null,
         obj=null,
        Permission= null,
        [script]=
'USE ' +  quotename(db_name()) + ';' +  CHAR(13) +
'IF NOT EXISTS (SELECT * from sys.sysusers WHERE name=' +  '''' + [sys].[database_principals].[name] + '''' + ')' + CHAR(13) +
'   CREATE USER ['
 + [sys].[database_principals].[name] + ']' + ' FOR LOGIN [' + [master].[sys].[server_principals].[name] collate Latin1_General_CI_AS + ']'
+ CHAR(13) +
ISNULL(' WITH DEFAULT_SCHEMA = '+ QUOTENAME([sys].[database_principals].default_schema_name collate Latin1_General_CI_AS),'')
+ CHAR(13)
FROM [sys].[database_principals]
INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] collate Latin1_General_CI_AS =[master].[sys].[server_principals].[name] collate Latin1_General_CI_AS
WHERE [master].[sys].[server_principals].[type] in ('U', 'G', 'S')

these lines are examples of a create user script:

USE [MEME]; IF NOT EXISTS (SELECT * from sys.sysusers WHERE name='meme')  CREATE USER [meme] FOR LOGIN [meme]  WITH DEFAULT_SCHEMA = [dbo]
USE [MEME]; IF NOT EXISTS (SELECT * from sys.sysusers WHERE name='company\Developers')  CREATE USER [company\Developers] FOR LOGIN [company\Developers]

Is there anything I should be concerned with, in case I would like to set dbo to all my users that currently have their default_schema as null?

for that end I would use the following script:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = N'';
SELECT @cmd = @cmd + N'ALTER USER ' + QUOTENAME(sdp.name) + ' WITH DEFAULT_SCHEMA = [dbo];' + CHAR(13)
 FROM sys.database_principals sdp
INNER JOIN sys.syslogins ss
        ON ss.sid = sdp.sid
 WHERE 1=1
  AND sdp.[type] in ('U', 'G', 'S')
  AND sdp.default_schema_name IS NULL

PRINT @cmd;
--EXEC sp_executesql @cmd;

just to complement, below is a good article about the default_schema of the users, and suggestions as how to change them, however my question here has not been addressed there.

Script to Set the SQL Server Database Default Schema For All Users


texts: Selecting the most specific match from one table

texts:
So let's say there is a simple table for dogs:

CREATE TABLE IF NOT EXISTS dogs (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  breed char(20) NOT NULL,
  color char(20) NOT NULL,
  special_skill char(20) DEFAULT NULL
);

The real example of this table would have foreign key references for breed and color to an enum table and the dogs table would be pre-populated with values that match the cartesian products of breed, color.
So there is the guarantee that there will always be a record for any combination of breed and color.

And some simple sample data:

1, 'American Bulldog',  'White', 'Runs fast'
2, 'American Bulldog',  'White',  Null
3, 'American Foxhound', 'White',  Null
4, 'American Foxhound', 'Black',  Null
5, 'American Bulldog',  'Black',  Null

How would one go about building a query that would find the most specific match possible by provided query filters?

Say a person wants to find an American Bulldog that is White and Runs fast. They will get a record with Id = 1 from our current table. Another person looking for the same kind of dog but with a 'Black' color instead would find a record with Id = 5 even tough the person also specified to find a dog that Runs fast.

So in essence here would be some pseudo filters and results:

find by ('American Bulldog',  'White', 'Runs fast')
Returns id 1

find by ('American Bulldog',  'White')
Returns id 2

find by ('American Bulldog',  'Black', 'Runs fast')
Returns id 5

During my quest to find a solution to this I was only able to come up with some very hackish queries with a case statement in it and explicitly ordering results in certain ways to be able to pick the top most result. But I am curious if there is a simple version (uses basic SQL features not some case) possible for this that could be done using some DSL in a programming language and not falling back to plain SQL queries.


texts: A function that can be used directly in SQL cannot be used from a stored procedure

texts:
In the Teradata 17 instance that I work with, there is external function hash_md5 that Teradata provides. I can run all tests of this function suggested by Teradata, as long as I do it in the studio. But if I try to use this function from a stored procedure then I get the following error:

Failed [3523 : 42000] DATAINSERT:An owner referenced by user does not have EXECUTE FUNCTION access to user1.hash_md5.

I was told that this user has been granted ALL on this database, therefore access should be there. I gather that since Teradata 13 there is some "security" thing on code objects, but it is invisible to me and I have no idea what is missing. What do I have to do to allow my stored procedure to call hash_md5?
We (as in the "owner of the business, the system, and the data in it") do not care about security. We want everyone to be able to call any function and stored procedure in this database. In short, we do not have any concerns for security, and we are only concerned with the necessity to successfully call this function from any stored procedure. This is not negotiable.


texts: database design for storing individual users' items

texts:
I am trying to make an application which users can have their own account
For the account, I have an single table and now I would like to add a function which
allows users to items in to their account

For example, user A want to add item A ,B ,C ,D to his account,
then on the database should have 4 rows of new data
Also there are no limit on the maximum number of item users can add.

My question will be how the item should I store on the database.
In a single table or a new table for each of the users?
Will the data retrieving time be very slow if there are lots of users with items?
And will many tables in a single database affect my works?


texts: MySQL - How many slaves can be handled by a master?

texts:
Can someone please put some highlights on this, How many slaves can be handled by Master.

I have 100+slaves for a single master, how this process will work?


texts: Deduplication/normalization of string column on insert

texts:
I have a database of text tuples. Imagine e.g. full file paths + standard comments. Then you dump large file tree and generate comments for files. A file can have lots of comments, but their text repeats exactly. The DB gets into several GBs size, so I think it's quite big for sqlite standard, I'm a noob here though.

Anyways, since strings in individual columns do repeat quite a lot, but combinations are original, I thought I can have tables for distinct original strings, and combinations just as tuples of foreign keys (I believe that's called normalization), then a VIEW, preserving all API, for reading.

The question is, can I implement deduplication mechanism on the DB side for insert?

I thought of something like INSERT on (text_column1, text_column2, text_column3) and then write some kind of INSTEAD OF INSERT trigger, that would split it into 3 INSERT IF NOT EXISTS commands + 1 insert into relation table. But I don't think it is even possible to have different "interface" and "storage" schema. I certainly failed to write it.

I have supplementary questions which tightly related (thus not worthy of separate entries, I believe):

Maybe sqlite3 already does string deduplication behind the scenes? (I doubt it, since mutable strings would complicate things a bit. Nothing undoable though.)
If it's hard then maybe it's a bad idea for some reason?

If it helps my data is read-only once inserted.

I've read:

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?
Implement a deduplication trigger in Oracle
Automatic deduplication (normalization) of strings in MySQL

But they regard different SQL systems, and don't really answer general question. I gather that this is not a popular problem and solution.


texts: SGA size not decreasing after reducing memory_target (Oracle 11.2.0.1)

texts:
I have an Oracle 11.2.0.1 database using dynamic memory allocation with parameter MEMORY_TARGET. When I decrease it with SCOPE=BOTH, the size of the SGA doesn't seem to decrease along with it. I have tested this on multiple systems. Here is an example

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
memory_target                        big integer 820M
SQL> sho sga

Total System Global Area  855982080 bytes
Fixed Size                  2180544 bytes
Variable Size             520096320 bytes
Database Buffers          327155712 bytes
Redo Buffers                6549504 bytes
SQL> alter system set memory_Target=720M scope=both;

System altered.

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2180544 bytes
Variable Size             520096320 bytes
Database Buffers          327155712 bytes
Redo Buffers                6549504 bytes
SQL> show parameter memory_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
memory_target                        big integer 720M
SQL>

However, after I bounce the instance, the SGA seems to size itself appropriately.

SQL> sho sga

Total System Global Area  751595520 bytes
Fixed Size                  2179536 bytes
Variable Size             415239728 bytes
Database Buffers          327155712 bytes
Redo Buffers                7020544 bytes
SQL>


texts: Getting the entire records from Two tables for Count purposes SQL

texts:
I'm just wondering if anyone could help on how to bring two columns from two tables together in one table.

These table have id's but they are not linked although they have other columns that I can join them on. But for the sake of getting the count of individual ID's I'm not sure if inner or outer join will give me the result that I want.

Here are the tables:

TABLE A

ID
1
2
3
4
5

TABLE B

2
4
5
7
8
9
10
11
12

I should be able to retrieve:

select count(a.id) from the combined_table
select count(b.id) from the combined_table


texts: What differences are there with SQL Server 2017's packaging of `Binn` on Linux and Windows?

texts: Knowing that the Binn is packaged differently on Windows and Linux, I'm wondering what differences there are between the two? Do they come with different utilities? On Linux Binn is distributed in a sfp archive and Windows it's just a directory.


texts: I'm creating a inventory management app in rails. I have an User Table and a Recipe table, there is also some tables with types of material (pens, sharpies, paper, and so on). Because there is a lot of types of paper, pens and other things, and every user may want a diferent pen, but a pen I think that it will be better to have a type of article and have in the Recipe table the Quantity of articles needed. So when an user checks his list of articles there will be Pens: 2  and maybe some options and a search for more info of an specific pen. Like a ToDo list. But I having trouble to figure out the relationships of the tables for this purpose. Right now im designing the database. I was thinking in use MySQL in Rails.


texts: Sql server 2008 filestream maximum number of rows

texts:
We are using filestreams in Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) and Windows server 2008 to store millions of files. Since there are millions of files all of them are grouped into 100 filegroups and inserted sequntially one after another filegroup. Now we have around 15K files in each filegroup and expected to grow to 20K soon. so we want to know is there any maximum limit of rows that a filegroup can have to give optimum performance or is there any maximum on the operating system side for optimum performace as we are storing or going to store around <20K files in single folder?
Any suggestion to the right resources will also be very helpful.
I have a link to msdn blog which states that

4.Check if FILESTREAM directory containers do not have more than 300,000 files individually, as NTFS performance degradation may be an
issue especially when generation of 8.3 filenames is enabled.

Msdn Blog Link

Thanks.


texts: perform non locking select when updating another table

texts:
We need to perform the following (periodic) query in mariadb:

update A set status='INACTIVE' where status='ACTIVE' and a.b_id in
(select id from B where ... LOCK IN SHARE MODE);

Unfortunately it locks table B for updates and causes deadlock errors for  other transactions on B:

MySQL error code: 1213, SQLState: 40001, Msg: Deadlock found when trying to get lock;
try restarting transaction query: "UPDATE B set ... where id = ..."

We would like to have a lockless, non consistent isolation in the inner select. Is it possible? We perform the query periodically using EVENTs. Can we change the isolation level inside an event?

Our binlog format is binlog_format = mixed


texts: Direct, simple SQL query results in "ORA-01406: fetched column value was truncated"

texts:
All the information I've seen on this issue is always in regards to running a query via some other utility (e.g. TOAD); however, I am seeing this issue when I simply try to select * from v$asm_disk; on the SQLPLUS command line. I am running the query as SYSDBA  and it fails. If I log in to the ASM instance as SYSASM, I am able to run select * from v$asm_disk;. I have two boxes with identical OS and kernel versions, that were set up in the same manner.
Ultimately the issue comes from not being able to query the underlying fixed view which is used to create this view, X$KFDSK_STAT. On one server, as SYSDBA I can query that view, and on the other, I cannot.
Has anyone seen this before?
Thanks


texts:
I am using MongoDB-2.6.1. Is there any way to limit the RAM memory usage?

For example: Out of 4 GB RAM, let MongoDB use till 3 GB (<= 3 GB).


texts: I have a database in SQL Server 2008, with the final data table having about 2 million rows. The development team is implementing a new application and they have suggested changing the column name to reflect what the business likes it to be. They already have their application built in the test environment with the new column names. Will there be a performance penalty if I have them connect to my database via a view with column name aliased to match the column names used by the application? I need to provide a justification for not to change the column names in the application. Any help will be appreciated. Thanks.


texts: Regarding create index and lock word in SQL statement used for SQL Server

texts:
This way someone creates an index.

CREATE TABLE [dbo].[QueueSlow](
[QueueID] [int] IDENTITY(1,1) NOT NULL,
[QueueDateTime] [datetime] NOT NULL,
[Title] [nvarchar](255) NOT NULL,
[Status] [int] NOT NULL,
[TextData] [nvarchar](max) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_QueueSlow] ON [dbo].[QueueSlow]
(
[QueueID] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX_QuerySlow] ON [dbo].[QueueSlow]
(
[QueueDateTime] ASC,
[Status] ASC
)
INCLUDE ( [Title])
GO

I have question that when creating non-clustered index like

CREATE NONCLUSTERED INDEX [IX_QuerySlow] ON [dbo].[QueueSlow]
(
[QueueDateTime] ASC,
[Status] ASC
)
INCLUDE ( [Title])
GO

so what is the meaning of INCLUDE([Title]) - does it mean a non-clustered index will be created on Title column?

Here is a SQL statement with lock keyword.

declare @Batch table (QueueID int, QueueDateTime datetime, _
Title nvarchar(255), TextData nvarchar(max) )

insert into @Batch
select Top (@BatchSize) QueueID, QueueDateTime, Title, TextData from QueueSlow
WITH (UPDLOCK, HOLDLOCK)
where Status = 0
order by QueueDateTime ASC

When inserting data into a table variable then why would anyone use lock like

WITH (UPDLOCK, HOLDLOCK)

What is the meaning of WITH (UPDLOCK, HOLDLOCK) ??

Is this statement WITH (UPDLOCK, HOLDLOCK) compatible with SQL Server 2000/2005 ?

Looking for a discussion. thanks


texts: Postgres Server access restricted to only one database from list of databases

texts:
I am trying on the server side to restrict a user connecting on LAN to seeing and accessing only 1 database from the list of databases I have in my system.

How can I achieve this?

I have tried

host    db_name     all       (connecting_ip)/32          krb5

host    db_name     all       (connecting_ip)/32          ident

host    db_name     all       (connecting_ip)/32          crypt

host    db_name     all       (connecting_ip)/32          trust

These parameters have been changed in the pg_hba.conf file, and even after that, it still does not work.

Please help with a solution on how to fix this issue so that any user accessing a database in a server with a list of databases can see and access only the required database.


texts: How to prevent a column from repeating in the final result when using the LISTAGG function

texts:
I have a table like this:
CUST_NUMBER | CUST_NAME | CUST_PROFESSION
------------------------------------------
   1        |   Mike    |    Teacher
   1        |   Mike    |    Engineer
   2        |   Jack    |    Driver
   3        |   Charlie |    Swimmer
   4        |   Negin   |    Engineer
   5        |   Zahara  |    Manager

In want to use the LISTAGG function to produce this result:
  CUST_NUMBER | NAME_PROFESSION
  -----------------------------
       1      | [Mike:Teacher,Engineer]
       2      | [Jack:Driver]
       3      | [Charlie:Swimmer]
       4      | [Negin:Engineer]
       5      | [Zahara:Manager]

What I've written so far is the query below:
select cust_number,
       '['||listagg(cust_name||':'||cust_profession,',')  within group (order by cust_number)||']' Cust_profession
from CUSTOMER_PROFESSION
group by cust_number;

The problem I have is that this gives me [Mike:Engineer,Mike:Teacher] (as you can see , the name Mike is repeated) instead of [Mike:Teacher,Engineer] . How can I solve this problem?
Thanks in advance


texts: "Fire" a trigger to populate columns that are NOT NULL?

texts:
Working out of my SQL book at the moment. The question is, Create a trigger based upon the ENROLLMENT table.  The trigger should “fire” before an INSERT statement.  The trigger will populate all the columns that are NOT NULL and columns that have foreign key constraints.

CREATE OR REPLACE TRIGGER triggerOne
BEFORE INSERT ON ENROLLMENT
FOR EACH ROW
BEGIN
  --loop to get all of our columns that are are checked to be not NULL
  FOR i in (SELECT STUDENT_ID ,SECTION_ID ,ENROLL_DATE ,FINAL_GRADE ,CREATED_BY ,CREATED_DATE ,MODIFIED_BY , MODIFIED_DATE
            FROM ENROLLMENT
            WHERE created_by || created_date || enroll_date || final_grade || modified_by || modified_date || section_id || student_id IS NOT NULL)
  --loop for CHECK constraints from ENROLLMENT table
  LOOP
    IF (CHECK(:NEW.SECTION_ID = ENR_SECT_FK) AND CHECK(:NEW.STUDENT_ID = ENR_STU_FK)) THEN
      INSERT INTO ENROLLMENT(9,9,'30-JAN-99',9,'9','09-SEP-99','9','01-JAN-00');
    END IF;
  END LOOP;
END;

I'm getting an error on line 9

  Error(9,5): PLS-00103: Encountered the symbol "CHECK" when expecting one of the following:     ( - + case mod new not null          continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date       pipe

We have a ENROLLMENT table with constraints for the columns that are NOT NULL and constraints ENR-SECT_FK and ENR_STU_FK as the foreign key constraints.

Not sure where I'm going wrong here... going to re-read the chapter, but any help would be greatly appreciated!


texts: List all columns and rows of PostgreSQL table with a view limit

texts:
I am currently using select * from items; to show my table during development however I am finding it a bit messy now as some the strings I am adding to the table are 1,000+ chars long.

Is there a modification to the command select * from items; that will simply refuse to show any character past 10 or 15 of all the fields in the database?

I am viewing the table via terminal for development reasons.


texts:
if i write on console at home (new machine, random IP):

/usr/bin/pg_dump --host xxxxxx --port xxxxxx --username "yyyy"
--no-password  --format plain --create --clean --section pre-data --section data --section post-data --inserts --column-inserts --verbose --file "/POSTGRES/backup_2016-09-08" "bbbbb"

serwer dumps database without asking for password! how do i block it?

my pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    aaaa              all             0/0                     md5
host    bbbb      all             0/0                     md5


texts:
I have the following string in db:

A/B/C/
A/B/D/
E/F/C/

It's the uri column.

The table is the following:

create table meta_info (
    id bigserial primary key,
    uri varchar,
    created timestamp with time zone,
    version varchar,
    binary_length bigserial
    )

In most cases I find the directory and want to find children.

E.g.,

SELECT tt.stem[2+1] as current, tt.stem[2+2]
as child from (SELECT regexp_split_to_array(uri, '/')
                      as stem FROM meta_info
                      where uri like 'A/B%' )
as tt group by tt.stem[2+1], tt.stem[2+2]

... , where "2" is the size of path to find (A/B% - size 2 , A/B/C% - size 3 , A/B/D/E%  - size 4)

E.g.,

SELECT tt.stem[4+1] as current, tt.stem[4+2]
as child from (SELECT regexp_split_to_array(uri, '/')
                      as stem FROM meta_info
                      where uri like 'A/B/C/D%' )
as tt group by tt.stem[4+1], tt.stem[4+2]

At the moment I have 400000 rows. It takes too much time to run that sql query.
Can I create an index to improve the performance?

The following index didn't help.

CREATE INDEX idx ON meta_info (regexp_split_to_array(uri, '/'));

The query still is quite slow.
Especially is slow the following sql:

EXPLAIN ANALYZE
    SELECT DISTINCT((regexp_split_to_array(uri, '/'))[1]) as branch
    FROM meta_info

I need to run the mentioned sql when I need to get the unique first levels (folders).

..........................
what about text_pattern_ops, yep, I know it. I am already using it. But thanks for split_part(uri, '/',1). Wow, it works really faster. I have replaced all regexp_split_to_array with split_part. Thanks a lot!


texts: Difference between On Delete Cascade & On Update Cascade in mysql

texts:
I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE and ON DELETE CASCADE

My Parent Table

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

My Question is: What is the difference between the following sql queries.

ON DELETE CASCADE

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

ON UPDATE CASCADE

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON UPDATE CASCADE
) ENGINE=INNODB;

ON UPDATE CASCADE ON DELETE CASCADE

CREATE TABLE child (
        id INT,
        parent_id INT,
        INDEX par_ind (parent_id),
        FOREIGN KEY (parent_id)
            REFERENCES parent(id)
            ON UPDATE CASCADE ON DELETE CASCADE
    ) ENGINE=INNODB;

Are there any errors in the queries? What do these queries (1,2 & 3) mean?? Are they same???


texts: SQL Server database backup status notification

texts:
I have scheduled a SQL Server database backup. But how to notify when backup has failed? There is option like e-mail notification. But that is not suitable for us because we does not checks mails every time.

Is there any another way to display notification on Windows if backup fails?


texts: Boolean as measure in fact table or dimension/attribute value

texts:
My fact table consists of complaints with typical dimensions of who, what and when. We have a target that the complaint should be responded to within a certain time period.

I'm not sure on how I best to model whether the complaint had a response within that time period.

I can store both the target value and the result in the fact table as integers with the result as a measure. Or I could use a dimension which has values of yes/no instead of representing this as a measure, I could then use a calculated measure in my cube. Or I could use a combination of both.

Are there any pros, cons and gotchas modelling the facts in the ways described above?

I anticipate that this fact table will be used to get the total number of complaints, the number of complaints responded to in time, the percentage responded in time and also identify individual complaints which were not responded to in time.


texts: Ideal database to store/query data submitted through dynamic user created forms

texts:
I am designing a web application that allows administrators to create public facing forms. The fields that can be included in the forms are anything from text fields to radio buttons.

I need a solution that allows the users to filter through form submissions.

I have 2 solutions in mind:

A document database such as MongoDB to store submitted form data
Store submitted data in a MySQL JSON data type

Also, what if the form is edited and fields are changed?


texts:
dokku-mongo export Database command is showing error "This does not look like a tar archive"

$ ssh svuser@dbexample.com
$ dokku mongo:export database > backup.tar
$ tar -xfv backup.tar
tar: This does not look like a tar archive
tar: Skipping to next header
tar: Exiting with failure status due to previous errors


texts: Same query taking different execution times on equivalent RDS instances

texts:
The train table has around 60million records all of which have their updated_at timestamp in the year 2021. The schema of the table is as below.
                         Table "public.train"
     Column     |            Type             | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------
 id             | uuid                        |           | not null |
 updated_at     | timestamp without time zone |           | not null |
 status         | task_status                 |           | not null |
 status_details | json                        |           |          |
Indexes:
    "train_id_updated_at_key" UNIQUE CONSTRAINT, btree (id, updated_at)

I have created a temp table train_temp with the same schema but partitioned on updated_at with Partition key: RANGE (date(updated_at))
I ran the below query to add the train table as a partition to train_temp in an RDS instance that is connected to the application server.
  ALTER TABLE train_temp ATTACH partition train FOR VALUES FROM ('2021-01-01') to ('2022-01-01');

The query takes around 5 minute.
Before running the query I had taken a snapshot of the RDS instance. I restored the snapshot on a couple of instances with the same configurations as the original RDS instance.
But when I run the query in the new instances the query takes around 1hr which is very bizarre since it is the same data in an equivalent instance.
Because of the uncertainty in the execution time, I am not able to come up with a concrete plan on how I could take this to production.
Any help would be much appreciated.


texts: status table join slows down query by 10 seconds

texts:
Got an issue with a fair few queries in our system that all rely on a status table (contains 3 rows)

CREATE TABLE `assessment_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(30) DEFAULT NULL,
  `Completed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Started` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Sequence` smallint(6) DEFAULT '1',
  `Archived` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `Archived` (`Archived`),
  KEY `Completed` (`Completed`),
  KEY `idAndcompleted` (`id`,`Completed`),
  KEY `idCompletedArchived` (`id`,`Completed`,`Archived`),
  KEY `idCompletedTitle` (`id`,`Title`,`Completed`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Data

id      Title           Completed   Started     Sequence    Archived
'1'     'Not Started'   '0'         '0'         '1'         '0'
'2'     'Started'       '0'         '1'         '2'         '0'
'3'     'Completed'     '1'         '1'         '3'         '0'

When running the query which has lots of other table joins but to this is:

select
...
ass_s.Title AS `Assessment Section`
...
from au
INNER JOIN assessment_status as_s ON au.idStatus = as_s.id
....
where as_s.Completed = 1

explain extended is:

id  select_type table   type    possible_keys                                                               key         key_len     ref         rows    filtered    Extra
'1' 'PRIMARY'   'as_s'  'ref'   PRIMARY,id,Completed,idAndcompleted,idCompletedArchived,idCompletedTitle'   'Completed' '1'         'const'     '1'     '100.00'    'Using temporary; Using filesort'

and the query takes about 12+seconds to return

Yet if I switch it round to a subquery for title and where exists it runs in 0.6seconds

select
(select as_s.title from assessment_status as_s where au.idStatus = as_s.id and as_s.Completed = 1) AS `Status`
...
from au
....
where exists (select as_s.Completed from assessment_status as_s where au.idStatus = as_s.id and as_s.Completed = 1)

explain extended is:

id  select_type         table   type    possible_keys                                                                   key         key_len     ref                 rows    filtered    Extra
5   DEPENDENT SUBQUERY  as_s    eq_ref  PRIMARY,id,Completed,idAndcompleted,idCompletedArchived,idCompletedTitle        PRIMARY     4           fslcms.au.idStatus  1       100         Using where
3   DEPENDENT SUBQUERY  as_s    eq_ref  PRIMARY,id,Completed,idAndcompleted,idCompletedArchived,idCompletedTitle        PRIMARY     4           fslcms.au.idStatus  1       100         Using where

It's obviously still selecting on the same values, yet runs a hell of a lot faster as it's using where instead on const. The thing is, reading MySQL docs on ref const states it's because it's only using one value it treats it as a const, which is the fastest way!?

I'm quite new with MySQL, come from MSSQL and never come across such a small table that could cause such a big performance issue before... As you can see from the table create, I've even tried varied versions of the index to see if it helps.

Regards

Liam


texts: The 24 is the storage overhead per row in a table is fixed for all tables ot it varies?

texts: I wanted to know whether the storage overhead per row in atable is fixed for all tables created irrespective of given datatypes or it varies from one table to another?


texts:
I have several MS Access databases with tables containing fields that were set to the Data Type "Double" with decimal places set to "Auto"

Access somehow changed all these fields to the Data Type "Decimal". When my users tried to run imports, an error message was displayed saying the decimal field's precision was too small to accept the data

I resolved this easily enough by switching the Data Type back to "Double". But, does anyone know why this would happen? Why would Access change the Data Type to Decimal and set its own parameters for the Precision? It caused the app to fail

Is there any way to prevent Access from doing this?


texts: RMAN Backup to Disk and to Cloud Parallel

texts:
I'm currently working on a backup solution for my company.
One part of the solution would be to write the backup to disk and also send it to an oracle cloud archive storage for long term storage.

In the documentation I only found a way to store it in two directories on local disk via:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/%U', '/u02/%U';

Is it possible to define the first backup location to be local disk, the second in the cloud and let rman backup to those two in parallel mode?

I appreciate any help.

Thanks in advance, jan


texts: Mysql replication does not replicate delete statement

texts:
Experiencing a strange issue.

MariaDB 5.5.44 on both the master and the slave

From the master, if I create a table, it appears on the slave, as well as data written to the master is replicated to the slave, however when I run the following on the master:

DELETE LOW_PRIORITY FROM MOBILE_LOG.USSD_LOG WHERE REQUEST_TIMESTAMP < 1527576588000

It does not replicate to the slave, and MOBILE_LOG.USSD_LOG on the slave still shows older data.

For the master server the binlog variables are:

MariaDB [MOBILE_LOG]> show variables like 'bin%';
+-----------------------------------------+-----------+
| Variable_name                           | Value     |
+-----------------------------------------+-----------+
| binlog_annotate_row_events              | OFF       |
| binlog_cache_size                       | 32768     |
| binlog_checksum                         | NONE      |
| binlog_direct_non_transactional_updates | OFF       |
| binlog_format                           | STATEMENT |
| binlog_optimize_thread_scheduling       | ON        |
| binlog_stmt_cache_size                  | 32768     |
+-----------------------------------------+-----------+

Could anyone advise how I replicate the DELETE statement?


texts:
I have a table with nearly 6m records.  The business key that identifies a unique row is quite large.  Our update processing is now taking a lot longer since I added this new table for a new cube.  I currently do not have an index on the join columns for the update.  SQL Server estimated execution plan says I should create this index on the business key:

/*
Missing Index Details from Server.db
The Query Processor estimates that implementing the following index
could improve the query cost by 86.9178%.
*/

/*
USE [db]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [schema].[Production]
(
    [ProdId],[PriceCalc],[CalcTypeId],[OprId],[CostGroupId],[Resource],
    [BOM],[ResourceDepartment],[OprNum],[DateWIP],[DataAreaId],[Partition]
)
INCLUDE
(
    [ProdOrderStatus],[ManufacturedItemId],[CalcType],[CalculationLevel],
    [CostAnalysisOrderType],[CostGrouping],[UnitId],[WorkCenter],[Name],
    [RealisedConsumption],[RealisedCostAmount],[RealisedCostAdjustment],
    [EstimatedConsumption],[EstimatedCostAmount],[LotSizeVariance],
    [StandardQty],[StandardCost],[ItemStandardQty],[HasSubstitutionVariance],
    [R2],[R3],[StandardQtyByRAFQty],[StandardCostByRAFQty],[ProductionOrderType],
    [RealisedAllocation],[CostVariance],[QuantityVariance],[SubstitutionVariance],
    [TotalVariance],[ComponentItemId],[InventoryUOM],[InventConsumptionTransUOM],
    [BomConsumptionTranUOM],[TransactionUOM],[TransUOMToInvUOMConversionRate],
    [InventConsumptionInvUOM],[BomConsumptionInvUOM],[TotalNetWeightPerUnitInvUOM],
    [InventoryNetWeightUOM],[ReportingNetWeightUOM],
    [NetWeight_InvUOMToReportingUOMConversionRate],
    [InventConsumptionTotalNetWeightInvUOM],[BomConsumptionTotalNetWeightInvUOM],
    [InventConsumptionTotalNetWeightReportingUOM],
    [BomConsumptionTotalNetWeightReportingUOM],
    [FinancialProductId],[FinancialDepartmentId],[FinancialMarketId],[FinancialCodeId],
    [FinancialTypeId],[FinancialSiteId],[ProdPoolId],[Company_SK],[ComponentItem_SK],
    [EndedDate_SK],[DateWIP_SK],[FinancialCode_SK],[FinancialDepartment_SK],
    [FinancialMarket_SK],[FinancialProduct_SK],[FinancialSite_SK],[FinancialType_SK],
    [InventoryNetWeightUOM_SK],[InventoryUOM_SK],[ManufacturedItem_SK],
    [ProductionOrder_SK],
    [ReportingNetWeightUOM_SK],[TransactionUOM_SK],[BatchRunId],[ValidInd],[ScrapVar],
    [QuantityPO],[ExpectedConsumption],[FibreScrapFactor],[QtyAndSubVariance],
    [EndedDate],[RealisedAllocationCost],[FilmScrapFactor])
GO
*/

it wants to create an index on the key columns, however it wants to include many columns.  Should i listen to it or just include the key columns? Thanks for your help. This is a production issue so I can't go testing different things etc.

We are considering putting in a hashing solution, along these lines:

Using Hashbytes to track and store historical changes for SQL Server data

In the short term, next week is an important financial end period and the system needs to perform well so adding an index seems like a good idea.  So I would like to do whatever I can quickly. Would you recommend that hashing solution for long term/new implementations?


texts: Microsoft SSMS 2018 cannot open (only the splash screen)

texts:
yes, i've read this SQL Server Management Studio 18 won't open (only splash screen pops up) , it work the first time. after i restart my computer, and try to open smss again, the problem happen again and i try to fix it like before, but now, it doesnt work.

this is my error log:

      34
      2019/05/19 23:14:19.298
      Error
      VisualStudio
      CreateInstance failed for package [Async Query Service Package]Source: 'mscorlib' Description: Could not load file or assembly 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
System.IO.FileLoadException: Could not load file or assembly 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
File name: 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
   at System.Reflection.RuntimeAssembly.GetType(RuntimeAssembly assembly, String name, Boolean throwOnError, Boolean ignoreCase, ObjectHandleOnStack type)
   at System.Reflection.RuntimeAssembly.GetType(String name, Boolean throwOnError, Boolean ignoreCase)
   at System.Activator.CreateInstance(String assemblyString, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, Evidence securityInfo, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(String assemblyName, String typeName)
   at System.AppDomain.CreateInstance(String assemblyName, String typeName)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

      {DD7B74B5-C1F9-411A-B22E-5E711592BC6B}
      80004005 - E_FAIL

what i get from there is saying that the program failed to find the dll, but, the dll exist.


texts: Oracle, changed NLS_LANGUAGE and is now invalid and can't fix

texts:
I'm using oracle 12c, and changed the NLS_LANGUAGE variable to a invalid text using the manager at localhost:5500/em, after reboot the oracle db doesn't get up and Database Configuration Assistant errors out with :

ORA-12700:invalid NLS parameter value (nls_language)

I tried looking for init.ora but there's nothing about language in it.

I looked for spfile.ora but no file found.

I found file SPFILEORCL.ORA, inside it I have *.nls_language='invalidvalueIput'

But if I change it to *.nls_language='AMERICAN' and reboot it just says the file is invalid, so how am I supposed to fix this value since the EM doesn't come up anymore and nothing seems to fix the file ?


texts: How do I hide sensitive information like plaintext passwords from the logs?

texts:
I do not have access to a Postgres installation, so I cannot check.

I am a security guy, and I'm seeing plaintext passwords in the logs:

create user user1 with password 'PLAINTEXT PASSWORD'

How can the DBAs change or create their passwords without the password in the clear in the logs?

I've seen this, which states you can use an md5 hash of the password, but then the hash is also in the clear. Is there a better way?


texts: Required WAL settings to permit large queries to run on server which is replicating to standby server

texts:
What WAL/conf settings are required to permit large queries in PostgreSQL without breaking replication to the replica server?
Is it only a matter of ensuring that:

the WAL writes don't time out?
the disk doesn't run out of space?
the checkpoints don't timeout


texts: mySQL throws error, but if copied from error message or die() into phpmyadmin it works

texts:
I want to get a script running, which creates a column in a table, if does not exist. I am using the script of user abahet from mysql ALTER TABLE if column not exists

My php code (inside of the db-class) looks like this:

$sql = '
    SET @preparedStatement = (SELECT IF(
        (SELECT COUNT(*)
                FROM INFORMATION_SCHEMA.COLUMNS
               WHERE table_name = "' . $this->t($table) . '"
                 AND table_schema = DATABASE()
                 AND column_name = "' . $column . '"
        ) > 0,
        "SELECT 1;",
        "ALTER TABLE ' . $this->t($table) . ' ADD ' . $column . ' ' . $params . ';"
    ));

    PREPARE alterIfNotExists FROM @preparedStatement;
    EXECUTE alterIfNotExists;
    DEALLOCATE PREPARE alterIfNotExists;
';
//die($sql);
$this->db($sql,'utf8');

Once executed, it throws the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PREPARE alterIfNotExists FROM @preparedStatement;
            EXECUTE alterIfNotExists;
    ' at line 12

            SET @preparedStatement = (SELECT IF(
                (SELECT COUNT(*)
                        FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE table_name = "ddpos_2_user"
                         AND table_schema = DATABASE()
                         AND column_name = "id_signature"
                ) > 0,
                "SELECT 1;",
                "ALTER TABLE ddpos_2_user ADD id_signature mediumint(7) DEFAULT 0;"
            ));

            PREPARE alterIfNotExists FROM @preparedStatement;
            EXECUTE alterIfNotExists;
            DEALLOCATE PREPARE alterIfNotExists;

But when I copy the query from the error message into phpmyadmin, or take the output directly from die($sql) into phpmyadmin, the procedure works fine without errors, and the column id_signature is created.

Why?


texts:
I get this error when running SSIS package...

A buffer failed while allocating 104857600 bytes.
The system reports 31 percent memory load. There are 17179328512 bytes of physical memory with 11714314240 bytes free. There are 2147352576 bytes of virtual memory with 406519808 bytes free. The paging file has 19729465344 bytes with 13419847680 bytes free.

The memory seems ok on the machine that the package is running on. Is 31% memory load considered high?
This has only started to happen after migrating to a newer server. But this new server has double the memory capacity.

No new SSIS packages have been added.
The SSIS packages are stored on SQL Server 2008R2 server. They are called from another server that has SSIS 2008 running.

Any idea what may be causing this?

The SSIS package is executed through a 3rd party application. We forced this application to run as 32 bit as we were getting the below error after adding a reference to System.Data.dll

How do I force the SSIS package to run as 64bit?


texts:
My current backup strategy for our AlwaysOn clusters is a daily full backup and hourly transaction log backups via a third party backup program. We had a request to minimize the amount of log backups and I noticed that we had a cluster of large log backups at the same time during the day and we began a process to run a log backup to nul right before a full backup.

It seemed to have worked until I tried to do a point in time restore and then it wouldn't restore past the full backup. I investigated and noticed that there is an overlap in the LSN's being backed up. Here is an example for one of the databases.

Last real log backup (secondary) - 20165000011169700001 / 20168000018974100001

backup to nul (primary)          20168000018974100001 / 20168000019060800001

full backup   (primary         20168000019060800001 / 20168000019245900001

first log backup (secondary)      20168000019060800001 / 20174000018926600001

It looks like like the log records backed up to the nul device isn't being registered on the secondary node of the cluster. I assumed that running backup log on any node will transfer to the other one since we normally run log backups on the secondary.

Is this how it is supposed to work?


texts: Search for authors whose records are separated by at least X days

texts:
I am using Postgres and have a table which contains metadata about several million images - many of which have the same author.

I am looking for contributors who add content regularly - not people who add images in "bursts" of many photos in a short period of time. In other words, I'd rather people who post six photos over a matter of months than 12 photos in just one week, but never post outside that short time window.

Writing a statement like SELECT count(*) AS i_count,author_id FROM images GROUP BY author_id ORDER BY i_count DESC will give me a list in descending order of who has contributed the most photos to the least, but how might I be able to select only people who have contributed images in a period that is greater than 30 days?

I have a date_added column as well.


texts:
I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just imagined that SQL would have a way easier way to do this.

Essentially, I'm using a GROUP BY to group two values on top of each other. The coding is simple and works well, where I get these results using WITH ROLLUP at the end:

Type    Person  Count
Type A  Person 1    10
Type A  Person 2    91
Type A  Person 3    13
Type A  Person 4    10
Type A  Person 5    2
Type A  NULL        126
Type B  Person 6    16
Type B  Person 7    63
Type B  Person 8    15
Type B  Person 9    22
Type B  Person 10   16
Type B  Person 11   3
Type B  Person 12   20
Type B  Person 13   44
Type B  NULL        198
NULL    NULL        360

Is there an easy way to substitute something saying if "NULL" is in the "Person" column, then make NULL AS "Subtotal" and if "NULL" is in the "Type" column, NULL AS "Grand Total?"

And if that's weirdly complicated, I'd be totally fine with just calling all of them "Total."

Thanks!


texts: Differences between MySQL DESCRIBE, SHOW COLUMNS FROM, SHOW CREATE TABLE and query to information_schema table

texts:
I have always checked table definitions through DESCRIBE, but today I came across something weird.

Another guy told me the DEFAULT value shown for a column through SHOW CREATE TABLE was different from what I got from DESCRIBE. I checked information_schema and I got the same result from DESCRIBE, so I’m curious why SHOW CREATE TABLE is showing something different.

Funny thing is some people at #mysql (IRC) told me this:

  protip: don’t use DESCRIBE or SHOW COLUMNS FROM, use information_schema

Funny enough, though I asked, no one told me the reason, and also given the one being different is SHOW CREATE TABLE, at this point I’m lost.

Given how the application works, I am trusting SHOW CREATE TABLE, but the wise people and my barely-knows-database knowledge tell me information_schema is right.

Could you please explain which option should I use, why, and when not, why does documentation mention nothing about not trusting one or the other?

Thanks in advance!


texts:
I have this code:

declare
  error1  number;
  error2  number;
  error3  number;
begin
  error1 := 1;
  error2 := 0;
  error3 := error1 | error2;  -- should be error3 = 1
end;

But I dont know how to do a logical OR in oracle PL/SQL. I dont want to do it with IF/CASE.


texts: Adding an identity field to be the new cluster of a large table - how do I get the "right" order?

texts:
I have a very large table (88m rows, nearly 300GB in size) that has a poorly planned clustered index. I want to add a BIGINT identity field, and make it the new clustered index (having the desirable attributes of unique, narrow, static, and ever-increasing).

The problem is that I also want this new identity field to be ordered in a certain way on the current records (based on an existing date field that is not the current cluster). This is so when we purge the oldest records, those will all be contiguous on disk. (The date field isn't unique, though, so isn't a good candidate for the cluster by itself.)

How can I get the values for this new identity field ordered the way I want?

The first idea I had was to make a new table with the identify field, and just INSERT INTO rows from the current table ORDER BY the date field? Would that work?

If I don't have enough disk space to do that, is there any way to do that "in-place"? Perhaps by changing the clustered index to the date field (wait a long time), then add the identity (and wait a long time again)? Are the values of the identity guaranteed to be ordered by the current cluster?