texts: Which are the differences and advantages on single INDEX or compound INDEX?

texts:
I have two INDEX on a n:m relationship table as you can see on pics below:

Now I can have this also:

My questions:

What is the advantage from last way(pic compound) over the two first?
Can the # have some weight or significance in the composite INDEX?


texts:
I have a legacy PostgreSQL database schema with hundreds of functions. I know that some of them aren't used anymore, and I want to delete them.

What is the easiest way to find which procedures are executed sometimes and which aren't? Is there any kind of statistics about procedure usage in PostgreSQL?

Used engine version is PostgreSQL 9.5.


texts: MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?

texts:
I'm using a MySQL database to store files.

The table I'm using is structured as follows:

+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| AttachmentID | int(11)  | NO   | PRI | NULL    |       |
| Data         | longblob | NO   |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+

The INSERT command I'm using is as simple as possible:

INSERT INTO table (AttachmentID, Data) VALUES (attachmentID, attachmentData);

Obviously, where attachmentID is an int, and attachmentData is a large byte array.

Having read several guides on how to store files in a database, I increased the max_allowed_packet setting in the config file to "512M", more than enough for the files that I would actually be inserting.

This worked fine for several files of 30MB - 40MB. However, now that I am inserting larger files in the 90MB+ range, I am recieving an "Out of Memory" exception, with a number of bytes needed equal to the size of the file I tried to insert.

The server is a virtual server, with 4GB allocated and nothing else running that could be interfering.

Why do I get an Out of Memory error for the larger files, and not the smaller?
Where is this file-size limitation arising?

I've included my config file below:

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-locking
key_buffer_size = 256M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
max_allowed_packet = 512M

ft_min_word_len=2

ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so

log-bin=mysql-bin
binlog_format=mixed
server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

EDIT: I can successfully INSERT a file of size 41,586 KB, but not one of 44,119 KB. The limit is somewhere between them.


texts: save 14 columns in one table or different table?

texts:
We are giving option to customer to enter his name, email, telephone like this in 14 textfields he will enter information. for all customers we wil create userid as one column in table.

so in mysql db, is it better to store all these information in one table or in different tables.

if we store in one table , later can we save same information in different tables and viceversa ?


texts:
Currently in my CI I'm completely wiping production db with development db. That was fine for testing but I'm coming to a stage where I need to start merging instead of completely wiping.
Right now the command I'm using to wipe and write is as follows:
mysqldump -u "$DB_USERNAME" -p"$DB_PASSWORD" -h "$DB_HOST" "development" > dump.sql
mysql -u "$DB_USERNAME" -p"$DB_PASSWORD" -h "$DB_HOST" "production" < dump.sql

I tried the following for merging:
mysqldump --no-create-db --no-create-info -u "$DB_USERNAME" -p"$DB_PASSWORD" -h "$DB_HOST" "development" > dump.sql

Which I thought would do the trick but I get the following error:
ERROR 1062 (23000) at line 23: Duplicate entry '1' for key 'PRIMARY'

Any ideas where I'm falling short?


texts: T-SQL to show Azure SQL Managed Instance vs Azure SQL Database

texts: Restated: How can I use T-SQL to determine whether a instance is an Azure SQL Managed Instance or Azure SQL Database?


texts:
I have an external application making a stored procedure call into a SQL Server 2017 database, via a normal database connection (PHP library (PDO?) of some form using the server, database, user, password) where it sends the stored procedure call as a block of SQL in a text string:

"DECLARE @Ret INT ;
 EXEC @Ret = spStoredProc :TheDate ;
"

where :TheDate is a DATETIME parameter (set to a value via PHP magic), and should be either NULL or a string containing a valid date.

Unfortunately, the caller is passing the NULL as 'NULL' (quoted string), which is leading to a 'Cannot convert varchar to datetime' error during SQL Server's set up to run the stored proc, and the stored procedure never runs.

Expecting : EXEC @Ret = spStoredProc NULL

Getting : EXEC @Ret = spStoredProc 'NULL'

Is there a way to log this situation? I have no control over the external application doing the call, so I want to log this situation on the SQL Server side.


texts:
Since english is not my first language i find hard to understand this paragraph that i'm reading from "Database Fundamentals" book.

I'm trying to figure out, in practice, real world, what this really means.


texts:
I have a main query that have a bunch of cross joins in it such as:

SELECT val1, val2, ...
FROM
 (  SELECT top 1 id as val1
    FROM TableA
    ) A
CROSS JOIN
 (  SELECT top 1 id as val2
    FROM TableB
     ) B
CROSS JOIN
 ( ?
  ) C

Inside the third CROSS JOIN C I want to place a query that contains a CTE in it (this one basically allows me to execute a query only if another query has no results), which looks like the following:

WITH r AS (
  SELECT * FROM film WHERE length = 120
)
SELECT CASE WHEN @flag = 'Y' THEN (
    SELECT * FROM r
    UNION ALL
    SELECT * FROM film
    WHERE length = 130
    AND NOT EXISTS (
      SELECT * FROM r
    )
) ELSE NULL END val3

Is this possible?


texts: Migrating PostgreSQL files to new server

texts: We have a Win 2008 server that went down because of cpu failure.  We were running parts of the Atlassian stack (JIRA, Confluence, Stash, and Bamboo) on it using PostgreSQL as the database.  I now have the harddrive from that dead server mounted in to a Win 7 machine as a secondary harddrive.  We unfortunately did not have backups setup.  All of the instructions I see for migrating databases to new machines involve running PostgreSQL cli programs against a running PostgreSQL instance, but in my case all I have is a folder not a running PostgreSQL server.  I would like to be able to install PostgreSQL on the Win 7 machine, somehow get the data migrated over to the new PostgreSQL installation and off of that secondary drive.  Then I would like to export the data out using the standard procedures and move it to a new production server or possibly export using the Atlassian backup tools if we end up moving the data in to the Atlassian cloud service where they use who knows what db engine.  Either way though I need the data off the olddrive and running in a new PostgreSQL server to move forward.  Anyone have any ideas?


texts:
Oracle 11.2.0.3

My instructions are

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, '<schema_name>');

I tried to run them at sqlplus /as sysdba

but it tells me unknown command beginning DBMS_RULE

Is there a different way to run these?


texts: Get total number of rows when offset is greater than max

texts:
I have a table users that has, say, 42 records. To get the total rows and some other details, I use a window function:

select count(*) over() as count, name, id from users

And since the consumer app is paginated, I can utilise limit and offset to get the desired data:

select count(*) over() as count, name, id from users limit 5 offset 20

This gives me a (sample) result:

 count |  name   | id
-------+---------+----
    42 | Dummy05 | 27
    42 | Dummy06 | 30
    42 | Dummy07 | 31
    42 | Dummy08 | 32
    42 | Dummy09 | 33
(5 rows)

Of course, this can be optimised as described in this answer.

However when the offset value overshoots, I get 0 rows which is correct.

select count(*) over() as count, name, id from users limit 5 offset 50

Is there a way that gives me the total count despite the offset returning 0 rows? Something on the similar lines:

 count |  name   | id
-------+---------+----
    42 |         |
(0 rows)


texts: Optimizing a query which runs through all hours in a day

texts:
I need help optimizing a query. I'm using PostgreSQL 9.3.4 currently but can upgrade to 9.4 if needed.

I have a table with 60+ millions of records which looks like this:

                                                          Table "public.snapshots"
   Column   |           Type           |                         Modifiers                          | Storage  | Stats target | Description
------------+--------------------------+------------------------------------------------------------+----------+--------------+-------------
 id         | integer                  | not null default nextval('snapshots_new_id_seq'::regclass) | plain    |              |
 camera_id  | integer                  | not null                                                   | plain    |              |
 created_at | timestamp with time zone | not null                                                   | plain    |              |
 notes      | text                     |                                                            | extended |              |
 data       | bytea                    | not null                                                   | extended |              |
 is_public  | boolean                  | not null default false                                     | plain    |              |
Indexes:
    "snapshots_new_created_at_camera_id_index" UNIQUE, btree (created_at, camera_id)
Foreign-key constraints:
    "snapshots_new_camera_id_fkey" FOREIGN KEY (camera_id) REFERENCES cameras(id) ON DELETE CASCADE
Has OIDs: no

There can be between 0 and 3600 snapshot records in an hour. For this query I'm interested only in knowing which hours in a given day for a given camera_id have 1 or more snapshot records (the actual count is unimportant).

Currently the application is implemented to execute one query for each hour in the day, like this:

SELECT count(*) AS "count" FROM "snapshots" WHERE (("snapshots"."camera_id" = 4809) AND ("created_at" >= '2015-05-24 23:00:00 UTC') AND ("created_at" <= '2015-05-24 23:59:59 UTC'));
SELECT count(*) AS "count" FROM "snapshots" WHERE (("snapshots"."camera_id" = 4809) AND ("created_at" >= '2015-05-25 00:00:00 UTC') AND ("created_at" <= '2015-05-25 00:59:59 UTC'));
...
SELECT count(*) AS "count" FROM "snapshots" WHERE (("snapshots"."camera_id" = 4809) AND ("created_at" >= '2015-05-25 22:00:00 UTC') AND ("created_at" <= '2015-05-25 22:59:59 UTC'));

  Explain analyze for one query: http://explain.depesz.com/s/9tbP

I tried optimizing this and ended up with one query which seemed like it was what I needed:

SELECT count(*) AS "count" FROM "snapshots" WHERE (("created_at" > '2015-05-06 23:00:00.000000+0000') AND ("created_at" < '2015-05-08 23:00:00.000000+0000')) GROUP BY date_trunc('hour', created_at);

  Explain analyze for this query: http://explain.depesz.com/s/cVUK

But this is actually 10x times slower than the above 24 queries combined.

What am I doing wrong? Should I forget COUNT since I'm not interested in the actual count? How would the query look like then?

Edit: Thanks for all the comments and answers, I learned multiple things from you! I wish I could accept all three answers, I chose ypercube's as it seems most efficient and flexible.


texts: MongoDB failover when master is stuck on IOWait?

texts:
We host a MongoDB 3.4 replica set in AWS with three nodes:  a primary, secondary failover and an arbiter.  Normally, if the primary instance dies, the failover to the secondary is pretty quick (10-30 seconds).

Today we had a network issue where the MongoDB primary instance lost connectivity with the disk containing the database for about 3 minutes and CPU IOWait went to 100%.  During this time, queries to the primary just hung and went into timeout.  Probably because the primary was still up (though unresponsive), the replica set did not failover or even start a vote.

Is there a configuration which would produce a failover also in such cases?  Or is there some ready tools that could force a failover if simple queries to the primary node start taking too long?


texts: how convert single row transposed into multiple columns

texts: dynamically how to convert single row transposed into multiple columns.


texts:
We have a database which has a number of tables that all utilise different identification methods for currencies. Some use ISO numbers other tables use ISO Codes. All the data and (and effectively) structure of these tables is from outside third party sources, so there is no influence on how and the format of the data produced.

We have a table that is a Currency table that contains both ISO number and ISO alpha codes, and a CurrencyId (just a simple integer: 1, 2, 3 etc).

We have an ETL that imports the data, and in some tables, the data is converted at import time from one currency identifier (eg ISO number) to the other currency identifier (eg ISO code). This I think is a result of different, incongruent, teams working on the software but that is a topic in its own right lol.

We have a .net software application that references these tables directly, and obviously there's no consistency in which ISO code (number or alpha) that the software is referencing.

It is also proving difficult to maintain code by referencing sometimes the ISO code and other times the ISO Number for the currency.

So we are considering one of the following resolutions:

Import all data and map at that point a new derived field (we are using SSIS) that is the CurrencyId for that currency from the "Currency" table based on the ISO code number or alpha (and still import the currency identifier that the original data does). Then change the references in the software to utilise the CurrencyId instead of the currency ISO code or number.

OR

build views for all the relevant tables and join them to the currency table on either ISO number or ISO code (whichever relevant). Then change all the references in the software to reference the view instead and utilise the CurrencyId instead of the currency ISO code or number.

We would like some advice and input as to which would be better approach for a performance perspective. Data storage perspective I imagine will be a small impact since I think the CurrencyId can be contained within a tinyint datatype field, but still very interested to know what you think in this area too.


texts: Second index on junction table with composite primary key: include both or one column

texts:
I'm working on a database that will be very read-intensive, and uses a junction table that's read often and in both directions.

The junction table contains two columns, IDA and IDB

The primary key for the junction table will be a composite key for both columns.

I want to create an index so the database can quickly seek the corresponding IDA for a specific IDB.

Should that index contain both IDB and IDA? Or should it just contain IDB?

To put it another way, should my table look like this:

CREATE TABLE ExJunction(
    IDA INTEGER NOT NULL,
    IDB INTEGER NOT NULL,
    CONSTRAINT PK_ExJunction_IDA_IDB PRIMARY KEY CLUSTERED (IDA ASC, IDB ASC),
    CONSTRAINT AK_ExJunction_IDB_IDA UNIQUE (IDB ASC, IDA ASC)
)

Or like this:

CREATE TABLE ExJunction(
    IDA INTEGER NOT NULL,
    IDB INTEGER NOT NULL,
    CONSTRAINT PK_ExJunction_IDA_IDB PRIMARY KEY CLUSTERED (IDA ASC, IDB ASC),
    INDEX IX_ExJunction_IDB NONCLUSTERED (IDB ASC)
)

Or, should I do something else altogether?

I'm having trouble conceptualizing this, since I've always thought of nonclustered indexes containing both the index key and the primary key. But the index key is already contained in the primary key, so, are these the same (both containing IDB and IDA), or different?


texts:
I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the experts in the field.

One of my questions is:

What does a best-practice SQL Server maintenance plan look like? Do you use the SQL Server maintenance plans for this, or do you use custom scripts?


texts:
I'd like to get some clarification about the --flush-privileges option for mysqldump.

Here's the description of the option from the MySQL docs:

--flush-privileges
Send a FLUSH PRIVILEGES statement to the server after dumping the mysql database.
This option should be used any time the dump contains the mysql database and any other
database that depends on the data in the mysql database for proper restoration.

When it says that it sends the flush statement after dumping the database, I read that to mean that the data, schema, etc. is dumped into the backup file and then the flush statement is sent to the database that was just dumped (after it was dumped).

I was wondering what dumping the data, etc. did that required the privileges to be flushed, so I started searching for an explanation to be sure when and why to use it.

As I read various answers, it occurred to me that it would make sense if the flush statement was being included in the dump file. Then after the contents of the file was loaded into a database, the flush privileges statement was run to update the settings after the new info was imported.

So, how does it work?
A) Flush the source database after dumping the data to a file? If so, why is this necessary?
B) Flush the destination database after importing the contents of the dump file?
C) Something other than the possibilities I've described?


texts:
Are there any benefits in disabling mongodb journaling besides performance gain?
I know journaling provides write durability and crash resiliency. But I can tune syncdelay parameter to make data files flush more frequently, but mongodb docs recommends against this. What effect would it have on my mongodb setup and why should I not set syncdelay on production setups?

Thanks,
Abhi


texts: Difference in the workload related to data-amount and search option

texts:
A couple of days ago I was asked why it is necessary to use the first normal form also for separation of names.

We have a table which stores private customers (Name+Surname;Information) and business customers (Companyname;Information).

My answer was that first of all it is a bad habit not keeping the values atomic and second the workload for using full-text searches will increase and affect the speed of the results as the amount of data in the database grows. An open end search needs less traffic than a full-text one.

Unfortunately I haven't found data related to the question HOW exactly the speed and efficiency decrease if I am searching for '%whatever%' instead of 'whatever%' or even weirder regular expressions.

Especially related to the scenario if the amount of rows in the table increases.

Can someone provide hard facts related to that?


texts: How can I use `psql` without installing via brew

texts:
I want to use the psql utility standalone, without installing postgress via brew or something (I have my reasons). I use psql version 9.5.3 on OS X, which was distributed with PostgressApp.

When I only distribute psql, I get the error:

dyld: lazy symbol binding failed: Symbol not found: _PQsslInUse
...
Expected in: /usr/lib/libpq.5.dylib

On most computers, /usr/lib/libpq.5.5.dylib is installed, but I use version 5.6. So then I tried distributing libpq.5.6.dylib in the same folder. This doesn't work, even when I set the $DYLD_LIBRARY_PATH variable.

Any pointers?


texts: How do I use the result of a CASE statement in a WHERE clause?

texts:
When I try to run this SELECT statement, I get this error: ORA-00904: "GPASCORE": invalid identifier. How do I use the result of the gpaScore CASE statement in the WHERE clause?

SELECT
  (CASE grade
   WHEN 'A+'
     THEN 4
   WHEN 'A'
     THEN 4
   WHEN 'A-'
     THEN 3.7
   WHEN 'B+'
     THEN 3.3
   WHEN 'B'
     THEN 3
   WHEN 'B-'
     THEN 2.7
   WHEN 'C+'
     THEN 2.3
   WHEN 'C'
     THEN 2
   WHEN 'C-'
     THEN 1.7
   WHEN 'D+'
     THEN 1.3
   WHEN 'D'
     THEN 1
   WHEN 'D-'
     THEN 0.7
   WHEN 'F'
     THEN 0
   ELSE NULL END)   gpaScore
FROM PGFINALGRADES
WHERE gpaScore = 3


texts: SQL Server Management Studio cannot Connect to Always On Availability Listener

texts:
I am trying to connect to the AG Listener in SSMS.
I choose [Connect]
Then [Database Engine]
I type in the name of the listener I created.
SQLListener
[Windows Authentication]
Click [Connect]

After about 1-minute, I receive the following ERROR message

  TITLE: Connect to Server

  Cannot connect to SQLListener.

  ADDITIONAL INFORMATION:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)

  (Microsoft SQL Server, Error: 2)

  The system cannot find the file specified

I watched a video on YT where this guy runs through the creation of the AG.
He names his GROUP and his LISTENER
Identical names, and can connect to the Listener, through SSMS.
However, I cannot.
My Group is: SQLGroup
My listener is: SQLListener
(Are they supposed to be identical names?)

I wanted to make sure my Listener port was available and was able to be reached
so I ran the following on the primary SQL Core Server.
The listener is registered in DNS and in AD - Computers as SQLListener.

cmd = netstat -ano|fine "2433"
Returned
TCP    192.168.2.77:2433   0.0.0.0:0   Listening   252
I did an NSLookup on the SQLListener.

  CMD nslookup SQLListener
  Returned:
  Server: MasterDC.Domain.local
  Address: 192.168.2.88
  Name: SQLListener.domain.local
  Address: 192.168.2.77
  Everything is accurate.

Do I need to open the port 2433 on my work station to add the listener within SSMS?
What is it that I am missing here?

Wayne


texts: Optimized ways for defragment mysql database table

texts:
We have a database in MySQL which has more than 500 GB of data, and currently in the process of purging data and count might go beyond 1 million. As we know that purging might lead to high fragmentation and will degrade performance. Also, under few circumstances where the purge data is more, it may make existing index unusable

In case if somebody has come across such scenario, please let me know best technique to handle the same


texts: Best way to model a database structure to store multidimensional variables?

texts:
I am working with some NetCDF data which has multidimensional variables.

As far as I'm aware, the fields are not mandatory.
Each variable will have the same fields/attributes.
Each variable represents a measurement of the atmosphere, in time & space. Level represents a pressure level (like a layer of an onion -- a thick layer).

For example, a variable called SO2 (Sulphur Dioxide) may be 4-D: Time, Level (pressure), latitude, longitude.

I have about 14 variables that I need to store.

Data retrieval tendencies

Now, I'm going to need to query this data and ask questions like:

Give me all the rows for variable SO2 that fall between latitude X and longitude Y at time T and level L.
What is the average temperature for time T at level L?
etc...

Current considerations

I could let each row in my DB be an observation and have the columns: type, lat, long, time, level, value.

Or, perhaps create a table for each kind of variable and have the columns: lat, long, time, level, value.

I'm not sure which will be more appropriate, or whether there is a better option, so I'd appreciate some advice.


texts:
I'm trying to create a linked server in SQL server 2017, linked system is odbc (criteria criOdbc)
Test 1 Form SQL Server SSMS
select * from OPENROWSET('MSDASQL','DSN=criteria64;uid=xxxx;pwd=yyyy;','select * from table where key<10')

-->  Give this error "Error converting data type DBTYPE_DBDATE to date."
Test 2 Form SQL Server SSMS
select * from OPENROWSET('MSDASQL','DSN=criteria64;uid=xxxx;pwd=yyyy;','select key from table where key<10')

--> Works perfectly
Test 3 In c# (load form odbc then bulk insert in sql server)
OdbcConnection acc = new OdbcConnection("DSN=criteria64;uid=xxxx;pwd=yyyy;")
OdbcCommand cmd = new OdbcCommand("select * from table where key<10", acc);
....

--> Works perfectly
Test 4 connect the same table in MS Access as linked table
--> Works perfectly
Conclusion

Msdasql (Provider OLE DB Microsoft for ODBC) --> Doesn't work well with date and some other type .net
System.Data.Odbc (.NET Framework
Data Provider for ODBC) --> Works perfectly

Final question
Is possible to use NET Framework Data Provider for ODBC in linked server? (cant find any documentation)
If not
Is there any kind of connector (odbc based) tath I can try to use in linked server in alternative to "msdasql" ?
Note
Casting every single date colum using a case statement is not a solution for me. I'm searching for a global fix.
I also need to Write into this tables.
My more comfortable alternative in this case is use c#.
_
Thank you for your time


texts: Failover scenario with principal "hard down"

texts:
My company has a failover pair of SQL Server 2005 instances, which provide database availability for a UL-licensed alarm/call center, where uptime and availability are critical for life safety.

Here's an example line from the connectionStrings of one of the business-critical applications (sanitized for our protection):

<add name="MyCompany.MyApp.Properties.Settings.MyDbConnectionString"
 connectionString="Data Source=Principal-DB;Failover Partner=Mirror-DB;
 Initial Catalog=MyDb; Integrated Security=True; Persist Security Info=True;"
 providerName="System.Data.SqlClient" />

About half an hour ago, this was the scenario as we believe it to be:

DBA reports need for hardware maintenance on Principal-DB server to all users at a specified time, and recommends they close and restart client applications if they experience database connection issues.
DBA fails over from Principal-DB to Mirror-DB. Client applications are unaffected.
DBA powers down Principal-DB server.
Some client applications begin to timeout on pooled connections (as would be expected). Users experiencing these errors close and restart as recommended by the DBA.
Those client applications now fail to start altogether, due to a failure to receive a response from the database.
Hilarity ensues.

Our theory was that, because Principal-DB was not in a state to respond to connection requests at all, when it would normally refuse such connections quickly if it were in the restoring state, the client applications ended up waiting for the entire allotted connection timeout (default, 20 seconds) for the primary server to respond, then returning the timeout error without ever attempting to connect to the listed failover partner.

The quick fix was to push an update to the client application containing an App.config that swapped the Data Source and Failover Partner instances, so Mirror-DB was now the server to which the app attempted to connect first. When we fail back to Principal-DB, we will have to undo this change with another application update.

I need a more permanent fix. This was not our expected behavior for a failover pair, and it cannot be allowed to happen again. There has to be a way to configure the client application so that it will correctly try to connect to the failover partner in this circumstance before returning an error.


texts:
My problem is this, I want to Install Sql server 2014 in my Pc but i have a problem with it:

SQL Server Setup failure.
SQL Server Setup has encountered the following error:
The SQL Server license agreement cannot be located for the selected edition, SQLBI. This could be a result of corrupted media or the edition being unsupported by the media.
Error code 0x858C001B.

My pc is new, I have not had any previous version of SQL server on this pc.. Do you have a Solution?
Thanks in advance.


texts: Horizontal scaling issues with row locking on a permissions system

texts:
Note: This is also on Stack Overflow as DBAs and Programmers could offer differing opinions

The requirement

I am currently building a permissions system. One of the requirements is that it is horizontally scalable.

To achieve this, we have done the following

There is a single "compiled resource permission" table that looks something like this:

| user_id | resource_id | reason |
|    1    |      1      |   1    |
|    1    |      2      |   3    |
|    2    |      1      |   2    |

The structure of this table denotes user 1 has accesses to resource 1 & 2, and user 2 has access only to resource 1.

The "reason" column is a bitwise number which has bits switched on depending on "Why" they have that permission. Binary bit "1" denotes they are an admin, and binary bit "2" denotes they created the resource.

So user 1 has access to resource 1 because they are an admin. He has access to resource 2 because he is an admin and he created that resource. If he was no longer admin, he would still have access to ticket 2 but not ticket 1.

To figure out what needs to go into this table, we use a "patcher" class that programmatically loops around the users & resources passed to it and logically looks at all DB tables necessary to figure out what rows need adding and what rows need removing from the table.

How we are trying to scale and the problem

*To horizontally scale this, we split the logic into chunks and give it to a number of "workers" on an async queue**

This only seems to scale so far before it no longer speeds up or sometimes even row locking happens which slows it down.

Is there a particular type of row lock we can use to allow it to scale indefinitely?

Are we approaching this from completely the wrong angle? We have a lot of "Reasons" and a lot of complex permission logic that we need to be able to recompile fairly quickly

SQL Queries that run concurrently, for reference

When we are "adding" reasons:

INSERT INTO `compiled_permissions` (`user_id`, `resource_id`, `reason`) VALUES ((1,1,1), (1,2,3), (2,1,2)) ON DUPLICATE KEY UPDATE `reason` = `reason` | VALUES(`reason`);

When we are "removing" reasons:

UPDATE `compiled_permissions` SET `reason` =  `reason` & ~ (CASE
            (user_id = 1 AND resource_id = 1 THEN 2 ... CASE FOR EVERY "REASON REMOVAL")
            ELSE `reason`
            END)
        WHERE (`user_id`, `resource_id`) IN ((1,1),(1,2) .. ETC )


texts: Alter user - is password stored in audit trails

texts:
I was asked an interesting question the other day and I have not been able to find a definitive answer.

If one of my DBA's changes a password, will the password the password show up in plain text in the audit trails?

Eg. sys as sysdba changes the password of system.

ALTER USER system IDENTIFIED BY <password>

Oracle states that it will not if the user changes their own password, but says nothing about another account changing the password of another account.
The suspicion is that the password would show up in clear text in trails such as v$SQL.

This seems like an egregious oversight if Oracle didn't account for this but I cannot find anything to prove otherwise.


texts: Monitoring Progress of Index Construction in PostgreSQL

texts:
Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring.

Is there a way to monitor this?


texts:
mysql> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB, ROW_FORMAT=TOKUDB_DEFAULT;
ERROR 1064 (42000): 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 'TOKUDB_DEFAULT' at line 1

CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB ROW_FORMAT=tokudb_zlib;
ERROR 1064 (42000): 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 'tokudb_zlib' at line 1

it is on Percona Server 8.0

why??


texts:
What can be done, if anything, when the special guest user is orphaned (not linked to any login)?

For one of my databases (SQL Server 2005), executing the following lists the guest user as an orphaned user.

exec sp_change_users_login 'report'

Results:

UserName    UserSID
guest       0x3C2E66759FFBC14F84127D6795C27FD3

If I try to fix the guest user using that procedure, I get the following:

exec sp_change_users_login 'update_one', 'guest', 'guest'

  Terminating this procedure. 'guest' is a forbidden value for the
  login name parameter in this procedure.

If I try to delete the user, I get:

  User 'guest' cannot be dropped, it can only be disabled.

select * from sys.database_principals where name = 'guest'

Results in:

name                 guest
principal_id         2
type                 S
type_desc            SQL_USER
default_schema_name  guest
create_date          11/13/98 2:58 AM
modify_date          10/16/01 4:31 PM
owning_principal_id  NULL
sid                  0x3C2E66759FFBC14F84127D6795C27FD3
is_fixed_role        0

The database seems to be confused as to whether this is a special user or not. Is there anything that can be done?


texts:
I have been searching a while to find a way to retrieve the amount of queries my postgres database is processing and responding to. I found various statistics about disk I/O, insert/update statistics but nothing specifically reflecting queries.

Why do I need it?
I am developing a client application and I want to find out if I missed any sections in my program where a lot of small queries are executed in bulk that could possibly be done in one big query to speed up queries from very far away clients (signal propagation delay).

To monitor this I wrote myself a tool displaying a "count per second" graph by polling

SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database

SELECT sum(tup_fetched) FROM pg_stat_database

and displaying the difference to the last poll.

However the first statement does not seem to update propery, or not display "select" statements at all as it is sometimes showing 0, which is impossible because it should at least show 1 even on an idle server due to the poll-query.
The second query does not seem to work either, it is showing 0 most of the time.

Any ideas?

Also if anyone knows if there is a way to get the actual network read/write statitic that would help as well. (not in packages or traffic, but in "recieved: query, sent: 1000 rows, received: next read request, sent: (last) 20 rows -> 2 operations recorded", so basically I can derive with a ping of 150ms the total time needed would be 600ms minimum, which could be reduced to 300ms by increasing the fetch size)

If you have any ideas as to how to test this with a different approach I am also open to suggestions.


texts: Executing a stored procedure inside another stored procedure from a linked server

texts:
I'm having a problem and I don't know how to solve it, I have searched the web and found good advice but I can't work it out.

This is the problem: I have a SQL Server instance running on my PC, and I linked one of the main servers SRVOLD\SQLDESA to it. I want to execute main server's stored procedures from my PC's SQL Server instance and insert the results into a new table. I found the perfect way to do it using the following:

SELECT *
INTO Bank
FROM OPENQUERY([SRVOLD\SQLDESA],
               'EXEC Bank_Database.Bank.usp_GetTDcodes 1, 5')
GO

There is important information about this server, it's SQL Server version is 2008. Keep this in mind for later.

Ok so I managed to executed this Stored Procedure but I found out something, turns out that inside this Stored Procedure there's an execution of another stored procedure, check this out:

1st stored procedure:

CREATE PROCEDURE Bank.usp_GetTDcodes
    (@code TINYINT = NULL, @qty TINYINT = NULL)
WITH ENCRYPTION
AS
    DECLARE @@msg VARCHAR(100)
    DECLARE @@OK INT
    DECLARE @@today CHAR(30)

    SELECT @@today = CONVERT(VARCHAR(30), GETDATE(), 112) + ' ' +
                     CONVERT(VARCHAR(30), GETDATE(), 8)

    SELECT bnk_code, bnk_descr
    FROM CODBNK
    WHERE bnk_code < 50

    EXECUTE @@OK = Bank.usp_WriteEvent  @qty, @code, @@today, 500

    IF @@OK <> 0
        RETURN @@OK

RETURN 0
GO

Now let's look inside the 2nd stored procedure:

CREATE PROCEDURE Bank.usp_WriteEvent
    (@code TINYINT = NULL,
     @qty TINYINT = NULL,
     @date DATETIME = NULL,
     @number SMALLINT = NULL,
     @ideve INT = 0 OUTPUT)
WITH ENCRYPTION
AS
    DECLARE @@sdate VARCHAR(30)
    DECLARE @@ret SMALLINT

    INSERT INTO Event (eve_code, eve_qty, eve_date, eve_number)
    VALUES (@code, @qty, @date, @number)

    SET @@ret = @@error

    IF @@ret = 0
    BEGIN
        SELECT @ideve = @@IDENTITY

        SELECT @@sdate = CONVERT(VARCHAR(30), @date, 112) +  ' ' +
                         VARCHAR(30), @date, 8)
    END
    ELSE
        RETURN @@ret
GO

When I executed the 1st stored procedure, I was able to insert it's result into a new table, but I was hoping to find a new row inserted in the table Event, because that is the expected result when executing 2nd stored procedure.

So I started to search online and managed to achieve this by doing the following:

SELECT *
INTO Bank
FROM OPENQUERY([SRVTEST\SQLDESA],
               'SET FMTONLY OFF;SET NOCOUNT ON;EXEC Bank_Database.Bank.usp_GetTDcodes 1, 5')
GO

So, the SET FMTONLY OFF;SET NOCOUNT ON worked and I was happy. But something happened...

I needed to execute the same stored procedure, but this time adding a new linked server SRVNEW\SQLDESA. This server's version is 2012, so the new solution didn't work. I kept trying and trying different ways, there's just one way to make it work and is the following:

EXEC [SRVNEW\SQLDESA].[Bank_Database].Bank.usp_GetTDcodes 1,5

But it doesn't work for me because I need the 1st stored procedure result into a new table. And I don't know its schema that's why SELECT INTO works best for me. I don't know what else I can do, maybe is the OPENQUERY that doesn't work? Do I need to change something else?

PD: I also tried using OPENROWSET didn't work either.

Thanks in advance, and have a nice day! Peace!

Some references: http://www.sommarskog.se/share_data.html#OPENQUERY


texts:
Help me please!

My C:\mongodb-win32-x86_64-2.4.6\data size is 8G. I have 95G free space of drive C. But I get this message:

  com.mongodb.WriteConcernException: { "serverUsed" : "/127.0.0.1:27017"
  , "err" : "Can't take a write lock while out of disk space" , "code" :
  14031 , "n" : 0 , "connectionId" : 10 , "ok" : 1.0}

The permissions are correct to write to data folder, because first few hours application works correctly.

What is going on?

Thanks in advance.


texts: I have two instances in one server. I configured  the max memory with the same number on each instance.  Total 36 Gigs and I gave 17 to each. Why the target memory keep coming up and down on one instance? (moves from 17 to 12 or between) but in other instance its fixed at 17? Also the total server memory is fixed at 10 Gigs.


texts: Can I normalize my experimental data table further?

texts:
I am attempting to get a MySQL database to 3NF, but I'm unsure as to how to get there.  I'm new to database design, so please correct me if my understanding is incorrect.

I have 6 columns: date(yyyy-mm-dd), compound_type, location, method, value, unit

The database represents values of samples taken on a particular day at a particular location.  There are 21 locations that happen for every date event that samples are taken, and 10 different types of compounds that are tested at each location.  There are three different methods to run on each compound.  So there is a value (and associated unit column) for each method for each compound type at each location on each day.

I got a pseudo-1NF by setting up a table like so:

id(PK), date,       compound_type,  location,   method, value,  unit
1,      2011-07-03, 1,              1,          1,      foo,    g
2,      2011-07-03, 2,              1,          1,      bar,    g
3,      2011-07-03, 1,              2,          1,      789,    g
4,      2011-07-03, 2,              2,          1,      123,    g
5,      2011-07-03, 3,              2,          1,      345,    g

And et cetera for all other compounds and locations and methods.  Obviously, there are a lot of repeats of dates for every compound, location and method combination.  To me, it seems fairly redundant to have that much repetition.

My question is: is it possible to further normalize this database, or is using an id(PK) column like I have above the best way to go with all of the repeating dates?  Am I better off using indexes or unique keys?  Would it be better to have a table for every date which breaks down into the compound/location/method categories?

I'm a newbie, so any resources you can point me to would be excellent.

Eventually, I'm going to be writing a front-end to this database for reporting and graphing, so I'm looking for a database design that will allow me to use brief SQL statements for returning data.


texts: How can I use a 1-argument function in an "ANY"/"ALL" query?

texts:
Assuming I have the following function:

 CREATE OR REPLACE FUNCTION myfun(IN TEXT)
     RETURNS boolean
     LANGUAGE SQL
     IMMUTABLE
     LEAKPROOF
     AS $CODE$
         SELECT COALESCE(
             $1 ILIKE ANY (ARRAY[
                 'test',
                 'bar'
             ]),
             false
         );
     $CODE$;

Which returns true if $1 matches 'test' or 'bar'.

How can I use this function in another ANY query? For example:

 SELECT myfun ANY(ARRAY['hello', 'world']);  -- should return false
 SELECT myfun ANY(ARRAY['hello', 'foo']);  -- should return true ('foo' matches)
 SELECT myfun ANY(ARRAY['test', 'world']);  -- should return true ('test matches')

But this syntax is incorrect. And I don't quite know how to properly write this.

edit: more detail

In essence what I want to do is check if any one of a set of columns contains any one of forbidden terms. In other words, I have a set of forbidden terms, and have to check multiple columns for containment of that word. This will later be used in a RLS policy. In the example below, the function contains_forbidden_term is the function I would like to be able to write.

A small but important note: The same functions must be applied to other tables, but with varying number of arguments (hence the array as argument-type).

CREATE TABLE foo (
    name TEXT,
    last_name TEXT
);
CREATE USER bob;
ALTER TABLE foo ENABLE ROW LEVEL SECURITY;

CREATE POLICY bob_foo ON foo TO bob USING (contains_forbidden_term(ARRAY[name, last_name]));

GRANT SELECT ON foo TO bob;


texts: sp_ExecuteSQL, performance and table variables

texts:
Is anyone able to explain some behaviour I'm seeing with SQL Profiler? Taking the following two batches:

exec sp_executesql N'
    declare @t table (
        x int
    )

    insert into @t (x) select top 10 number from dbo.gennumbers

    select * from @t
    union
    select * from @t
'

exec sp_executesql N'
    select top 10 number from dbo.gennumbers
    union
    select top 10 number from dbo.gennumbers
'

(this is a simplification of some refactoring I'm doing; in reality the temp table is populated through a CTE that's reused in the final query, so mitigating the amount of duplicated work SQL Server needs to do by caching the data).

In this example, GenNumbers is simply a numbers table with a single column that just contains a sequential run of numbers - nothing special.

In profiler, I'm getting the follow results:

Batch 1, with the temp table:

Event             TextData            Reads
SP:StmtCompleted  INSERT INTO(...)    27
SP:StmtCompleted  SELECT * FROM ...   6
SQL:StmtCompleted exec sp_executesql  170

Batch 2, a direct hit:

Event             TextData              Reads
SP:StmtCompleted  SELECT TOP 10 * ...   6
SQL:StmtCompleted exec sp_executesql    6

What I'm trying to understand is the anomoly of the final read count on the SQL:StmtCompleted. I was expecting that the total reads should equal the sum of all reads that occur within the batch, however that doesn't seem to be the case in batch 1 - in fact, it's significantly higher. Whereas batch 2, that does a single select, final reads == actual reads.

There's nothing obvious in the execution plan to see where this extra time is going, but maybe I'm not capturing the right thing - does anyone have any thoughts on what's actually going on? I'm wondering whether I need to worry about the extra 100 reads that SQL Server is "giving" me, given that this read count is five or six times higher than the actual read count.

I'm assuming it's something to do with SQL needing to maintain information about the temporary table, but it's not clear exactly what. Any light shed would be useful.


texts:
Create schema Nevy;

create table sailor
(
  sid INT(5) NOT NULL,
  sname Varchar(20) NOT NULL,
  rating INT(5) NOT NULL,
  age INT(5) Not NULL,
  primary key (sid));

Create table reserve
(
  sid INT(5) NOT NULL,
  bid INT(5) NOT NULL,
  DAYT date NOT NULL,
  primary key (sid,bid,dayt),
  foreign key (bid) references boat(bid),
  foreign key (sid) references sailor(sid)
);

Create Table boat
(
  bid INT(5) NOT NULL,
  bname VARCHAR(10) NOT NULL,
  color VARCHAR(10) NOT NULL,
  primary key (bid) );

INSERT INTO nevy.sailor
values(22,'Dustin',7,45),(29,'Brutus',1,33),
  (31,'Lubber',8,55.5),(32,'Andy',8,25.5),
  (58,'Rusty',10,35),(64,'Horatio',7,35),
  (71,'zorba',10,16), (74,'Horatio',9,35),
  (85,'Art',3,25.5),(95,'Bob',3,63.5);

INSERT INTO nevy.boat
values(101,'interlake','blue'),(102,'Interlake','red'),
  (103,'Clipper','green'),(104,'Marine','Red');

INSERT INTO nevy.reserve
values (22,101,'1998-10-10'),(22,102,'1998-10-10'),
  (22,103,'1998-8-10'),(22,104,'1998-7-10'),
  (31,102,'1998-10-11'),(31,103,'1998-6-11'),
  (31,104,'1998-12-11'),(64,101,'1998-5-9'),
  (64,102,'1998-8-9'),(74,103,'1998-8-9');

select sname
from sailor
Where sailor.sid= reserve.sid
  AND bid=103;

When I execute the last select statement , it gives me the error

  unknown column: reserve.sid

I don't know why this error is being generated.


texts: IBM DB2 9.7, possible to temporarily disable transaction logs?

texts: I want to clean the whole database, so I am dropping all tables and DB objects in two FOR loops, within one transaction. This makes the transaction logs become full. I could increase the transaction log size, but I would much rather drop the database without anything going into the transaction logs. Is this possible to do?


texts: How to cache inserted data with ProxySQL to avoid delays

texts:
we are using MariaDB 10.2 with Galera plugin. Cluster consists of 4 nodes - 3 are busy service complex queries and 1 is more for a backup.

Recently we came across ProxySQL and this sounds very interesting for us in terms of splitting insert queries to one server and selecting data from others. We are hoping to avoid deadlocks and autoincrement problems during high activity.

But one of our developers has discovered a delay - when using ProxySQL he tries to insert a simple record and do instant select of that record. In general every second or third such select-request fails due to latency. If he adds tiny 0.01s delay - he never gets failure, as all galera nodes are able to sync within this time.

Now the question is - is it possible to cache inserted data with ProxySQL, so instant "select" would be returned from the cache, at least for the first 0.01seconds.

Or are there any other ways or suggestions in this scenario?

Thank you. :)


texts:
I have the following tables:

CREATE TABLE `Channels` (
   `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
   `title` VARCHAR(255) NOT NULL,
   `views` INT(11) NOT NULL DEFAULT 0,
) Engine = InnoDB;

INSERT INTO Channels (title, views)
VALUES ('Movies', 50030),
       ('TV shows', 51201),
       ('Howtos', 18601);

CREATE TABLE `Videos` (
   `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
   `channel_id` INT(11),
   `name` VARCHAR(255) NOT NULL,
   `description` TEXT,
   `views` INT(11) NOT NULL DEFAULT 0,
   `created_at` DATETIME NOT NULL,
   `updated_at` DATETIME,
   CONSTRAINT videos_channel_id_fk
       FOREIGN KEY ( channel_id )
           REFERENCES Channels ( id )
) ENGINE=InnoDB;

INSERT INTO Videos (`channel_id`, `name`, `created_at`)
VALUES (1, 'A', '2019-10-03 00:00:12'), (1, 'B', '2019-03-01 00:12:12'),
       (1, 'C', '2019-10-01 19:12:12'), (1, 'D', '2019-10-03 01:55:55'),
       (2, 'E', '2019-10-03 01:58:00'), (2, 'F', '2019-10-01 15:11:11'),
       (2, 'G', '2019-09-09 11:11:11'), (2, 'H', '2019-08-01 12:53:10');

I'm trying to get a distinct list of channels with their most recent videos.

For example for Channel 1, it would be Video 'D' at '2019-10-03 01:55:55', for Channel 2 it would be Video 'E' at '2019-10-03 01:58:00');

I've tried doing a self join like this,

SELECT v1.id, v1.channel_id, v1.name FROM Videos AS v1
LEFT JOIN Videos AS v2 ON v1.channel_id = v2.channel_id AND v1.created_at>v2.created_at
WHERE v1.created_at IS NULL

but my Video id's never match up with the latest video. i.e. I'll end up with video id: 1 where I want Video id: 4.


texts: Postgres text column too large for an index - searching for IP address, so I don't think text search will work

texts:
I have a Postgres database with a column that has a large amount of text in it.  There are potentially multiple IP addresses in the text, as well as a lot of other text.

I need to do a query

WHERE value LIKE '%' || ipValue || '%'

which would be like:

WHERE value LIKE '%192.168.2.1%'

The column is too big for an index:

  [54000] ERROR: index row size 2872 exceeds btree version 4 maximum
  2704 for index "rtfdata_value_index" Detail: Index row references
  tuple (34153,51) in relation "rtfdata". Hint: Values larger than 1/3
  of a buffer page cannot be indexed. Consider a function index of an
  MD5 hash of the value, or use full text indexing.

An MD5 hash of the column value clearly will not work, and I don't think that a full-text index will allow me to search on the IP (since it's not a language word).

What can I do here?

My current best idea is to write a script that will create yet another table with a record_id and an ip_address column that will search each text column with a regex and pull out all the IPs and create 1 row per IP/record in this table.  Then I can join this table when looking for the IP, instead of the wildcard search on the large text column.


texts: Should I duplicate columns between tables to speed-up aggregations like SUM?

texts:
I have two tables in PostgreSQL 10.12 database:

waste_card (id (PK), user_id, manufacture_date, transfer_date, address_id, card_type, .. (and other card_specific_firelds)
wastes (user_id, waste_card_id(FK), amount, ... (other wastes_specific_fields)

I need to frequently list and count user wastes (2 separated API endpoints) by address and manufacture_date or transfer_date
Now to list items I fire 2 queries: 1- load user cards then 2- load wastes
SELECT waste_cards.*
FROM   waste_cards
WHERE  waste_cards.user_id = $1
       AND (waste_cards.manufacture_date < '$2')
       AND (waste_cards.address_id = $3)
LIMIT $4

SELECT wastes.*
FROM   wastes
WHERE  wastes.waste_card_id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)

To just counting amount I fire:
SELECT SUM(wastes.amount)
FROM   waste_cards
LEFT   OUTER JOIN wastes
       ON wastes.waste_card_id = waste_cards.id
WHERE  waste_cards.user_id = $1
       AND (waste_cards.manufacture_date < '$2')
       AND (waste_cards.address_id = $3)

Should I add [manufacture_date, transfer_date, address_id] fields to wastes to speed up queries to be like:
SELECT SUM(amount)
FROM   wastes
WHERE  user_id = $1
       AND address_id = $2
       AND manufacture_date < '$3'

I'm on very beginning with this system. Queries are less than 150 ms. Today its about 1K users with 100-200 cards each and 2-5 wastes on each card. Not much yet but we want to add other 10-15K users next month. So I want to ask if this db schema is correct or should I change it before go live on production.


texts: Force create foreign key even if reference does not exist

texts: I have two tables foo and bar, bar has column a that references foo.id as a foreign key. When creating table bar before foo, the error column "a" referenced in foreign key constraint does not exist is given. This is obvious and intended. However, the creation of these two tables are handled by two different entities that of which I cannot control. It's guaranteed that no data will be added to either table until both are successfully created, but the creation itself may happen out-of-order. Is there a way to force the creation of bar even though foo does not exist?


texts: I am new to SQL SERVER, could anyone please explain that why do we have VLFs' in T-LOG and why not have 1 T-log itself. I am aware that VLFs' are logical but my question is why VLFs'? what is the benefit that we get from having VLFs'.


texts:
I have a "users" table:

And an organisations table ("orgs"):

And this is the pivot table:

I have some relations:

On the "users" table, I have a function called "orgs()" which is a belongsToMany relation to "orgs" (org_user,org_id,user_id).

On the "orgs" table, I have a function called "users()" which is a hasMany relation to "users" (org_user,org_id_user_id).

Now, in Laravel, when I do:

$orgUser = new OrgUser;
$orgUser->org_id=$org->id;
$orgUser->user_id=$user->id;
$orgUser->role_id=10;
$orgUser->save();

I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`eamorr`.`org_user`, CONSTRAINT `org_user_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)) (SQL: insert into `org_user` (`org_id`, `user_id`, `role_id`, `updated_at`, `created_at`) values (3, 1, -1, 2015-07-22 10:29:04, 2015-07-22 10:29:04))'

I would have thought I could just do something like:

$org->users()->attach(...)

or similar instead of having to reference the "org_user" table directly?

Does anyone have any experience at this?

Here's the User.php model:

namespace App;

use Illuminate\Auth\Authenticatable;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class User extends Model implements AuthenticatableContract {

    protected $table = 'users';
    public $timestamps = true;

    use Authenticatable;
    use SoftDeletes;

    protected $dates = ['deleted_at'];

    public function orgs()
    {
        return $this->belongsToMany('\App\Org', 'org_user', 'org_id', 'user_id')->withPivot('role_id');
    }

    public function files()
    {
        return $this->hasMany('\App\File', 'user_id');
    }

    public function clients()
    {
        return $this->hasMany('\App\User', 'user_user', 'owner_id', 'client_id');
    }

    public function tags()
    {
        return $this->hasMany('\App\UserTag', 'user_id');
    }

}

And here's the Org.php model:

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Org extends Model {

    protected $table = 'orgs';
    public $timestamps = true;

    use SoftDeletes;

    protected $dates = ['deleted_at'];

    public function files()
    {
        return $this->hasMany('\App\File', 'org_id');
    }

    public function locations()
    {
        return $this->hasMany('\App\Location', 'org_id');
    }

    public function tags()
    {
        return $this->hasMany('\App\OrgTag', 'org_id');
    }

    public function users()
    {
        return $this->hasMany('\App\User', 'org_user', 'org_id_user_id')->withPivot('role_id');
    }

}

And here's the OrgUser.php model:

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class OrgUser extends Model {

    protected $table = 'org_user';
    public $timestamps = true;

    use SoftDeletes;

    protected $dates = ['deleted_at'];

}


texts:
We have MySQL Front installed in our server, I can connect to it with no problems, there is a db with tables, everything fine.

Now I installed MySQL workbench (client) in the same server, but can't connect, I receive a "Malformed packet" error when I test connection. The port is 3306, hostname is localhost (as I'm logged in the server), user and password are the same.

Why shouldn't I be able to connect to the same server with workbench? Actually I need to connect to it using Entity Framework, but I can't due to this error.

I don't know what info would be useful (feel free to ask anything), I can only guarantee the windows service is running, the firewall is not blocking (I even tried disabling it).

If I try to connect with workbench using an "invalid" username (e.g "foobar"), the connection is sucessful (which is strange imo), but then I can only see the "test" database, and I can't see the actual database I want to (as in MySQL Front).


texts: What might be the best approach when converting or migrating a database containing over 2,000 tables? Where would you begin to tackle such an issue?  Steps to take in the first weeks of designing? Risks?  Assuming there is hardly any information about the old (current) database?


texts: Highest cost section in Actual Execution Plan doesn't show any logical reads?

texts:
In the following query plan - https://www.brentozar.com/pastetheplan/?id=H1dwOCHed (it has 3 sections, the last one is the problem), there's a node at the very bottom that consumes 37% of the resources. This is the actual query plan, not the estimated query plan.
However, the table it references does not show up at all, when looking at logical reads via Set Statistics IO. This includes the 2 sections for ActivityFeedTeamCache and ActivityFeedCache.
In the past, I've been able to use Logical Reads as a pretty good proxy for performance. Reduce the logical reads, and you've done the right thing for performance.
However, since in this case, there are no logical reads for the chunks that are the worst (ActivityFeedTeamCache, ActivityFeedCache, Locales), what is a good proxy for testing performance? Just that the % cost in the Actual Execution Plan goes down?


texts: How to index WHERE (start_date >= '2013-12-15')

texts:
I have a table named items, and inside the table there is a some_date column, with date as the datatype.

I was wondering how can I index the some_date column with PostgreSQL

SELECT "items".* FROM "items" WHERE (some_date >= '2013-12-15')

Bitmap Heap Scan on items  (cost=126.48..4671.78 rows=6459 width=384) (actual time=0.799..2.035 rows=7511 loops=1)
  Recheck Cond: (some_date >= '2014-03-30'::date)
  ->  Bitmap Index Scan on index_items_on_some_date_and_end_date  (cost=0.00..124.86 rows=6459 width=0) (actual time=0.744..0.744 rows=8777 loops=1)
        Index Cond: (some_date >= '2014-03-30'::date)
Total runtime: 2.439 ms

I've thought about partial index, but some_date is flexible in terms of the value used.


texts:
I'm getting the following error at the very end of SQL Server 2016 setup.

  Updating permission setting for file 'Local Machine' failed.  Principal 'SYSTEM\CurrentControlSet\Services\Eventlog\Appliaction\DatabaseMail' was tried to be added to the file permission setting.  The file permission setting was supposed to be set to 'account name'.

It seems like a permissions issue.  Any help would be greatly appreciated. Thanks


texts: What would a "properly designed" schema for roles and permissions look like?

texts:
I'm creating an application that has a similar roles and permissions system to Discord.

Basically

Users are members of groups
Groups have roles
Roles have permissions
And users can be associated with one role at a time.

I'm not sure how to design a system like this. Originally, I was thinking I'd have a roles table, and for each permission there would be a boolean column like: can_change_nickname.

That doesn't seem right though...

If you have any recommended reading, or can offer an answer with details on why it's done that way, I'd really appreciate that.

A USER is a member of 1 or more GROUPS
A GROUP can have 1 or more ROLES
A ROLE can have 1 or more PERMISSIONS
A USER can only get PERMISSION by being assigned a ROLE with those PERMISSIONS.
Every GROUP will share common permissions based on what features and content types the application has made available
Different ROLES can share the same PERMISSIONS.

So it looks like I'll need a table for both ROLES and PERMISSIONS. Now I just need to find what the PERMISSIONS table would look like.


texts:
I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me.

I have a master table which is then joined to another table but the table that is joined is based on some data in another column.

My application will in turn run queries such as:

SELECT f.dataA, m.data1, m.data2
FROM Master_Table m
INNER JOIN Foo f ON f.id = m.TableKey
WHERE TableName = 'Foo'

and

SELECT fb.dataA, fb.dataB, m.data1, m.data2
FROM Master_Table m
INNER JOIN Foobar fb ON fb.id = m.TableKey
WHERE TableName = 'Foobar'

This works as desired but I don't like the fact that my Master_Table.TableKey should probably be held to a foreign key constraint but this is impossible with this design (or at least I think so).

Should the TableKey column be split out to being a FooKey, BarKey and FoobarKey? This also then seems odd because I then guarantee that BarKey and FoobarKey will be null where TableName = "Foo".

Or is there a completely different way I should be approaching this?

EDIT: Funnily enough this recent meta.dba post seems to touch on this idea about inheritance but I'm not too sure what the best approach still is.


texts: Get data from a MDX linked server and use it on a FROM clause on SQL without OPENQUERY

texts:
i have the following SQL query on SQL Server 2012:

WITH MdxQuery (field1,field2,field3) AS (
    SELECT field1, field2, field3
    FROM OPENQUERY(my_linked_server, 'my_long_mdx_query')
)
SELECT * FROM MdxQuery

The 'my_long_mdx_query' is a MDX query running on a linked server. Everything works fine except when the MDX query is longer than 8000 characters, which is unsupported by OPENQUERY.

I have tried to use variables, but without success:

Declare @TempTable TABLE (
     field1 varchar(max),
     field2 varchar(max),
     field3 varchar(max)
)
Declare @strMDX varchar(max)
set @strMDX = N'my_long_mdx_query'
INSERT INTO @TempTable (
    field1,
    field2,
    field3
) EXEC (@strMDX) at my_linked_server

WITH MdxQuery (field1,field2,field3) AS (
    SELECT field1, field2, field3
    FROM @tempTable
)
SELECT * FROM MdxQuery

It throws this error before reaching the WITH MdxQuery part:

The requested operation could not be performed because OLE DB provider "MSOLAP" for linked server "my_linked_server" does not support the required transaction interface.

Is it any missing configuration? Is there any other approache i can use?
Thanks


texts: Is it possible to make schema changes on a replicated subscriber database?

texts:
I have a table that is published via transactional replication. A developer has requested modifying the subscribed table to add a column that is to be used for reporting purposes. The column will be updated via an outside process.

It is not prudent to add the column to the published table and insert the data there as the data that is needed to update the column is on the subscriber server.

Is this possible? Is there a better way?


texts: Is my JOIN syntax not valid? And have I understood JOIN correctly?

texts:
I've been reading up a bit regarding JOINs. And i just noticed that I've been writing the syntax a little bit different. Although I don't think there's any problem with it, I would like to hear your oppinions.

This is how I see just about every tutorials writes the syntax:

SELECT a.<columns>
    b.<columns>
FROM table_a a
    INNER JOIN table_b b ON a.pk = b.fk_table_a_pk

And this is how I've been doing it:

SELECT a.<columns>
    b.<columns>
FROM table_a a
    JOIN table_b b ON b.fk_table_a_pk = a.pk

As you can see; I've switched around the part where the comparison takes place.
Instead of a.pk = b.fk_table_a_pk, I do
b.fk_table_a_pk = a.pk. I don't know why, but I would guess it's because it made more sense - in my head - to continue "working" on the joined table (JOIN table_b b ON b.fk_table_a_pk =)

I've always gotten the expected result, so I've never thought about it. But is this wrong? Or is there situations, that I'm not aware of yet, where this could fail and return an unexpected result?

An additional question regarding LEFT and RIGHT joins.
Is it correct to say that the joined table (table_b) is the RIGHT table? - and that table_a is the LEFT table - in this case?
And if I continue the code like this:

....
FROM table_a a
    JOIN table_b b ON b.fk_table_a_pk = a.pk
    JOIN table_c c ON c.fk_table_a_pk = a.pk

they're both RIGHT tables relative to table_a?  And if I continue like this:

    ....
    JOIN table_c c ON c.fk_table_a_pk = a.pk
        JOIN table_d ON d.fk_table_c_pk = c.pk

now table_c is still RIGHT relative to table_a, but is now also LEFT relative to table_d - and of course then table_d is RIGHT relative to table_c - in this case?


texts: How do you set the pagesize, codeset and table space when creating a database in DB2?

texts:
I want to create a database in DB2.

I found some information on  this page

I want to use only some options which are explained on the page:

PAGESIZE
CODESET
TABLESPACE

Then, I want to add/configure the buffer pools.

How should the create SQL look ?

CRAETE DATABASE myDatabase
USING CODESET UTF-8 TERRITORY US
COLLATE USING SYSTEM PAGESIZE 8192

CATALOG TABLESPACE 16K  -- Is the 16K ok?
USER TABLESPACE 16K  -- Is the 16K ok?
TEMPORARY TABLESPACE 16K;  -- Is the 16K ok?


texts: Alternate per 2 postgresql rows, but start over if there are rows remaining

texts:
I'm trying to select below table, grouped by two (=max, if there is only one, show one) of each supplier_id. But, after all suppliers are selected it needs to start over. So this is the table:

Table: products

ID   Supplier_id   Name            created_at
---------------------------------------------
1       1          productname     2014-01-12
2       3          productname     2014-01-12
3       2          productname     2014-01-18
4       2          productname     2014-01-18
5       3          productname     2014-01-17
6       1          productname     2014-01-17
7       2          productname     2014-01-16
8       4          productname     2014-01-10
9       4          productname     2014-01-14
10      2          productname     2014-01-17
11      5          productname     2014-01-13

After selecting the rows I want this result:

Table: products

ID   Supplier_id   Name             created_at
----------------------------------------------
1       1          productname      2014-01-12
6       1          productname      2014-01-17
3       2          productname      2014-01-18
4       2          productname      2014-01-18
2       3          productname      2014-01-12
5       3          productname      2014-01-17
8       4          productname      2014-01-10
9       4          productname      2014-01-14
11      5          productname      2014-01-13
7       2          productname      2014-01-16
10      2          productname      2014-01-17

On https://stackoverflow.com/questions/15969614/in-sql-how-to-select-the-top-2-rows-for-each-group I almost found what I was looking for.

SELECT *
FROM   products as p
WHERE
        (
            SELECT  COUNT(*)
            FROM    products as pr
            WHERE p.supplier_id = pr.supplier_id  AND p.created_at >= pr.created_at
        ) <= 2
        order by p.supplier_id

But as I told, after 2 of each supplier_id's are selected it needs to start over if there are any rows left. Anyone who knows this is possible with a query?


texts: How can I remove only consecutive duplicate fields when the id is not consecutive in MySQL


texts:
In SQL Server, when setting permissions with a Grant statement, we do it on Users, not Logins. For example:

CREATE LOGIN login1 WITH PASSWORD = 'sssssssss';
use mydb;
CREATE USER user1 FOR LOGIN login1
grant delete on table1 to user1

So, users may or may not be able to do a certain task on a certain table.
But, when connecting to a database from JDBC and etc, we give the login name and the password (login1 and 'sssssssss' in here) in the connection string, not the User name!

So where do these grant statements show themselves?! We never mentioned User1 in the code! And, if we define two users for same login, each with different permissions, and then connect to database from JDBC, which of these user permissions are considered?


texts:
I'm trying to create an index on table [Children] in SQL Azure but get the following error:

  The operation failed because an index or statistics with name
  'IX_Children_ParentID' already exists on table 'Children'.

As far as I can see there's no index or statistics with this name. The following command returned zero results so there shouldn't be any indexes or statistics. Is that correct?

SELECT object_id, name FROM sys.stats WHERE name = 'IX_Children_ParentID'
UNION ALL
SELECT object_id, name FROM sys.indexes WHERE name = 'IX_Children_ParentID'

UPDATE: Changed the query in response to @Shark's anwer.

My SQL command for creating the index:

CREATE INDEX IX_Children_ParentID
ON [myschema].[Children]
(
    [ParentID] DESC
)

Before this error occurred I had problems with timeouts while creating the index. Could this be caused by some remnants?


texts: Why did DBCC SHRINKFILE never complete but shrink successfully after cancellation?

texts:
I have to deal with databases that have experienced a lot of data deletion and I am preparing to move the DBs to new Sql-Server installations and additionally to a new SAN.

The size of the data files ranges between 120 and 590 GB and the quote of used space ranges between 95% on the smaller and 30% on the larger files.

Because I plan to use log shipping to do the data migration and initial restore of a full backup is required, I would like to decrease the volume to an amount that makes sense on the larger and partially empty files, knowing all the negative side effects of shrinking the files.

I tried to shrink one of the files in a live system for a very tiny amount to get an initial feeling for the required time:

The data file was 450484 MB large having 153 GB unused. I tried:

DBCC SHRINKFILE (N'logicalfilename' , 450000);

So I tried to reclaim the ridiculous amount of 484 MB.
I cancelled this after 45 Minutes. Disk Waits increased during this period, mainly IO_COMPLETION and SLEEP_BPOOL_FLUSH.
The command caused ~32 Mio Reads and 400,000 Writes.

Suprisingly after I cancelled it and looked at the file's size, it has been resized to the anmount of 450000 MB as desired!

I made a second try with a shrink volume of 50 MB, cancelled after 4 Minutes and once again the shrink completed successfully.

Why did the DBCC command not return? What was it waiting for? How long did it take really?

Side note, we are using FULL recovery mode and take LOG Backups in 15 min interval. Does this have some negative impact?


texts: Restoring SQL Server Database Command Line Error

texts:
I have a restore database script in a SQL file, and I'm trying to restore a backup generated from one DB to a new DB (that exists). That SQL file is being executed by SQLCMD on the command line. The command window lets me know the restore finished successfully; however, the database is in a restoring state still, and cannot be accessed. Because of that, the rest of my batch process fails. How can I get the database out of the restoring state, and back into an online state so that I can run additional batch process scripts?

I'm using this to do it:

restore filelistonly
from disk = 'c:\program files\microsoft sql server\mssql10.mssqlserver\mssql\backup\olddb.bak'
GO

RESTORE DATABASE NewDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\olddb.bak'
WITH NORECOVERY, REPLACE,
MOVE 'OldDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\newdb.mdf',
MOVE 'OldDB_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\newdb.ldf'

Any ideas why my db is still in restoring state if it says it completed successfully?

Thanks.


texts: Why is SELECT * much faster than selecting all columns (in a different column order) by name?

texts:
On a table with columns a,b,c,d,e,f,g,h,i,j,k I get:

select * from misty order by a limit 25;
Time: 302.068 ms

And:

select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25;
Time: 1258.451 ms

Is there a way to make the select by column as fast?

Update:

No index on a table, newly created one

Here is the EXPLAIN ANALYZE, does not seem too helpful:

explain analyze select * from misty order by a limit 25;

Limit  (cost=43994.40..43994.46 rows=25 width=190) (actual time=404.958..404.971 rows=25 loops=1)
->  Sort  (cost=43994.40..45731.11 rows=694686 width=190) (actual time=404.957..404.963 rows=25 loops=1)
     Sort Key: a
     Sort Method: top-N heapsort  Memory: 28kB
     ->  Seq Scan on misty  (cost=0.00..24390.86 rows=694686 width=190) (actual time=0.013..170.945 rows=694686 loops=1)
Total runtime: 405.019 ms
(6 rows)

And:

explain analyze select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25;

Limit  (cost=43994.40..43994.46 rows=25 width=190) (actual time=1371.735..1371.745 rows=25 loops=1)
->  Sort  (cost=43994.40..45731.11 rows=694686 width=190) (actual time=1371.733..1371.736 rows=25 loops=1)
     Sort Key: a
     Sort Method: top-N heapsort  Memory: 28kB
     ->  Seq Scan on misty  (cost=0.00..24390.86 rows=694686 width=190) (actual time=0.015..516.355 rows=694686 loops=1)
Total runtime: 1371.797 ms
(6 rows)


texts:
I am trying to do datapump.

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.open( operation => 'EXPORT', job_mode => 'TABLE', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'tab1.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''USERS'')');
DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl,name => 'NAME_EXPR',value => 'IN (''CA_FILE'')');
DBMS_DATAPUMP.start_job(hdnl);
END;
/

But this throws an error:

Error report -
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3507
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3756
ORA-06512: at line 5
39001. 00000 -  "invalid argument value"
*Cause:    The user specified API parameters were of the wrong type or
           value range.  Subsequent messages supplied by
           DBMS_DATAPUMP.GET_STATUS will further describe the error.
*Action:   Correct the bad argument and retry the API.

Please help.


texts: Understanding how to choose between fields and tags in InfluxDB

texts:
What are some good rules and examples of how to choose between storing data in fields vs. tags when designing InfluxDB schemas?
What I've found so far is:

a measurement that changes over time should be a field, and metadata about the measurement should be in tags
tags and fields are effectively columns in the table. tags are indexed, fields are not
the values that are highly variant and not usually part of a WHERE clause are put into fields
Store data in fields if you plan to use them with an InfluxQL function
Tags containing highly variable information like UUIDs, hashes, and random strings will lead to a large number of series in the database, known colloquially as high series cardinality. High series cardinality is a primary driver of high memory usage for many database workloads.

But let's say you store filled orders in an e-commerce application: order id, sale price, currency.

Should the order id be a tag, or a field?
Should the currency be a tag, or a field?


texts: Moving the MySQL data directory Ubuntu 16.04

texts:
OS: Ubuntu 16.04

I am trying to move the MySQL data directory to another location. Here's what I did:

mysql -u root -p

select @@datadir;
Output
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+

sudo systemctl stop mysql

mkdir /data

sudo rsync -av /var/lib/mysql /data

I then verified that a /data/mysql/mysql directory was created and all the data was moved there.

Then I modified the /etc/mysql/mysql.conf.d/mysqld.cnf file:

datadir=/data/mysql

I then modified /etc/apparmor.d/tunables/alias. At the bottom of the file, added the following alias rule:
    alias /var/lib/mysql/ -> /data/mysql/,

then:

sudo systemctl restart apparmor

No errors so far.

I then did:

sudo mkdir /var/lib/mysql/mysql -p

followed by:

sudo systemctl start mysql

This gave me the following error message:

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

systemctl status mysql.service

gives me:

mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Thu 2016-09-01 08:09:04 UTC; 10s ago
  Process: 20380 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 20377 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 20380 (code=exited, status=1/FAILURE);         : 20381 (mysql-systemd-s)
    Tasks: 2
   Memory: 336.0K
      CPU: 221ms
   CGroup: /system.slice/mysql.service
           └─control
             ├─20381 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─20430 sleep 1

Sep 01 08:09:04 db.msrv.com systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Sep 01 08:09:04 db.msrv.com systemd[1]: Stopped MySQL Community Server.
Sep 01 08:09:04 db.msrv.com systemd[1]: Starting MySQL Community Server...
Sep 01 08:09:06 db.msrv.com systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

Any ideas?


texts: Plan cache memory: parameterized SQL vs stored procedures

texts:
In making a case to disallow parameterized SQL in my company's development environment the lead developer related a story about how the last time they used parameterized SQL the server had major performance issues. He said this was because the plan caching ate up almost all of the available memory on the server and switching to stored procedures cleared up the performance issues.

My question: is there a major difference in the memory footprint of a compiled/cached stored procedure and the cached plan for a parameterized SQL?

I have a guess that they also simplified the number of calls by going to procs and that probably had as much impact or more than just going to procs by itself, but I don't know.


texts: How should I store changes to items in the database?

texts:
I have items (tracked with itemid) that have attributes assigned to them (field1,field1 for example).  At any given time, field1=val1, field2=val1, ..., fieldn=valn where val1,val1,...,valn = 1..100. and n = 1..20

I need to design a DB to track the changes for each field, and I'm not sure if I should list each field as a column (for example table1), or list each change as a row (for example table2).

create table1 (datetimeofchange datetime2, itemid bigint, field1 smallint, field2 smallint, field3 smallint, field4 smallint, ..., fieldn smallint)

create table2 (datetimeofchange datetime2, itemid bigint, fieldval smallint, changeval smallint)

create table2lookup (fieldval smallint, fieldname varchar(50))

What are the trade-offs I get from choosing between the two designs?  Is there another design which would have different trade-offs?  Is there some terms I can google to research this issue?

Thanks.


texts:
I am planning on using UUID field for storing items which can have a parent item. I am using PostgreSQL 9.5. I imagine there will be 3-4 levels at most. The design is to hold a structure such as Corporations->sub-companies->stores/shops So perhaps +/- 2 levels.

Now if I have the item and parent on same table wouldnt it be a problem in future if I want to divide data into multiple databases? What would be the best way to proceed?

                 uuid                 |             parent_uuid
--------------------------------------+--------------------------------------
 89b4052a-fd9a-43d3-b5f7-c69ee6ea9f5b |
 c569ae30-87af-6bcf-fdd1-8143e3bae27a | 89b4052a-fd9a-43d3-b5f7-c69ee6ea9f5b
 d7460788-ff07-e82d-83ed-5a0182ae2fbf | 89b4052a-fd9a-43d3-b5f7-c69ee6ea9f5b


texts: how to insert big data in mariadb with execution time less than max_statement_time value

texts:
i have some table with this structure.
CREATE TABLE `trcdmppdetail` (
  `mpp_code` varchar(50) NOT NULL,
  `company_code` varchar(50) NOT NULL,
  `mppdetail_date` datetime(3) NOT NULL,
  `position_id` int(11) DEFAULT NULL,
  `basedon_code_1` varchar(50) NOT NULL,
  `basedon_code_2` varchar(50) NOT NULL,
  `mpp_emp` int(11) DEFAULT NULL,
  `actualemp_onrequest` int(11) DEFAULT NULL,
  `dept_id` int(11) NOT NULL,
  `created_date` datetime(3) DEFAULT NULL,
  `created_by` varchar(50) DEFAULT NULL,
  `modified_date` datetime(3) DEFAULT NULL,
  `modified_by` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`mpp_code`,`company_code`,`mppdetail_date`,`basedon_code_1`,`basedon_code_2`,`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=3488

and value on global variable max_statement_time=60
i insert 50k row data on thats table but i got error because my query run more than max_statement_time
my dba can't allow me to change global variable max_statement_time=0 becuase he says will be distruption the server.
so how to i insert the data with execution time less than max_statement_time value.


texts: Calculate number of days since from timestamp

texts:
I have a column in my database table containing timestamp.

SELECT id, millisecs, to_timestamp(millisecs) AS date_time FROM my_table;
id      | millisecs  |       date_time
--------+------------+------------------------
 126952 | 1494161691 | 2017-05-07 13:54:51+01
 126952 | 1494161692 | 2017-05-07 13:54:52+01
 126952 | 1494161693 | 2017-05-07 13:54:53+01
 126952 | 1494161694 | 2017-05-07 13:54:54+01
 126952 | 1494161695 | 2017-05-07 13:54:55+01

Then I want to calculate the number of days since '1899/12/30' using either the millisecs or date_time columns. As my table has large number of rows, doing this with a python function in notebook kills the kernel after sometime.


texts: msdb.dbo.sp_start_job got stuck in stored procedure

texts:
I have a stored procedure USP_A with logic like following:

......
BEGIN TRY
exec dbo.usp_Log 'Start to run job job1'
Exec @intErrorCode = msdb.dbo.sp_start_job 'job1'
  IF @intErrorCode <> 0 Goto errorHandling
exec dbo.usp_Log 'End to run job job1'
Return @intErrorCode
END TRY
BEGIN CATCH
  exec dbo.usp_Log 'Error......'
END CATCH
......

But when I run this stored procedure, it got stuck and when I check log I can only see message 'Start to run job job1'. And no error being caught in CATCH block. Also in the SQL Server Agent job monitor I can not see this job get triggered.

But if I manually run

Exec @intErrorCode = msdb.dbo.sp_start_job 'job1'

it works fine.

The SQL Server is Microsoft SQL Server 2005 Enterprise Edition (version 9.00.5000.00)

Update

It seems that the sp is crashed rather than hungup. Because I used sp_WhoIsActive to find which query is waiting and no record returned.

I also tried to comment the start job logic and just

Return @intErrorCode

However the SP still hangs (or crashes).


texts: reapply permissions post sql server restart

texts:
In our environment, users were granted read/write/ddladmin permissions on tempdb database (dev/qa/prod).
These permissions will be lost when SQL Services restart, as tempdb rebuilds every time. One solution that is currently in place is - reapply the permissions scripts from a SQL Agent job, when ever SQLAgent starts. This is not accurate, as the users/permissions will be modified over time. Another thought, is take a daily backup (with a 15day retention policy) of tempdb permissions and use the same job to pick the latest file to reapply the permissions when SQL restarts. Is this an accurate solution for the problem.
Thanks!!


texts: How to count multiple values of a column as one group?

texts:
I have a table main with the following rows:

 id |  name  | rank
----+--------+------
  1 | Ali    | a
  2 | Sami   | b
  3 | Khan   | c
  4 | Kamran | d
  5 | Imran  | e
  6 | Asad   | a
  7 | Nawid  | v
  8 | Jamil  | c
  9 | Usman  | j

I want to count rows with certain values in column rank. For example, I want to group values as follows:

Values (a,b,v) should come in one group by name myvalues.
Values (c,d,j) should come in another group by name yourvalues.
Value (e) should come to another group by name extravalues.

My desired result:

myvalues | yourvalues | extravalues
   4     |     4      |      1

myvalues counts 4 because it consists of (a,b,v). There are 2 occurrences of a, 1 occurrence of b and 1 of v - a total of 4.
The same is the case with yourvalues which consists of (c,d,j) and the occurrences of these values makes a total of 4.
And the last group extravalues consists of (e) and counts only 1 row.


texts: SQL Server transaction log file refuses to grow

texts:
Problem: I have a SQL Server database sitting on a 1 TB drive. The log will not grow past 5 MB even when the MAXSIZE is set much larger than 5 MB.

Error received:

  The transaction log for database 'Temp' is full. To find out why space
  in the log cannot be reused, see the log_reuse_wait_desc column in
  sys.databases

Questions:

How do I fix this?
Why is this happening?

Code:

CREATE DATABASE Temp ON PRIMARY(
       NAME = Temp
       , FILENAME = 'C:\\Temp.mdf'
       , SIZE = 2MB
       , FILEGROWTH = 10%)
LOG ON (
       NAME = Temp_Log
       , FILENAME = 'C:\\Temp.ldf'
       , SIZE = 1MB, MAXSIZE = 70MB
       , FILEGROWTH = 10%)

Result of select name, log_reuse_wait_desc from sys.databases where name = 'temp'; :

  name, log_reuse_wait_desc
  Temp, NOTHING

Additional Information:

Sql Server manager says maxsize = 5mb and restricted growth
Recovery mode Simple
Database is handling hundreds of individual SQL Statements as it is for a data integration tool

Updated Addition information:

When the SQL statement is run directly in the SQL server manager it creates successfully and does not cause errors on implementation...On the other hand if the create database statement is run progmatically using System.Data.SqlClient and the exact same syntax it creates the Database with all defaults. I have to additionally supply alter database statements to achieve the proper behavior.

Code to run Queries:

public void AcuConvert()
        {
            using (DestD)
            {
                SqlCommand command = new SqlCommand();
                DestD.Open();
                command.Connection = DestD;
                foreach (var item in Entity.SqlDestinationQueries.ToList())
                {
                    command.CommandText = item.Query;
                    command.ExecuteNonQuery();
                }
                foreach (var item in Entity.SystemQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Sys", SysD.Database);
                    command.ExecuteNonQuery();
                }
                foreach (var item in Entity.InsertQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Source", SourceD.Database); ;
                    command.ExecuteNonQuery();
                }

            }
        }


texts:
I'm looking for a way to enable switching between users in pgAdmin 4. I'm aware of the possibilty to change user from the properties-dialog before connecting. However this isn't useful when I'm already logged in, since it seems impossible to logout and get permission to change user in the properites-dialog.

Anyone have a solution for this? Is it possible having mutiple connections instead, one per user?


texts: How to SUM previous sum, e.g N = (N-1) + (N-2) + ... + 1?

texts:
I have a table name "TABLE_A (id integer, no integer)" .

I want to sum "no" with group by "id" and current "sum of no" = previous "sum of no"

Here is my code :

1/ Create table & insert data:

create table table_a (id int, no int);

insert into table_a values(1, 10);
insert into table_a values(1, 20);
insert into table_a values(1, 30);
insert into table_a values(2, 100);
insert into table_a values(2, 200);
insert into table_a values(2, 300);
insert into table_a values(3, 1);
insert into table_a values(3, 2);
insert into table_a values(3, 3);
insert into table_a values(3, 3);

2/ Desired result:

id | sum_of_no
--------------
1  | 60
2  | 660
3  | 669

3/ My solutions (ok):

with t_report_code_temp as
(
select id, sum(no) as t_code
from table_a
group by id
)
select a.id, sum(b.t_code)
from t_report_code_temp a
join t_report_code_temp b on b.id <= a.id
group by a.id
order by 1

My question:

Could you give me the better way to solve ?


texts: How to interpret Azure SQL QueryStore Query Performance Insight

texts:
The Azure SQL QueryStore is supposed to give you insight into what is causing high DTU usage, but I can not figure out what is going on.
In some cases, I get good data out of it, and I can pinpoint to the offending query, but often I come across a situation like below:

So here we have a peak of 45% DTU usage, all caused by CPU, yet the total of the top 20 queries is < 10%. So what is causing the other 30% of DTU usage?


texts:
When I connect to some e-commerce website with my browser I use HTTPS that uses a certificate that (more or less) guarantees that example.com is indeed example.com.

Now I connect to a SQL Server somewhere on the Internet - it might be SQL Azure for example and then I just connect to a URL like abcdef.database.windows.net. There's a chance that there's a fake server there and then I expose my sensitive data. I haven't found any evidence that certificates or anything equivalent is used when connecting to SQL Server.

How is SQL Server host identity ensured?


texts: What causes DBCC CHECKTABLE to return "session is in the kill state"?

texts:
A scheduled job step ran DBCC CHECKTABLE on a table but returned the following error:

Cannot continue the execution because the session is in the kill state. [SQLSTATE HY000] (Error 596). The step failed.

What causes this error?


texts: How to replace a single table with Identity Primary Key column from a database backup?

texts:
I need to restore a table from a backup. My understanding is the only way to get a single table back into the database is to copy it into a temporary table. Then drop the old table and rename the new one to the old name. My problem comes with this particular step due to the Primary Key column being an Identity column. The table also has multiple foreign key constraints. How can I copy one table from my backup database into the original database and still maintain the Primary key identity column and all foreign constraints?

Here is my current scripted attempt:

CREATE TABLE dbo.Incidents_tmp
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[IncidentAction_ID] [int] NULL,
[IncidentCompromise_ID] [int] NULL,
[IncidentDamage_ID] [int] NULL,
[IncidentReportingEntity_ID] [int] NULL,
[IncidentStatus_ID] [int] NULL,
[IncidentType_ID] [int] NULL,
[IncidentID] [varchar](20) NULL,
[Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_Incidents_tmp] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Incidents_tmp ON
GO

IF EXISTS ( SELECT *
        FROM DB_old.dbo.Incidents )
INSERT INTO DB_new.dbo.Incidents_tmp
        (
        ID ,
        IncidentAction_ID ,
        IncidentCompromise_ID ,
        IncidentDamage_ID ,
        IncidentReportingEntity_ID ,
        IncidentStatus_ID ,
        IncidentType_ID ,
        IncidentID
        )
    SELECT
    ID ,
    IncidentAction_ID ,
    IncidentCompromise_ID ,
    IncidentDamage_ID ,
    IncidentReportingEntity_ID ,
    IncidentStatus_ID ,
    IncidentType_ID ,
    IncidentID ,
    FROM DB_old.dbo.Incidents TABLOCKX
GO

SET IDENTITY_INSERT DB_new.dbo.Incidents_tmp OFF
GO

ALTER TABLE dbo.IncidentSubject DROP CONSTRAINT
FK_IncidentSubject_Incidents;
ALTER TABLE dbo.IncidentsReportLog DROP CONSTRAINT
FK_IncidentsReportLog_Incidents;
ALTER TABLE dbo.Incidents DROP CONSTRAINT PK_Incidents;

DROP TABLE DB_new.dbo.Incidents
GO

Exec sp_rename 'Incidents_tmp', 'Incidents'

Exec sp_rename 'PK_incidents_tmp', 'PK_Incidents'

ALTER TABLE [dbo].[IncidentSubject]  WITH CHECK ADD  CONSTRAINT
[FK_IncidentSubject_Incidents] FOREIGN KEY([Incident_ID])
REFERENCES [dbo].[Incidents] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[IncidentSubject] CHECK CONSTRAINT
[FK_IncidentSubject_Incidents]
GO

When I try adding the constraints back in I get an error stating:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_IncidentSubject_Incidents". The conflict occurred in database "DB_new", table "dbo.Incidents", column 'ID'.

EDIT:
Some additional info. The user of this application deleted 175 incidents by accident. Unfortunately she didn't tell us until two weeks later and then it wasn't even routed to us until almost a month later. In users have been accessing the application and making changes so a complete database restore is not an option. The user is requesting just the deleted incidents be restored. I looked into the database and noticed an incidents table with the missing data in the current database and the 175 records existing in the backed up database. Hope that provides some better context for why I'm trying to restore just a single table of incidents. It starting to look like additional tables need to be restored.


texts: Query data from two tables and substitute foreign keys of first table with values of second


texts: Insert into multiple tables with one query

texts:
I would like to add data to at least three tables with one query. I thought of something like this:

WITH ins AS (
  INSERT INTO core.adr
    (street, "number", postal_code, city)
  VALUES
    ('test 1', '25a', '00912', 'villageman')
  RETURNING id)
INSERT INTO core.adr_information
  (idref, info)
SELECT id, 'test data'
FROM ins;

which works perfectly for exactly two tables.

Main problem here is, that all additional queries require the id value from the first INSERT query, which seems not manageable this way. I think it could be easily done with a stored procedure or transaction, but I would like the solution to be a simple and solid query.

Am I overlooking something? Is it possible that way or another (no stored procedures* or transactions)?

Note

* Altough, strictly speaking, Postgres does not have stored procedures, only functions, as @Erwin Brandstetter rightly pointed out via comments. See this series of posts for relevant information.


texts: How to extract binlog related to a single table in MySQL from binlog file?

texts:
I am using MySQL 5.7.31 and binlogs enabled in MIXED format.
I am trying to perform a partial PIT Recovery, i.e. restoring  specific table in a specific instant of the past. After restoring my last partial backup, of the table I need to take back in time, I am looking for a way to apply the binlogs related to it, up  to my desired point in time.
Is there a way, by mean of any command e.g. "sed", to extract the binlog of a single table from a binlog file, in order to perform the single table Point-in-time Recovery?
I did something similar for the restore of the single table. My doubts come from the form the binlogs are written and I haven't a clear idea on what poprtion to isolate and how to actually perform it, to achieve my goal above explained.


texts: Query to retrieve aggregated data using variables

texts:
Question:
Summarise events using the MIN, MAX and COUNT functions

Help:
To do this, create variables to hold each of the aggregates:

Variable          What it will hold eventually
---------------   -----------------------------
@EarliestDate   = The earliest date
@LatestDate     = The latest date
@NumberOfEvents = The number of events
@EventInfo      = The title Summary of events

Now use a single SELECT clause to set the value for all of these variables (alternatively you could use a series of subqueries), write another SELECT clause to show the value of the variables, to show the summary output shown above.

Result:

I'm having trouble getting this exercise done.

Is there anywhere I can get the solution?

Thanks in advance!