texts: Is it better to shrink a database or defragment to improve performance?

texts:
I'm no DBA hence me visiting here. I do however manage an application which writes to a SQL database. We are experiencing poor performance with the application now. Pages taking forever to load up and unexpected odd behaviour such as pages loading that shouldn't etc.

To improve performance would I be better off defragmenting the database or shrinking it? Why is shrinking so bad? Is it possible to explain that in a couple of lines?


texts: Does SQL Server allow INSERTs into tables and indexes to be written in parallel?

texts: If I have a table that has many indexes, and I run a statement that inserts rows into the table, will SQL Server insert rows one-at-a-time, or will the rows be inserted using parallelism?


texts: Offset vs Where performance for pagination with index

texts:
Suppose that you have a list of millions of items, each one with a timestamp (created_at).

You also have an index on created_at DESC.

Sometimes you need to navigate through all items, chronologically, and obviously you will use pagination on created_at.

Now I have a question about pagination on big data: considering that there's an index, and no other filter conditions on items, is there any performance difference in using OFFSET or using WHERE created_at > last_item_timestamp?

I mean, is there any difference in the way that the btree index will be accessed? Is there any difference in the amount of data that will be loaded from disk?

I ask this because I would say that there's no difference in the traversal of the btree, using OFFSET or WHERE, but from some quick tests that I have made it seems to be a performance difference. In particular I am testing this with PostgreSQL 11.

Update: after more reasoning I think that there's actually a difference in the number of tree nodes that have to be read. In case of OFFSET you need to read all the N nodes before the item, while with WHERE you only need to read log(N) nodes (because you don't need to count them).


texts:
I'm trying to simply change the length of a column within a table through SQL Server Management Studio, but receive a 'Validation Warning':

'MyTable' table
-Warning: The following schema-bound objects will be modified:
 -View 'dbo.MyViewName': indexes and schema binding will be removed.

I remember from experience that if I actually go through with this, the binding will not be readded. In the past I've had to manually write a script to re-add the view. This doesn't seem right - why would it just remove it without re-adding it at the end of the operation?

It makes me think I don't have something set up correctly, or am going about this the wrong way - am I? Or is it 'correct' procedure to have to manually re-add things in this way after table changes are made?


texts: Multiple errors import Oracle 12c DB impdb

texts:
I am a custom developer and I need to restore an Oracle DB and receiving multiple errors during the import. Very new to oracle, thanks in advance

System has been granted read/write on dump directory

CREATE OR REPLACE DIRECTORY dpump_dirOW AS 'E:\Dump';
GRANT READ, WRITE ON DIRECTORY dpump_dirOW TO SYSTEM;

impdp system/system@oracle1 FULL=Y DIRECTORY=dpump_dirOW DUMPFILE=full_EDML48T1_export_121019.dmp LOGFILE=5expdp_Merit121619.log exclude=table_statistics;

Errors from log file

;;;
Import: Release 12.2.0.1.0 - Production on Wed Dec 18 08:37:30 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@oracle1 FULL=Y DIRECTORY=dpump_dirOW DUMPFILE=full_EDML48T1_export_121019.dmp LOGFILE=4expdp_Merit121619.log
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE:"TOOLS" failed to create with error:
ORA-01119: error in creating database file '/oradata/EDML48T1/tools03.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

Failing sql is:
CREATE TABLESPACE "TOOLS" DATAFILE '/oradata/EDML48T1/tools03.dbf' SIZE 805306368 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,'/oradata/EDML48T1/tools01.dbf' SIZE 805306368 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,'/oradata/EDML48T1/tools02.dbf' SIZE 805306368 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
ORA-39083: Object type TABLESPACE:"EDMDATA4" failed to create with error:
ORA-01119: error in creating database file '/oradata/EDML48T1/edmdata401.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

Failing sql is:
CREATE TABLESPACE "EDMDATA4" DATAFILE '/oradata/EDML48T1/edmdata401.dbf' SIZE 805306368 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,'/oradata/EDML48T1/edmdata402.dbf' SIZE 805306368 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,'/oradata/EDML48T1/edmdata403.dbf' SIZE 805306368 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
ORA-39083: Object type TABLESPACE:"EDMHIST1" failed to create with error:
ORA-01119: error in creating database file '/oradata/EDML48T1/edmhist103.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

And on and on....


texts:
I connect to SQL Server with a given user that is not an administrator. I want to get a list of all my open connections. I've found endless commands to do so but all fall in one of those categories:

I'm not allowed to run that command:

  Msg 297, Level 16, State 1, Line 1
  The user does not have permission to perform this action.

I only see my current session!

Is it possible to list all sessions from current user?


texts: SQL Server Replication or Backup via sftp

texts:
I have a SQL Server database that is hosted in a cloud service as part of one of our ERP systems . Currently we are getting daily backup dumps via sftp which i am restoring to our on prem SQL Server for the departments to use with Services like PowerBI, SSAS and on site SSRS. Recently a request was made to try and increase the frequency of the updates. What i was wondering is if this would be a task for which Replication would be a better tool than more frequent backups?

Can you even setup a Snapshot & Distribution Agent to basically place a file in a location and then have another program pull the snapshot down via sftp and have a subscriber then pull that snapshot in to the local database?

Esentially a push type Replication (merge or snapshot) where the subscriber can not directly connect to the Distributor.

Or is more frequent backups with the associated locks and overhead a better solution?


texts: MySQL GROUP_CONCAT Query, is there a better way?

texts:
I have the below Data / Query. Is this really the most efficient way of doing this? As it seems like a lot of work to get the result.

I've tried reducing it / combining the parts  but this is the only way I can get it to work.

(n.b. The actual table has a lot more entries over multiple days - hence the WHERE filters at the end)

CREATE TABLE test.`actest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `LS` varchar(25) NOT NULL,
  `ET` datetime NOT NULL,
  `STATUS` int(11) unsigned DEFAULT NULL,
  KEY `ix_id` (`id`)
) ENGINE=InnoDB;

INSERT INTO test.actest (`LS`,`ET`,`STATUS`)
VALUES ('aaa',NOW(),'123'),('aaa',NOW(),'123'),('aaa',NOW(),'123'),('aaa',NOW(),'456'),
('aaa',NOW(),'456'),('aaa',NOW(),'789'),('bbb',NOW(),'123'),('bbb',NOW(),'123'),('bbb',NOW(),'456');

SELECT DATE(`ET`) AS ED, `LS`, GROUP_CONCAT(DISTINCT(cnt2) ORDER BY cnt2 SEPARATOR ', ') AS STATUSES
FROM
    (
    SELECT `ET`, `STATUS`, `LS`,CONCAT(`STATUS`,' (',cnt,') ') AS cnt2
    FROM
        (
        SELECT `LS`,`ET`,`STATUS`,COUNT(`STATUS`) AS cnt
        FROM actest
        GROUP BY `LS`, `STATUS`
        ) a
    ) b
WHERE `ET`
    BETWEEN CONCAT(DATE_SUB(CURDATE(), INTERVAL 0 DAY),' 00:00:00')
    AND CONCAT(DATE_SUB(CURDATE(), INTERVAL 0 DAY),' 23:59:59')
AND `STATUS` > 0
GROUP BY `ED`,`LS`;

Result:
ED_________| LS__| STATUSES__________________
2015-10-01 | aaa | 123 (3) , 456 (2) , 789 (1)
2015-10-01 | bbb | 123 (2) , 456 (1)

Also, is there an easy way to get a TOTAL Count for each LS field, e.g

aaa | 6
bbb | 3


texts: MongoDB conditional query for array $size

texts: I have a user collection, and each user has different number of id_cards, all id_cards are stored in an array, I can get all users that have 7 id_card by using array $size operator, how do I get the users with say more then 5 id_cards?


texts: setting statistics time on/off giving different results

texts:
I am tuning an SP and re-written that to optimize. Now I am comparing old code and new code in terms of timing using "setting statistics time on/off".

While setting  time statistics off, my new code is performing well at least 4 times better than old code (Old code is taking 4 secs and new code is taking 1 secs to execute), but when I am setting time statistics On, my new code is taking approx 12 secs and old code is taking around 7 secs.

Why new code is performing badly after setting time stats on? Looks like there is some cost of time stats also and in my new code, that cost is very higher in comparison to old code. Am I right? If so what is that?


texts: Import using pgAdmin3 produces ERROR: duplicate key value violates unique constraint

texts:
I am importing from the GUI a CSV file into a table. I have created the following table:

CREATE TABLE jira_tickets.daqa_rpt_tbl
(
  daqa_report_id bigint NOT NULL DEFAULT 'jira_tickets.daqa_id_seq'::regclass,
  project_name character varying(250) NOT NULL,
  jira_key character varying(250) NOT NULL,
  jira_summary text NOT NULL,
  issue_type character varying(100) NOT NULL,
  jira_status character varying(100) NOT NULL,
  jira_resolution character varying(250),
  jira_assignee character varying(250) NOT NULL,
  jira_reporter character varying(250) NOT NULL,
  jira_created text,
  jira_last_viewed text,
  jira_updated text,
  jira_resolved text,
  last_updated timestamp without time zone DEFAULT now(),
  CONSTRAINT "daqa_report_id_PK" PRIMARY KEY (daqa_report_id)
);

When I import I get this error.  Clearly the sequence is not incrementing when I move to the next row. Here is how I declare the sequence:

  CREATE SEQUENCE jira_tickets.daqa_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 1000000000000000000
  START 1
  CACHE 1;
ALTER TABLE jira_tickets.daqa_id_seq
  OWNER TO postgres;
GRANT ALL ON TABLE jira_tickets.daqa_id_seq TO public;
GRANT ALL ON TABLE jira_tickets.daqa_id_seq TO postgres;

Any suggestions or trouble shooting would be greatly appreciated.


texts: Streaming replication and lag when performing VACUUM on master

texts:
My streaming replication standby server is showing a delay of a few minutes when vacuuming a large table on the master.

What is the reason for the lag, and is there a way to reduce it?

Both servers have identical hardware on a 10GB network. Relevant settings are:

wal_buffers = 16MB
wal_writer_delay = 20ms
checkpoint_segments = 16
shared_buffers = 14GB
archive_timeout = 10
maintenance_work_mem = 1GB


texts:
I have 2 separate case statements which are producing the correct results:

Case when A_staff_Service_Type.type = 'v'
  then isnull(Indirect_Duration,0)/60.00 else null end

The above produces 6.5 for 1 employee.

Case when pt_service.service_code = 'N06 '
  then isnull(direct_Duration,0)/60.00 else null End

The above produces 5.5 for the same employee.

So I have two separate outputs of 6.5 and 5.5. What I would like is the output to be 12. I have tried

Case when A_staff_Service_Type.type = 'v'
      then isnull(Indirect_Duration,0)/60.00 else null end
+

Case when pt_service.service_code = 'N06 '
      then isnull(direct_Duration,0)/60.00 else null End

But the above results in 0 instead of 12.

This is a query writer within a clinical application based on SQL Server 2008.


texts:
I am running a Drupal site that is backed by Postgres. This is my first time running something with Postgres; I come from the MySQL world.

For doing dev work on the site, I connect to a VPN and tunnel my traffic through a machine that has an IP that is allowed in pg_hba.conf.

The queries are coming back very slow. My logs tell me that loading the homepage on the dev server:

  Executed 86 queries in 84.26 ms

And on my local machine (with the remote connection):

  Executed 87 queries in 22368.05 ms

Queries are 80% SELECT, with a few INSERT INTOs.

I have done some rudimentary tuning also on the db server, hoping to speed things up for the remote connection (the tuning did not, but it certainly made the dev server's queries come back faster!).

On the server's db config files, I have turned of ssl and only allowed connections from localhost and ips within the VPN. I am authenticating db users via md5.

Could the hold up be going through this VPN? Or could it have to do with the fact that the db is being accessed from two different places with the same username/password?


texts: Moving large database to another drive, how to measure performance before, and setup some type of replication for fast fail back?

texts:
Sorry if this is kind of more than question at a time, but here goes.

Running SQL Server 2008 on windows server 2008 as well. We have a large database just over 200gb right now (it holds images of documents that are scanned in, the 3rd party app is using an image column. it also has some other data that is accessed, including licensing info for the program)

The data files for it are still on the c drive because someone decided to set it up that way. It's been growing bit by bit and the drive is getting dangerously low on space. I have an iScsi drive I want to move it to before anything bad happens (there are log files on this drive right now, but it has a ton of free space and I don't really have any other options right now), but I need to be sure of a few things first.

I want to find a good way to measure the i/o rates on the files as it currently stands, but even after searching I'm not sure of the best way to do this over the course of a day or two to have a good idea.
I also want to test the throughput of the new drive I plan on using to make sure it is suitable. I was thinking of using SQLIO for this, but haven't looked into it much yet.
Lastly, just in case there is some serious performance issues, I was thinking it might be a good idea to have some kind of replication setup, probably asynchronous to avoid performance issues, so that we can quickly revert back to the original files without having to lose any data or wait for the new file to copy back over again.

Thanks


texts: Logon failed for login due to trigger execution

texts:
I have been trying to login using one of my sql server logins but I am getting the error message below:

General Error Message

Copy Message Text

TITLE: Connect to Server
------------------------------

Cannot connect to MY_SERVER.

------------------------------
ADDITIONAL INFORMATION:

Logon failed for login 'cola' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

For help, click: http://go.microsoft.com/fwlink?> ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Advanced Information

I am aware of the question below but it is slightly different and I have tried everything that is said there and it did not work for me, that's why I am putting this question here:

“Logon failed for login 'sa' due to trigger execution.” When No SA Login Triggers Defined

This question here is very similar too:

Login failed for user - Error: 18456, Severity: 14, State: 38

And from this one I got the following info:

Login failed for user - Error 18456 - Severity 14, State 38

1      'Account is locked out'
2      'User id is not valid'
3-4    'Undocumented'
5      'User id is not valid'
6      'Undocumented'
7      'The login being used is disabled'
8      'Incorrect password'
9      'Invalid password'
10     'Related to a SQL login being bound to Windows domain password policy enforcement.
        See KB925744.'
11-12  'Login valid but server access failed'
16     'Login valid, but not permissioned to use the target database'
18     'Password expired'
27     'Initial database could not be found'
38     'Login valid but database unavailable (or login not permissioned)'

According to the information above I have the following issue:

  Login valid but server access failed

Still no joy.

I tried to drop the login and re-create it, but encountered a
Login can not be dropped because it is in use.

I used the script below to find out where it is used, by whom, from where:

USE master
go
SELECT
        sdes.session_id
       ,sdes.login_time
       ,sdes.last_request_start_time
       ,sdes.last_request_end_time
       ,sdes.is_user_process
       ,sdes.host_name
       ,sdes.program_name
       ,sdes.login_name
       ,sdes.status

       ,sdec.num_reads
       ,sdec.num_writes
       ,sdec.last_read
       ,sdec.last_write
       ,sdes.reads
       ,sdes.logical_reads
       ,sdes.writes

       ,sdest.DatabaseName
       ,sdest.ObjName
    ,sdes.client_interface_name
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.Query
    ,KillCommand  = 'Kill '+ CAST(sdes.session_id  AS VARCHAR)
FROM sys.dm_exec_sessions AS sdes

INNER JOIN sys.dm_exec_connections AS sdec
        ON sdec.session_id = sdes.session_id

CROSS APPLY (

                SELECT DB_NAME(dbid) AS DatabaseName
                    ,OBJECT_NAME(objectid) AS ObjName
                    ,COALESCE((
                            SELECT TEXT AS [processing-instruction(definition)]
                            FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                            FOR XML PATH('')
                                ,TYPE
                            ), '') AS Query

                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)

    ) sdest
WHERE sdes.session_id <> @@SPID
--ORDER BY sdes.last_request_start_time DESC

I then killed the 2 sessions, re-created the login, tried again, same error.

What am I missing?


texts: alter column varchar to nvarchar - will file group on full drive have issues?

texts:
Our database server has 2 drives.
The first drive is full, so we had to add a new file on the 2nd drive so new data went to that drive.
This all worked great, and the mdf on full drive stopped growing and all new data goes to drive 2.
I now need to alter a column to go from varchar to nvarchar.
I know a lot of this data exists on the first drive in the MDF file that has 9mb remaining.
What will happen if I try to alter the column as the pages are on the full drive?
Is this a valid concern or will the file group stuff work it out and put the bigger column on the new drive?


texts:
I am having issues with the user accounts in SQL Server v 11.0.6020. I need to run a script that will allow for the copy of the principal user account: 'TrainID1' and all of the user rights and permissions so that when I get the other users logged in, they can access the DB's and run the applications that connect to the SQL DB's.
So I have the user: 'TrainID1', and that user connects to the SQL DB: 'TrainID_01' DB, so I need to have TrainID2 to 42 user accounts allowed in SQL Security for all of the other Data Bases like: TrainID_02, TrainID_03 and so on...


texts: PostgreSQL replication with periodic log shipping

texts:
I'm using Windows Server 2008 R2 with 64-bit PostgreSQL 9.2 on both test machines. What I wish to achieve is that one server will be a master and other will be a slave. This is just for tests, finally the servers will work in 1 master - N slaves scenario. Once for each 24h, all changes from the master are meant to be replicated on the slave(s).

I decided to use native replication without streaming (no need for that). Ideally I wish just to deliver manually (via some script) WALs that are needed to the slave, restart the machine and let it update itself.

So I set up my master:

postgresql.conf:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "%p" "C:\\archive\\%f"'

And my slave machine:

postgresql.conf:
hot_standby = on

recovery.conf:
restore_command = 'copy "C:\\archive\\%f" "%p"'

Then I started the master machine, I wrote a few things to database. I executed the pg_start_backup('base'); command. Then, replaced whole 'data' folder on the slave with files from the master (just without the configs and postmaster.pid file). Now, I executed pg_stop_backup(); and copied files between my master's and slave's archive folders. After starting the slave's PostgreSQL service everything went good - database was there.

I deleted WALs from archive folders, did a small change in the master's db, again start_backup with stop_backup, copied the WALs, and... it does not work.
I even tried the simplest case - once it worked after first copying, I restarted the service. Again failure. Each time it logs:

WARNING:  WAL was generated with wal_level=minimal, data may be missing
HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
FATAL:  hot standby is not possible because wal_level was not set to "hot_standby" on the master server
HINT:  Either set wal_level to "hot_standby" on the master, or turn off hot_standby here.
LOG:  startup process (PID 4320) exited with exit code 1
LOG:  aborting startup due to startup process failure

I double checked my configs and they are fine; Wal level is clearly set to hot_standby. Any idea what am I doing wrong?


texts: SSIS Package Truncation may occur due to inserting data from data flow column

texts:
I've a dataflow in SSIS 2008. It reads the data from flat file and inserts in to database.

The flat file is CSV. For some reason, all the fields in flat file connection manager seems to have 50 OutPutColumnWidth.

Could someone please suggest how to fix it. I'm getting error below:
Truncation may occur due to inserting data from data flow column


texts: how to interpret log files with high transactions

texts: I'm having this issues with too many logs files being generated. Over 60gb in the last 24 hours. It's not a massively high transactional website. What is the best way to read the log files and work out a likely cause. I'm using SQL Server 2008


texts: Error when using general and specific GRANT

texts:
I'm having troubles with GRANTs when trying to use particular permissions on specific database. There's an user that needs SELECT on all schemas starting with some text; and INSERT, SELECT, UPDATE, DELETE on one schema. The thing is, when I give him the permissions, MySQL is giving "UPDATE command denied".

After long research, trying everything that came to my mind, I found that when the order is:

+----------------------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX'                              |
| GRANT SELECT ON `schema_%`.* TO 'user'@'%';                                                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%'                                          |
+----------------------------------------------------------------------------------------------------------------+

and I try (with user)

mysql> UPDATE schema_user.table SET field='some_text';
ERROR 1142 (42000): UPDATE command denied to user 'user'@'X.X.X.X' for table 'table'

I get the error... BUT if I do:

REVOKE SELECT ON `schema_%`.* FROM user;
GRANT SELECT ON `schema_%`.* TO user;

And check again the GRANTS, the order changed and now it's first the "full" GRANT:

+----------------------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX'                              |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%'                                          |
| GRANT SELECT ON `schema_%`.* TO 'user'@'%';                                                                    |
+----------------------------------------------------------------------------------------------------------------+

And I can UPDATE now without error:

mysql> UPDATE schema_user.table SET field='some_text';
Query OK, 0 rows affected (0.09 sec)

The thing now is, everytime I do FLUSH PRIVILEGES; the order is back to the first and "user" can't UPDATE.

This is happening with MySQL Community Server 5.6.36 in Centos 7.3.1611.
I tried with my local machine in 5.6.34 and got the same error, but tried again in VM Centos 7.3.1611 and same MySQL version and worked fine.

Here's some script that you can use in order to "replay" my error:

As root:

CREATE DATABASE demo_priv;
CREATE TABLE demo_priv.tbl (id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, some_text VARCHAR(50));
INSERT INTO demo_priv.tbl(some_text) VALUES ('insert');
CREATE USER test IDENTIFIED BY 'Password';
GRANT SELECT ON `demo_%`.* TO test;
GRANT INSERT, SELECT, UPDATE ON demo_priv.* TO test;
FLUSH PRIVILEGES;
SHOW GRANTS FOR test;

As "test":

UPDATE demo_priv.tbl SET some_text='update';
ERROR 1142 (42000): UPDATE command denied to user 'test'@'X.X.X.X' for table 'tbl'

As root:

REVOKE SELECT ON `demo_%`.* FROM test;
GRANT SELECT ON `demo_%`.* TO test;

And then you should be able to UPDATE.

Can anybody help me? I don't know if it's a bug or I'm understanding wrong the privileges in MySQL. Any help would be appreciated.


texts: SSRS 2016 - Change connection string in all reports

texts:
We have migrated from SSRS 2012 to SSRS 2016, and moved it to a new domain.

Using below code, we are able to list all datasources and their connection strings. How can we change the connection strings of nearly 700 reports using PowerShell or T-SQL? (so that the point to new data warehouse server)

WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;


texts: Replace the server name in email sender address when sending email from SQL Server 2008

texts:
I'm sending emails directly to Microsoft Outlook accounts from SQL Server 2008. Everything is fine, email was sent. Only problem is that in the header of the email the SQL Server name is embedded.

I've tried specifying the @from_address when executing the mail sending in DB but the sql server name is still there in the email's header. I don't want my recipients to see the sql server name in the header.

Is there a way to block this?

Thanks much!


texts: Microsoft Baseline Configuration Analyser on Production

texts:
I came across an useful tool that Microsoft has released for configuration analysis. It's called Microsoft Baseline Configuration Analyser 2.0.

I downloaded and ran it on my local instance and got a number of errors and warnings. In installation process, it warns you that you should accept some changes happen on you power shell to install the tool.

Considering that, do you think it is safe to be installed on Production server for regular checkings?

Have anyone used this tool before and recommend it?

Thanks.


texts: Postgres symbol lookup error : undefined symbol: ing

texts:
I have a Docker container running PostgreSQL 12, and on container start-up I had a symbol look-up error. Starting the container again failed, but rebooting the host did lead to recovery.
I didn't have much success googling the symbol error, but I've included the logs below - the first section just demonstrates the version and the OS.
2020-09-13 12:19:37.337 UTC [1] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on arm-unknown-linux-gnueabihf, compiled by gcc (Debian 8.3.0-6) 8.3.0, 32-bit
2020-09-13 12:19:37.352 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-09-13 12:19:37.352 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-09-13 12:19:37.375 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

PostgreSQL Database directory appears to contain a database; Skipping initialization

postgres: symbol lookup error: /usr/lib/arm-linux-gnueabihf/libxml2.so.2: undefined symbol: ing, version LIBXML2_2.4.30

Yes, the hardware is a Raspberry PI, so not suitable for production databases.


texts: Invalid default value for DateTime when changing to utf8_general_ci

texts:
I'm trying to run a script to modify a database, this query

ALTER TABLE cmContentVersion CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

gives this error

ERROR 1067 (42000) at line 68: Invalid default value for 'modifiedDateTime'
Warning (Code 1264): Out of range value for column 'modifiedDateTime' at row 1
Error (Code 1067): Invalid default value for 'modifiedDateTime'

the column modifiedDateTime is defined as

`modifiedDateTime` datetime NOT NULL default '0000-00-00 00:00:00',

Question: Is there a valid "invalid" default value that can replace 0000-00-00 00:00:00 when using utf8_general_ci. Or do I have to tell the developers to stop relying on an "invalid date" in their code?


texts:
We have a third party application that sends T-SQL statements in batches.

The database is hosted on a SQL Server 2016 Enterprise SP1 CU7, 16 cores and 256GB memory. Optimize for Ad-Hoc is enabled.

This is a dummy example of the queries that are being executed:

exec sp_executesql N'
IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT

select field1, field2 from table1 where field1=@1
option(keep plan, keepfixed, loop join)

select field3, field4 from table2 where field3=@1
option(keep plan, keepfixed, loop join)', N'@1 nvarchar(6)',@1=N'test'

When I monitor the database and I look at batches/sec and compiles/sec, I notice they are always the same.  Under heavy load, this can be 1000 batches/sec and 1000 compiles/sec.  Under average load, there are 150 batches/sec.

I analyze the query cache for recently compiled plans:

SELECT TOP (1000) qs.creation_time
    , DatabaseName = DB_NAME(st.dbid)
    , qs.execution_count
    , st.text
    , qs.plan_handle
    , qs.sql_handle
    , qs.query_hash
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st
ORDER BY creation_time DESC;

When I run above query I only see 10-20 new query plans/sec.

It's like every sp_executesql call triggers a compile but the queryplan is not cached.

What can be the cause of batches/sec being equal to compiles/sec ?


texts: Is there a way to do the following query with joins instead of a sub query?

texts:
I am using SQL Server 2014 and I want to eliminate having to use the following sub query so I can create an indexed view.

The only thing similar to my problem is the greatest-n-per-group queries but I want all the values, instead of only the last measured factor ones.

This is my query:

SELECT
      eu.[Name],
      pp.[MonthDay] --date type,
      pp.[Value] *
            (SELECT TOP 1 [Factor] FROM [em].[CO] co
            WHERE co.[EUID] = eu.[Name]
            AND co.[MonthDay] <= pp.MonthDay
            ORDER BY [MonthDay] DESC) AS [CO]
            --multiply by the last measured factor
            --I also want to remove the sub query
FROM em.[PPGenData] pp
JOIN em.[PPEUID] eu
ON eu.ID = pp.ID


texts:
I'm creating a database of simulation results, and am trying to do so the right way. I've attempted to show relationships between data to make sure nothing redundant is thrown in.

My current structure is like this:

Experiment

Primary key: SimulationID

Tables:

measurement
sampling_rate
first_draft_flow_rate
second_draft_flow_rate
final_flow_rate

Foreign key relating these tables: cycle_count

Each table contains the primary key, foreign key, and values for the variables.

So for a given experiment, it runs for so many cycles (the number of cycles it runs for varies from simulation to simulation). We log data at each cycle of the simulation.

I've made a table called measurement, sampling_rate, etc, but am not sure what to name the columns. Should the also be called measurement, sampling_rate, etc? Or should I just use Value as the column name?

Here's a spreadsheet with a sample log to demonstrate what I'm working with. All of this data would be filed under a single SimulationID.

Also, any tips on how best to design a DB to be normal/best practices would be greatly appreciated.


texts: Routinely deleting aged postgresql rows via cron

texts:
I have a Django website with a postgresql 9.3.10 backend. The website contains dynamic user-generated content (think of it as a form of 9gag). There are some tables from which I routinely delete aged rows. The database is called dbname and I log into it via the user postgres. The following are two examples of such routine deletion:

begin;
DELETE FROM links_groupseen WHERE which_reply_id IN (SELECT id FROM links_reply where "submitted_on" < now() - interval '7 days');
DELETE FROM links_reply WHERE "submitted_on" < now() - interval '7 days';
commit;

begin;
DELETE FROM links_vote WHERE link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour');
DELETE FROM links_photoobjectsubscription WHERE which_link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour');
DELETE FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour';
commit;

There are ~10 other such operations I run. At the moment, I do everything manually - I want to automate these to run at a certain low-utlization time of day. How do I do that? Can someone give me an illustrative example?

As per my own rudimentary knowledge, I'm thinking I should first save the following shell script to /etc/cron.daily:

#!/bin/sh

dbname="dbname"
username="postgres"
psql $dbname $username << EOF
begin;
DELETE FROM links_groupseen WHERE which_reply_id IN (SELECT id FROM links_reply where "submitted_on" < now() - interval '7 days');
DELETE FROM links_reply WHERE "submitted_on" < now() - interval '7 days';
commit;
begin;
DELETE FROM links_vote WHERE link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour');
DELETE FROM links_photoobjectsubscription WHERE which_link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour');
DELETE FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour';
commit;
EOF

And then add the said script to crontab like so:

30 4 * * * /etc/cron.daily/mypgscript

And then service crond restart


texts: Object-oriented programming CRUD design pattern

texts:
At first I have posted this question at https://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can not answered by "yes" or "no" or with a single answer.

I don't know if here it's the better place for this question, but here we go. I have shared a common question for DB designers - imagine the following SELECT:

 SELECT
     P.id,
     P.legalName,
     P.type,
     L.tradeName,
     L.foundation,
     L.EIN,
     N.SSN,
     N.gender,
     N.birthDay,
     A.zipCode,
     A.address,
     A.number,
     A.district,
     A.city,
     A.state
 FROM
     Person as P

 LEFT JOIN
     LegalEntity AS L ON L.person = P.id AND P.type = 'L'

 LEFT JOIN
     NaturalPerson as N ON N.person = P.id AND P.type = 'N'

 LEFT JOIN
     Address as A ON A.person = P.id AND E.legalAddress = 1

The above SELECT has a "maintaining" level relatively high by the following factors:

CREATE - When a Person are inserted in the database the next insert will be based on Person.type (L => Legal or N => Natural) that defines if will create a new record on LegalEntity or NaturalPerson with Person last inserted id as the foreign key.
READ - To provide a consistent data, we use JOINS with aditional term "AND T.type" for LegalEntity and NaturalPerson to return only relative data to Person.type FLAG. For eg. if Person it's a LegalEntity than we have EIN (Employer Identification Number or Federal Tax Id), if not, it's a NaturalPerson than have SSN (Security Social Number)
UPDATE - When you perform an update is needed to test whether there is specialization. This is because a record could originally have been created as type = N (Natural) and later want to upgrade it to Corporate (L = Legal). An INSERT to create original NaturalPerson was made, and now with the exchange of nature type is necessary to exclude logically or physically the NaturalPerson record and then create the new record of LegalEntity.
DELETE - WITHOUT aditional complexities.

Imagining all this complexity is also necessary. Now think that be need to run in 3 differente SGDBs (MySQL, Oracle and Postgres) - the quoted example are created in MySQL.

What are the best practices that we can adopt in this scenario?
Maintenance of data with the triggers assist for the 3 SGDS?
Create a CRUD with appropriate treatments implemented via application?

Thank you in advance for your time.


texts: Is there a query to check the current WAL size in PostgreSQL?

texts: I'm using a hosted PostgreSQL database where I don't have shell access.  Is there a query I can use to check the current WAL log size?


texts: Benefits of reindexing large bloated indexes in PostgreSQL

texts:
I have a 228GB index on a 1.86TB table. My shared_buffers are 225GB on a host with 768GB RAM. The index is bloated by 75%. After reindexing it, I get about 85GB. I'm on PostgreSQL 10.9.

I think my current query times on the old index are good as-is. Postgres docs do say that Btree indexes require no maintenance [reference missing].

Given that I have other large indexes on other large tables, will Postgres work better given that the new index fits into shared buffers better?


texts: How can I troubleshoot TNS connection issues? Do I need TRCROUTE executable?

texts:
I am trying to connect to a particular TNS name; in my tnsnames.ora file I can see that it is defined as being a TCP connection to foo-scan on port 39000. I can tnsping this name OK.

I can successfully telnet foo-scan 39000 but when I try to connect in e.g. sqldeveloper I get the error ora-12545: connect failed because target host or object does not exist

According to google it is likely that the listener on foo-scan is actually trying to forward my client to bar-vip, which no doubt I cannot connect to. Also according to google there is a utility called trcroute that will debug this issue and show me exactly where my client is trying to connect and what step is actually failing. But I cannot find this utility anywhere.


texts: DB disk space requirements comparision between MySQL versions

texts:
I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used.

In one lab, MySQL version 5.0.77 is installed.
The DB directory for each DB is around 230 KB, on average.

    select engine from   information_schema.tables where  table_schema = 'test_1389772840'   and table_name = 'test';
    +--------+
    | engine |
    +--------+
    | InnoDB |
    +--------+
    1 row in set **(0.82 sec)**

In the other lab, MySQL version 5.6.11 is installed.
The DB directory for each DB is around 500 MB, on average.

    select engine from   information_schema.tables where  table_schema = 'test_1389586231'   and table_name = 'test';
    +--------+
    | engine |
    +--------+
    | InnoDB |
    +--------+
    1 row in set **(0.00 sec)**

So in summary, both labs have similar DataBases and tables, and use same engine, but older version is slow, and disk space requirement is less, while newer version is fast, and disk space is requirement huge.

Is this expected ? Why the slowness ? Why the huge space requirement ? How can query speed and disk usage be brought to good levels ?

I checked MySQL documentation, but it did not help. It suggested optimize table ( here http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html ) but there was no change in disk usage.

I found some compression parameters ( here https://blogs.oracle.com/mysqlinnodb/entry/innodb_compression_improvements_in_mysql ) which I tried configuring:

On 5.0.77:

    show variables like '%compr%' ;
    +---------------------------+-------+
    | Variable_name             | Value |
    +---------------------------+-------+
    | have_compress             | YES   |
    | slave_compressed_protocol | OFF   |
    +---------------------------+-------+

BEFORE change of values on 5.6.11:

    show variables like '%compr%' ;
    +------------------------------------------+-------+
    | Variable_name                            | Value |
    +------------------------------------------+-------+
    | have_compress                            | YES   |
    | innodb_compression_failure_threshold_pct | 5     |
    | innodb_compression_level                 | 6     |
    | innodb_compression_pad_pct_max           | 50    |
    | innodb_log_compressed_pages              | ON    |
    | slave_compressed_protocol                | OFF   |
    +------------------------------------------+-------+

AFTER change of values on 5.6.11:

    show variables like '%compr%' ;
    +------------------------------------------+-------+
    | Variable_name                            | Value |
    +------------------------------------------+-------+
    | have_compress                            | YES   |
    | innodb_compression_failure_threshold_pct | 0     |
    | innodb_compression_level                 | 9     |
    | innodb_compression_pad_pct_max           | 0     |
    | innodb_log_compressed_pages              | OFF   |
    | slave_compressed_protocol                | OFF   |
    +------------------------------------------+-------+

I will be monitoring the average size over next few days.


texts: mysql RDS cpu utilization is 90 % above always for less than for 250 connections ( max conn 1236)

texts:
I'm using mysql RDS  db.m3.xlarge( 4 vcpus, 15G Ram). max_connection limit is 1236 but cpu is used more than 90% for less than 250 connection. I don't know what I'm struggling to find reason. Please help ?

what should be the ideal value for innodb_thread_concurrency for above configuration?


texts: In star schemas, what should I do when a dimension has a date attribute?

texts:
I'm trying to learn about dimensional models and star schemas.  Say I have a Sales fact table recording the total sales at retail stores, with, say, four dimensions, Date, Customers, Stores, and Promotions (as in sales promotions like coupons).  (Pseudo-schema below).

I really like the idea of prepopulating the Date dimension with attributes like day of the week, so that you can easily filter the facts by seemingly complex conditions like "saturdays in the second quarter for the past 10 years".

Now, say I want the Promotion dimension to have Start_Date and End_Date attributes which denote the start and end of the promotion.  How do I do this?  I thought about it and can come up with three less than ideal solutions:

1) Make Start_Date and End_Date regular, atomic attributes (either ISO8601 strings or the DB's datetime object) and only allow limitted filtering.

2) Make Start_Date and End_Date foreign keys to the Date table, breaking the star schema, but allowing the same filtering capabilities as the full Date dimension.

3) Include attributes like Start_Date_Day_of_Week, Start_Date_Quarter, maintaining the star schema but increasing the dimension size and essentially duplicating the structure of the Date dimension in the Promotions dimension.

Sales Table
-----------
Date key      (FK to Dates table)
Promotion key (FK to Promotions)
Customer key  (FK to Customers table)
Store key     (FK to Stores)
Sales Amount

Date Table
----------
Date Key (PK)
Month
Day of week
Day of month
Day of Quarter
Day of year
Holiday?
Quarter
Day since Epoch
Month since Epoch
etc...

Promotion Table
---------------
Promotion Key (PK)
Type (Coupon code, 2-for-1 sale, 50% off, etc)
Start_Date
End_Date

Customer Table
--------------
Customer Key (PK)
Name
Age
Sex
etc...

Stores Table
------------
Store Key (PK)
Address
City State
Zip
etc...


texts: Why is this table structure so slow to CRUD?

texts:
I have a table that I am using for queuing emails. Unfortunately, adding to or reading from this table becomes horrendously slow after adding about 50 records.

-- Table "mail_queue" DDL
CREATE TABLE `mail_queue` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `time_to_send` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sent_time` datetime DEFAULT NULL,
  `id_user` bigint(20) NOT NULL DEFAULT '0',
  `ip` varchar(20) NOT NULL DEFAULT 'unknown',
  `sender` varchar(50) NOT NULL DEFAULT '',
  `recipient` text NOT NULL,
  `headers` text NOT NULL,
  `body` mediumblob NOT NULL,
  `try_sent` tinyint(4) NOT NULL DEFAULT '0',
  `delete_after_send` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have tried using a text as well as a blob for the email body field, but no noticeable performance change, either way.

Thanks for any help.

Results of "SHOW VARIABLES LIKE 'innodb%';":

Variable_name   Value
innodb_adaptive_flushing    ON
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size 8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 134217728
innodb_change_buffering all
innodb_checksums    ON
innodb_commit_concurrency   0
innodb_concurrency_tickets  500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite  ON
innodb_fast_shutdown    1
innodb_file_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   OFF
innodb_flush_log_at_trx_commit  1
innodb_flush_method
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_io_capacity  200
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  8388608
innodb_log_file_size    5242880
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
innodb_max_dirty_pages_pct  75
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files   300
innodb_purge_batch_size 20
innodb_purge_threads    0
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  4
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_spin_wait_delay  6
innodb_stats_method nulls_equal
innodb_stats_on_metadata    ON
innodb_stats_sample_pages   8
innodb_strict_mode  OFF
innodb_support_xa   ON
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   0
innodb_thread_sleep_delay   10000
innodb_use_native_aio   ON
innodb_use_sys_malloc   ON
innodb_version  1.1.8
innodb_write_io_threads 4

Version: 5.5.22-cll


texts: Why did Microsoft kill "English Query" on SQL Server?

texts: Given the popularity of the natural language user interface Siri, which was acquired by Apple for $200 million, I've wondered if anyone knows why Microsoft killed "English Query" on SQL Server.


texts:
I am new to Microsoft SQL Server, please advise.

I am receiving the following error message when I deploy code into my development environment (which is hosted on Azure using Node.js (version 10) and Objection.js (1.4.0). It works fine locally and only seems to have issues with specific queries on the development environment.

Error Details

RequestError: Connection lost - read ECONNRESET
    at handleError (/src/node_modules/mssql/lib/tedious.js:519:15)
    at Connection.emit (events.js:203:15)
    at Connection.socketError (/src/node_modules/tedious/lib/connection.js:1028:14)
    at Socket.<anonymous> (/src/node_modules/tedious/lib/connection.js:878:18)
    at Socket.emit (events.js:203:15)
    at emitErrorNT (internal/streams/destroy.js:91:8)
    at emitErrorAndCloseNT (internal/streams/destroy.js:59:3)
    at process._tickCallback (internal/process/next_tick.js:63:19)

Code

 return Order.query()
.joinEager('[shipments.shipmentQuantities.lineItem, lineItems.shipmentQuantities]')
.findById(id);

Connection File

database: {
  client: 'mssql',
  connection: {
  host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    charset: 'utf8',
    stream: true,
       options: process.env.ENABLE_DB_SSL ? {
       port: 1433,
       database: process.env.DB_NAME,
       encrypt: true,
    } : undefined,
   },
    debug: false,
  },


texts:
I am managing a database in SQL Server 2008.  Across about 15 tables, I have 1600 VARCHAR columns that need to be changed to NVARCHAR.

I was able to generate all the appropriate ALTER TABLE statements with a query, but now I am faced with the problem of running them.

My approach has been to just paste the 1600 ALTER TABLE statements and try to run them, however this seems to be too big to run practically. If I step away from my desk for too long, I am disconnected from SQL Server and the process stops (no columns are changed).

Is there a smarter way to run this?


texts: Is it a good idea to split an auditable table into two tables based on the update frequency?

texts:
I'm currently designing a database schema for a small microservice which handles products.
The service contains a simple REST API which allows users to perform basic management of products and an another API which is used to perform operations which consume these products. The maximum amount of consumes per product is limited with an explicitly defined consumables count. When this limit is reached, the product cannot be consumed anymore. Each product typically contains about 1,000 - 1,000,000 consumables, while a single operation may take 1 - 10 consumables at a time.
The frequency of the management operations is quite low, but the rate of the consuming operations is very high.
At the moment, the schema puts all product information into a single table. This table also contains information which must be audited into a snapshot based history table on each INSERT, UPDATE and DELETE operation. This is currently done with DB triggers, which know how to add rows into the history table along with the additional audit information. Each consumable use also adds a new row into consumables table with some additional information related to how it was consumed.
Here's a simplified example about how these tables are currently organized.
------------------------
| product              |
------------------------
| + id                 |
| + name               |
| + is_enabled         |
| + consumable_limit   |
| + consumable_counter |
| ...10 omitted fields |
------------------------

------------------------
| product_history      |
------------------------
| + id                 |
| + product_id         |
| + time               |
| + user_id            |
| + history_type       |
| + name               |
| + is_enabled         |
| + consumable_limit   |
| + consumable_counter |
| ...10 omitted fields |
------------------------

------------------------
| operations           |
------------------------
| + id                 |
| + product_id         |
| + consumable_idx     |
| ...10 omitted fields |
------------------------

In the above diagram, I've omitted some fields to keep things more simple. The thing that keeps me wondering here is that the consumable_counter in the product table gets rapidly updated with consuming operations, which may further flood the product_history table. Would it be better move the consumable counter into an another table where it would not trigger the a full snapshot of the product table row each time the consumable_counter is being updated? Somehow, I feel like it could be ideal as the consumable_counter is the only column in the product table which is being updated with a high rate. A full snapshot on each counter increment somehow feels a bit overkill.
Edit
The consumable_counter acts as a counter of how many times the corresponding product has been used. When it reaches the consumable_limit, the product cannot be consumed any more.
Edit
Each time a product is being consumed, a new entry will be added into the operations table. Entries in this table also act as business logic transactions containing a state which will be managed during the operation execution flow.


texts: "Pausing" log shipping to allow normal DB use. Is this process valid?

texts:
I am in the process of migrating a server to AWS.  Right now it is just for Dev/QA work, but will eventually be turned into our production server.  It is a 2 node AG.  Server is all up and running, but our data is over 2 months old, and our team would like to periodically (every 5 days) get new data into the DB from the existing Prod DB, and I have been asked to use log shipping to do so.  The database needs to be usable, so keeping log shipping intact isn't an option since it will be read only or no recovery.  We need to test how it works for eventually populating the nodes for go live, so 2 birds with one stone so to speak.  The DB is 1.2TB, so transferring it over the NW to do a backup and restore normally is a bit time consuming.  Would the below work, or does anyone have a better way to do this?

remove the DB from the AG to allow it to be added as a secondary LS destination(i have not found info that an AG primary can be used as a LS secondary.)
Restore the most recent backup of prod DB to the destination server, leaving it in restoring mode.
Add the DB as a secondary and let the LS copy and restore jobs bring it up to sync.  choose the no recovery option on the DB.
once the DB is synced, disable the restore job and bring the database online using restore DB with recovery command.
add DB back to the AG so they can test everything for the next 5 days.

So five days later, I would need to copy over a new backup since we only keep 7 days of log backups in order to have all the log backups needed to get new data.  Is there a way to disable the log backup cleanup for a specific secondary?  I don't want to bloat the live prod server with weeks log log backups...

Would this process work, or does anyone have a different way to do this?


texts: MySQL schedular every day in a time periode every hour

texts:
This schedular i made doesn't work how i would like it to work.

The idea is that it will run every day in a time periode from 06:15:00 till 23:59:59 and in that only periode run every hour

That means it will run on 06:15 - 07:15 - 08:15 etc.. till 23:15 and start over again the next day

Server version: innodb_version:5.7.25

DELIMITER |

/*  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  */
ALTER
    EVENT `Database`.`Import_data01_view`
    ON SCHEDULE
        EVERY 1 HOUR
        STARTS '2019-04-15 06:15:00'  ON COMPLETION PRESERVE ENABLE
    DO BEGIN

/* do things */

END |

DELIMITER ;

With the info i found my own solution for it:

DELIMITER |

/*  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  */
ALTER
    EVENT `Database`.`Import_data01_view`
    ON SCHEDULE
        EVERY 1 HOUR
        STARTS str_to_date( date_format(now(), '%Y%m%d 0615'), '%Y%m%d %H%i' )
        ON COMPLETION PRESERVE ENABLE
    DO BEGIN

 IF time(CURRENT_TIMESTAMP) between time('06:14:59') and time('23:59:59')
      THEN

/* doe something */

end if;

END |

DELIMITER ;


texts:
I have SERVERA and SERVERB configured for Database Mirroring starting with A as Principal and B as Mirror. I created a CNAME for SERVERA and use that in my connection string. I am not using the FailoverPartner attribute of the connection string, partly because I suspect some of my applications will not support it and partly because I'm trying to learn about CNAMEs.

My expectation upon failover from A to B was that: 1. connections would fail 2. I would need to change the CNAME to point to SERVERB. 3. I would have to ipconfig /flushdns on my clients. 4. Then all would be good.

Instead, I didn't have to do any of that. My application automagically started going to SERVERB, even though my CNAME is pointing to SERVERA. I've read that the connection string will obtain the mirror instance from SQL and that will supersede the FailoverPartner attribute of the connection string (and the CNAME, I guess), but those were all posts about when the FailoverPartner attribute was being used. Again, my connection string is not using that attribute.

Can someone explain or point me to a resource that indicates how my app knew to got to SERVERB even though the CNAME being used in the connection string pointed to SERVERA?

Setup is Windows 2008R2 / SQL 2008R2


texts: Can't execute CreateAll.tt template in SQL Server Express

texts:
I have a CreateAll.tt template in a Visual Studio project. All the scripts I try to execute are correct - the template is tested on a different machine with SQL Server 2012 Developer Edition and it works perfectly fine.

On my computer with SQL Server 2012 Express instance however, i get the following error:

============== STDERR ==============
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

============== EXITCODE ==============
1

I already tried the suggestions given here: http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/


texts: PostgreSQL nondeterministic collations are not supported for LIKE

texts:
I am using Postgresql v12.
I created a collation like this:

CREATE COLLATION ci (provider = icu, locale = 'tr_TR', deterministic = false);

I used that collation in a table:

create table testtable1 (
    id serial  primary key,
    name text  COLLATE "ci"
);

And I inserted sample data:

insert into testtable1 values(3,'abc');

When I query that table using LIKE, it returns the following error:

select name from testtable1 WHERE name LIKE '%a%'

  ERROR:  nondeterministic collations are not supported for LIKE
  SQL state: 0A000

But I need to use LIKE. Is there any way to allow this?


texts: Rsync'ed postgres databases won't allow log-on, gets "cache lookup failed for relation" error

texts:
Running postgres 9.4.4 on a SLES11 SP4 Machine.
I'm rsyncing an entire machine over the Internet to create a copy of the operating system and when I try running postgres (the service starts).  I stoped the postgresql service before running rsync.
Logging in to my database (that works on the machine I rsynced from), I get

$ psql -U [my_username] [my_database]
psql: FATAL:  cache lookup failed for relation 1259

Searching here and there shows me lots of info for this error, but most of them appear to be for people who can actually log in to their database.
I can log in as the postgres user, but I don't think that user can fix it.
I've tried rsyncing again, in case something was lost in translation, and got exactly the same error.  So something must be gummed up.   I've rebooted, restarted postgresql.  Ran vacuum (as postgres, probably doesn't touch the busted relation).
I'll probably have to go hack on the config files to give the postgres user access to the tables, but short of that, are there any ways I can fix this.  I really only need the schema to survive the rsync, the data can (and will) get blown away and repopulated by another machine.


texts:
I am trying to create a computed column using a udf. I keep hitting the cannot be persisted because the column does user or system data access, when i try creating a index for that column or even for trying to make the column persisted.
This is the udf func i am using :
CREATE FUNCTION x.bal(@ID NVARCHAR(128))
RETURNS DECIMAL WITH SCHEMABINDING
AS
BEGIN
DECLARE @SUM DECIMAL ;
    WITH CTE AS (
    SELECT ROW_NUMBER() OVER (ORDER BY date ASC, type DESC) AS ROWNUMBER,[Id]
     T_id
    FROM x.entries
    )

    SELECT @SUM = (SELECT SUM(AMOUNT) FROM CTE L1
    WHERE L1.B_ID = L2.B_ID  AND L1.DATE <= L2.DATE AND L1.ROWNUMBER<L2.ROWNUMBER)
    FROM CTE L2
    WHERE L2.ID = @ID
    RETURN @SUM
END
GO

to add the column :
ALTER TABLE x.Entries
ADD bal AS dbo.bal(id)
CREATE INDEX IDX_bal ON x.Entries(bal)

I have also found that the error is only on 2017 local sql server where as for Azure sql ,I see the following :
If I  run the Column Property check before creating the index, it works but if missed, it fails.


texts: Performance after creating new MySQL server

texts:
When I clone an existing MySQL server, it takes for the same query executed against the same data and producing the same results significantly more time to execute on the new server. It seems the query is running forever (over 5 mins before I kill it as opposed to 15-20 second on the origin server) in the very beginning right after the new server was created then, as some time passes by, the query performance improves and finally it takes several hours before the query in question produces the same execution time as on the origin server.

I experienced this when I clone an AWS EC2 instance running a MySQL server and also when I add a new read replica to an AWS Aurora MySQL cluster.

What is happening in the background that makes the new server perform much worse? Is there a way to monitor / get feedback on these processes?


texts:
I've created a view from a table in SQL Server which cannot be altered in any way. The purpose is to make the fairly complex table conceivable for the users.

The view consists of several columns which share an ID and a relation, vendor & vendee, (VO & VE). Think of the ID as some kind of transaction-ID:

NAME | REL | ID
JAKE | VO  | 1234
JOSH | VE  | 1234
ANNA | VE  | 1212
FAYE | VE  | 1212
MATT | VO  | 1212

What I want to do is to create two new columns in the view based on their given relation and sorted by the ID and add a count based on the number of vendees involved in that given transaction.

  VO |  VE  |  ID  |CNT
JAKE | JOSH | 1234 | 1
MATT | ANNA | 1212 | 2
MATT | FAYE | 1212 | 2

Kind of new to SQL, is this even possible to perform with a view?


texts: Using GROUP BY together with wildcards for table columns

texts:
Issue

Is possible insert substitution for all cols of joined table? For example like this.

select distinct
p.product_id,
i.filename,
pl.* -- instead of pl.price_tax, pl.price_rec, etc.

from
s_product p
left join s_product_image i on i.product_id = p.product_id
left join s_pricelist_generated_lists pl on pl.product_id = p.product_id

Using Postgres 11.5

Edit

First error. Why?

SQL Error [42803]: ERROR: column "pl.product_id" must appear in the GROUP BY clause or be used in an aggregate function

Ok, then I added to group by this.

group by
p.product_id,
i.filename,
pl.*

But it does not take in this syntax.

  SQL Error [42803]: ERROR: column "pl.product_id" must appear in the GROUP BY clause or be used in an aggregate function

Here is the whole sql (pl changed to pp).

select distinct
        p.product_id,
        i.filename,
        pp.*
from s_product p
  left join s_product_image i on i.product_id = p.product_id
  left join s_pricelist_generated_lists pp on pp.product_id = p.product_id
group by p.product_id,
         i.filename,
         pp.*
order by p.product_id

Error:

  SQL Error [42803]: ERROR: column "pp.product_id" must appear in the GROUP BY clause or be used in an aggregate function


texts:
I have a Percona MySQL database 5.6 running in a linux machine. I have created new users called test1 and test2. What I did was I imported mysql database from my test setup to this new setup. After which when I run the query

SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;

I'm not able to see the new users which I created before the import was done but I'm able to connect to MySQL with the users I created before import.

Even after import my new users exist then it has to show in the mysql database right? What is wrong?

Thanks!!


texts: A simple Query causing high CPU in SQL Server 2008

texts:
We are noticing below SQL is causing very High CPU and its been executed 10,000 times. Is there a way we can tune this T-sql?

SELECT ISNULL(max(trans_seq),0) +1
FROM inv_inventory_journal
WHERE organization_id = @P0
AND wkstn_id = '600'

From Execution Plan, it is suggesting to create the following index.

/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 98.8039%.
*/

/*
USE [LKY_Xcenter]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[inv_inventory_journal] ([organization_id],[wkstn_id])
INCLUDE ([trans_seq])
GO
*/

Table Structure


texts: Row not being deleted from the publication when it's deleted in a subscription

texts:
We've merge replication configured in our environment(1 Publisher, 3 Subscribers) and We know by default merge replication is bi-directional. Its working fine in case of inserts i.e, if a row is inserted at publisher then it's getting updated at subscriber and vice versa. The real issue is, if row gets deleted at publisher then its getting deleted at subscriber but, if a row gets deleted in any of 3 subscribers then its not getting deleted at publisher.

When a row is deleted at publisher then it's getting updated in MSmerge_tombstone(Publisher). But, in case of subscriber, the corresponding row(rowguid) is not getting updated in MSmerge_tombstone(Subscriber). I think that might be the issue, even merge delete trigger exist on these tables(Subscriber). Please help me out, as it is a production issue.

Publisher(2008R2 sp1-Enterprise), Subscribers(2008R2 sp1-Standard)


texts: Using CLR User Defined Functions in SQL Server

texts: I have used CLR user defined functions for very specific instances, such as complex string handling, but generally didn't like them from a maintenance point of view (yet another place to hide code).  What do people consider the pros and cons of this type of function, and are there any best practice guidelines as to when to use them, both from a performance and maintenance perspectives?


texts: Cpu-Alert in SQL Server -2012 standard edition

texts:
My question is an extension to this.

We have SQL Server Agent alerts(Deadlocks, Network IO etc) to monitor SQL Server performance. So i would like to write an alert for CPU increase percentage. Since We are using SQL Server-2012 standard edition we are not able to write an alert for it. So i have googled and found options like; we can raise an
alert through Perfmon(Performance monitor). Please look below image.

In alert task tab, "Run this task when alert is triggered" in this box i have given the path of .BAT file (which will run a query and send the current cpu expensive statements in a mail).

But alert is not firing when cpu percentage crosses certain limit. I guess the way i am configuring in perfmon is wrong for cpu alert.
Can any one please suggest blogs or links for raising cpu alert from performance monitor? or Please suggest any other way to do this.

Basically, I am trying to call a batch file from performance monitor by using alerts. Please advice.


texts:
I have to develop a small website and I wanted to understand which language to use.
The main purpose of this site is to access various tables, for example customer table, orders, warehouse, etc. by people with login / password.
Each user must be part of a group with specific functionalities (for example, they can access only for reading or make insertions / changes and no deletions, others only administration, etc.).
I have good knowledge of PHP and long ago I did something similar accessing 4 tables using CodeIgniter and MySQL.

I could proceed with CodeIgniter as the problem is basically identical, but I wanted to take one more step and, after analyzing various types of PHP and CMS frameworks, I deduced that the best solution is to switch to OctoberCMS (which is based however on the Laravel framework).
Initially I had opted to switch to the use of Laravel (it is more advanced than CodeIgniter) but with OctoberCMS theoretically I save a good part of work (long and laborious for the various pages and controls) while still leaving me a complete customization of the contents and the possibility of also inserting code native php for particular functions.
I prefer to be the owner of the data and that they are not hosted by third parties also because I already have some tables on a MySQL database and I would like to access them too.

Do any of you know OctoberCMS to suggest me if it could be the right solution?
Or is it better to change to something else?
I have already checked in the forum but it seems to be no trace of this problem.
I also have knowledge of Microsoft Access and it would be nice to develop it with this tool since with a few clicks you can do many things ... but there is a problem, it cannot be put online (I am not only referring to tables but also to forms and reports).
What I am looking for, could be a tool similar to Microsoft Access (access to tables and masks to insert data and display table lists) but that can be used on the Internet to share and simultaneous use from people around the world.
In my opinion it is better not to use a Remote Desktop / Terminal Server solution, it doesn't seem serious or functional.

Thank you for the help.
Davide.


texts: "Cannot open database version 706. Upgrade the database to the latest version" - when trying to use availability database for read-only access

texts:
I have Availability Group with below config:

replica-01: SQL Server 2012 (primary)
replica-02: SQL Server 2012 (secondary synchronous)
replica-03: SQL Server 2016 (secondary asynchronous)

replica-01 and 02 are in on-premise data center
replica-03 is on Azure VM
(03 is synchronizing, no problems here)

When I try to use replica-03 as reporting read-only server, running "use MyDB" statement shows the following

Cannot open database 'TestDB' version 706. Upgrade the database to the latest version.

I tried to run ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 130
But it shows error message:

ALTER DATABASE failed because a lock could not be placed on database

Tried to exclude MyDB from availability group, set compatibility level to 130, and include back to AG
But since database in NORECOVERY mode, it does not allow me to set compatibility level

If I restore database with recovery, I can set compatibility level to 130, but can't return database in norecovery mode, and can't add back to AG

What do you suggest on above ?

I need to keep primary SQL server replica as 2012 for now, and I need to make async replica (SQL server 2016) to be available for read-only access


texts: Calculating disk usage with INFORMATION_SCHEMA.TABLE versus file system?

texts:
MySQL's database size can be calculated with

SELECT table_schema AS db_name, SUM(data_length + index_length) AS size
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema != 'INFORMATION_SCHEMA'
GROUP BY db_name

But you can also calculate the database size by looking at the disk-usage of the data-directory. Should these two numbers ever deviate? Is there ever a reason to use one of these methods of the other? By extension, how is INFORMATION_SCHEMA.TABLES's data_length and index_length calculated?


texts: How to remove the red squiggly line from IntelliSense for the newly created table or procedure without restarting the SSMS

texts: A newly created table or procedure will always have the red squiggly underline until the SQL Server Management Studio is restarted. Is there a way to get rid of it without re-start the SSMS? Or it's a feature that SSMS needs to update? Thanks!


texts: SQL Server 2012 mirror setup - how to reduce transaction log files

texts:
We just upgraded our Windows Server 2003\SQL Server 2005 setup to Windows Server 2012/SQL Server 2012. Both configurations have a mirror with NO witness server.

On the old setup the transaction logs remained small, about 22 GB total. Now they're over 160 GB and the partition of the new server is getting full. I need to reduce the transaction log file sizes.

However, all of the descriptions I've read say to switch the recovery mode from Full to Simple then back to Full. This won't work because we have a mirror so the recovery model MUST remain in Full recovery mode. I couldn't change recovery modes unless I break the mirror.

What would be the best course of action to reduce the log file sizes? Should I go ahead and break the mirror, switch from Full -> Simple then back? Does anyone have experience with this kind of setup?


texts: How to edit data to 2 tables using MySQL Workbench

texts:
How to edit data in 2 tables using MySQL Workbench (or can I)?
I use the below query to get the data but unable to edit it from the table (or Form Editor).

SELECT
    *
FROM
    TABLE1
        INNER JOIN
    TABLE2 ON TABLE1.ID = TABLE2.ID
WHERE
    TABLE1.ID = 1

=====


texts: PostgreSQL 9.3: Minimize locking when updating a large table with primary key

texts:
I am building a DB solution to handle a fast stream of online events.

Each event UPSERTs a row in a location_update table, using its car_id primary key. Since there are very few new car_ids, the vast majority of events trigger an UPDATE.

Since the table very frequently read by SELECT statements, I would like to minimize any locking and blocking delays on the table, and especially prevent a single UPDATE from blocking a large number of SELECTs.

How do I minimize Read/Write locking time on a table being UPDATEd using a primary key?


texts:
I want to design a model for a distributed database solution.

Are there appropriate tools and approaches?
How to include stereotypes for (horizontal) fragments?


texts: SQL Server Cluster + Availability Groups?

texts:
I'm actually finding a way to provide a good High Availability and Failover solution while I have a way to manage some sort of horizontal scalability and Load-Balancing...

I actually have a SQLServer Failover Cluster with 2 nodes (Active-Passive) but I wonder if could be possible to add more nodes to this cluster and configure some Database from the actual FailOver Cluster in AlwaysON Availability groups as read-only secondary copies.

And in the top of everything some sort of IP Load Balancer... I don't know if something like this could be possible.


texts:
I'm trying to insert data from a file into an Oracle table using SQL loader. The primary key is populated by a sequence where the nextVal is called from an insert trigger.

When I try using sqlldr, it seems to bypass the trigger as it says I cannot insert a null value into the primary key field.

Is there a way I can use sql loader to call the trigger for each insert?

OPTIONS (direct=false, SKIP=1)
load DATA
INFILE 'bcbsm.ctl'
append into table pprs.x
(
  LAST_NAME POSITION(1:25) CHAR
)

command: sqlldr username/password control=file.ctl

In my trigger there is a "Before Insert on x For Each Row" where I call the nextVal for the sequence then assign it to the primary key column. Since it's a unique identifier and populated by a sequence, it's not on the file that I'm trying to load.


texts: SQL SERVER auditing SELECT of a single column

texts:
Is this possible, or is table-level the most granular access?

So far, Google search has shown nothing, and this screenshot does not mention column, but I thought I'd ask here.

SQL Server version is 2012 SP4-OD.


texts: Database level health check did not trigger failover on losing a disk

texts:
I'm testing the new database level detection option in 2016 SQL Enterprise SP2 CU7 edition, and it does not seem to be working as expected.  We have a 2 node setup, synchronous commit, automatic failover on both nodes.  Database level health detection option is checked.  On the primary node, I took a drive offline that contained one of the data files of the DB that is in the AG.  I ran a select * from a table which read from the missing disk, and got the expected 823 error, which was logged in the error log.  I ran it a few times, and the error log recorded the 823 multiple times.

The availability group did NOT fail over as it was supposed to when this happened.  I waited for about 3 minutes to see if a failover would occur, and it never did.  How can i find out how often the DB level health check routine is set to run?  I understand this needs to see the issue in 4 consecutive runs according to this article:
enhanced database level failover

I checked the health check timeout value in the AG, and it was 30 seconds.

I also reviewed the failure condition level on the server and it is set to On CriticalServerErrors, but as I understand it, this setting is completely independent of the database level health check, and either of them should be able to trigger a failover on their own.  Is this correct?

The only thing I can think of that is preventing this is the pending timeout in the WSFC manager.  this has a value of 3 minutes before it will take the cluster resource offline.

Any idea where else I should be looking for why this did not fail over?


texts:
Today, while monitoring my data base I see an special session_id with negative number. (-4)

Anyone would help me to understand what do it please?


texts: MySQL when using WHERE BETWEEN, query hits all partitions

texts:
This is probably just me misunderstanding how MySQL partitions work, but I have a table defined with:

  `ID` int(11) NOT NULL,
  `target_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,

  PRIMARY KEY (`ID`, `created_at`),
  KEY `index_created_at_target_id` (`created_at` desc, `target_id`)
  KEY `index_on_created_at` (`created_at`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY HASH(MONTH(`created_at`))
PARTITIONS 12

If I query the data with a single created_at timestamp:

select * from my_table where `created_at`= '2018-12-00 05:00:00' AND target_id in (6,7,8);

Then the explain knows to only hit 1 partition. This is correct.

However, if I query on a range:

select * from my_table where `created_at` BETWEEN '2018-12-00 05:00:00' AND '2019-01-04 04:59:59' AND target_id in (6,7,8);

The explain now hits all partitions. Is this a known limitation, or am I just doing something wrong?

Side note; judging by other "partitioning" questions, I expect a couple "partitioning doesn't help just index correctly" responses... I'm just trying it for myself and gathering some metrics so we can find the solution that performs best for our needs. (also partitioning the table, even when it then checks all partitions, still cut the query time in half versus having 1 monolithic table with the same indexes; 2.3s vs 0.8s)

Edit 1:

I did make sure that both queries do hit the index_on_created_at index. The only difference is a 'non unique key lookup' versus an 'index range scan' on the index.


texts: Understand the underlying logic of Sakila DB

texts:
I'm working with Sakila database, trying to find which film is instock in store 1. However, before going to the syntax, I have basic question about the database first.

If a movie is rented out, I think it's captured in the rental Table. However, if it has been returned, would it be captured in rental table or will be deleted?

If it's captured in 'rental table', then how do we know which movie has been returned and which movie has not been returned? I just observe the table, and it looks like the 'return date' are all BEFORE 'last update'. does that mean all movies have been returned? (refer to the picture)
If it is NOT captured in 'rental table', then which Syntax does the job of 'deleting the record'?

What are these syntax for? (Rental table) what does KEY for?
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),

Thanks so much


texts: How can we remove the drop permission from a user or a role like dbowner? Want to restrict the dbowner from dropping the database.


texts: Adding new tables -- memory usage increases

texts:
I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema.

Does adding more tables increase memory usage? I am using about 1.7 gb of ram for mysql where before I was using 1.4-1.5gb


texts: Sql server, altering column to smaller size

texts:
I'm doing a project that's adding some columns to a fairly badly designed table, and I noticed that it's got a lot of wasted space.

I see a lot of posts about adding columns on the end being relatively quick and cheap (for reasons I don't quite get - the follow-on links were broken), and I see a lot of posts about how expensive it is to grow columns, and how it essentially boils down to "make a new table and copy all the old data into it" (in fact ssms does that with Generate Script no matter what change you make).

I'm curious about a more niche concern I guess - altering columns to smaller data sizes and how to do that efficiently.

Specifically, this table has multiple datetime columns that really only want the date.  In fact the sql is doing all the date arithmetic to strip time off of GETDATE().  I want to

ALTER TABLE Foo ALTER COLUMN BAR DATE NULL

But I don't want to incur all the expense of creating a temp table and re-writing the old data (if I can avoid it).

Seems like all the old data in place would be fine, just smaller.

And I'm hoping that freeing up that space in the block would make the new requirements I have to add less onerous (but I obviously don't get the deep internals of row allocation).

So what about going to smaller fixed-size types with an ALTER statement?  Will that just be okay and not blow up the log?

Thanks


texts: Optimisation of MySQL procedure to generate tree

texts:
I have MySQL table like this
|-----------------------------|
|IDENTIFIER | POINT1 | POINT2 |
|-----------------------------|
| NAME1     | AA1    | BB1    |
| NAME2     | AA1    | BB2    |
| NAME3     | AA1    | BB3    |
| NAME4     | BB2    | CC1    |
| NAME5     | BB1    | BB2    |
| NAME6     | AA1    | CC1    |
| NAME7     | CC1    | BB1    |
| NAME8     | BB2    | BB3    |
| NAME9     | CC1    | BB3    |
|-----------------------------|

and this procedure to find all possible ways in table
DELIMITER $$

USE `test`$$
DROP PROCEDURE IF EXISTS `FindWays`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `FindWays`(IN MY_START VARCHAR (20),  IN MY_FINISH VARCHAR (20),  IN WAY TEXT,  IN PROHIBITED_POINTS TEXT,IN CYCLE INTEGER,IN MAXCYCLES INTEGER)
BEGIN
  /* define variables */
  DECLARE MYNAME VARCHAR(20); /* IDENTIFIER */
  DECLARE ROW1 VARCHAR(20); /* POINT1 */
  DECLARE ROW2 VARCHAR(20); /* POINT2 */
  DECLARE TMP_PROHIBITED TEXT;
  DECLARE TMP_WAY TEXT;
  DECLARE FINISHED INTEGER DEFAULT 0;
  DECLARE TMP_CYCLE INTEGER DEFAULT 1;
  /* cursor (select) */
  DECLARE curs CURSOR FOR SELECT IDENTIFIER , POINT1 , POINT2  FROM my_table
    WHERE (POINT1  = MY_START OR POINT2  = MY_START) AND PROHIBITED_POINTS NOT LIKE CONCAT('%',IDENTIFIER,'%') ORDER BY IDENTIFIER ASC;
  /* handler to controll loop */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;

  OPEN curs;
  SET TMP_CYCLE = TMP_CYCLE + CYCLE;
  TreeLoop: LOOP
    FETCH curs INTO MYNAME,ROW1,ROW2;
    /* finish loop if no rows or MAXCYCLES */
    IF FINISHED = 1 OR CYCLE = MAXCYCLES THEN
    LEAVE TreeLoop;
    END IF;
    /*IF first loop set without comma*/
    IF LENGTH(WAY)>0 THEN
      SET TMP_PROHIBITED = CONCAT(PROHIBITED_POINTS,",",MYNAME);
      SET TMP_WAY = CONCAT(WAY,',',MYNAME);
    ELSE
      SET TMP_PROHIBITED = MYNAME;
      SET TMP_WAY = MYNAME;
    END IF;
    /* if first way then do at least one iteration*/
    IF (ROW1 = MY_FINISH OR ROW2 = MY_FINISH) AND LENGTH(WAY)>0 THEN
      INSERT INTO resulTable VALUES (TMP_WAY);
      SET FINISHED = 1;
    ELSE
      IF ROW1 = MY_START THEN
        CALL FindWays (ROW2,  MY_FINISH, TMP_WAY ,  TMP_PROHIBITED,TMP_CYCLE,MAXCYCLES);
      ELSE
        CALL FindWays (ROW1,  MY_FINISH, TMP_WAY ,  TMP_PROHIBITED,TMP_CYCLE,MAXCYCLES);
      END IF;
    END IF;
  END LOOP TreeLoop;
  CLOSE curs;
END$$

DELIMITER ;

procedure working as expected just is too slow.
Procedure is called like CALL FindWays('AA1','AA1','','',0,5)
The result is something like this (Circle ways)
Row1: NAME1,NAME5,NAME2
Row2: NAME1,NAME5,NAME8,NAME3
Row3: NAME1,NAME5,NAME8,NAME9,NAME6

When I have in table around 1000 rows and set MAXCYCLES to 5 it takes around 4 mins, with MAXCYCLES = 10 after half hour i had around 30000 possible ways but it was not finished.
So I think its really slow (+/- 20 ways per second).
Have someone any idea how to optimise it? (MySQL: 10.5.9-MariaDB, source and result tables are MEMORY)


texts: MongoDB working set size on capped collection larger than capped collection size?

texts: On my database server right now I have 16GB of ram and an 8GB capped collection with about 4GB of indexes. So I may understand this wrong but shouldn't this only ever use around 12GB of my RAM or maybe a bit more? After about 2 weeks of running the database it starts to act like it no longer fits into memory with insert speeds slowing down significantly and I have to repair the database. Am I just misunderstanding the way this should work?


texts: Linked Server Error - Login Failed for user 'myaccount'

texts:
I have a database on serverA. I have a windows account (myAccount) that is a member of db_datareader role on this db.

On serverB, I want to create a linked server to the database on server using myAccount.

However, I get Login Failed for user 'myAccount'.

Are there any other permissions I need to set?


texts:
I have a situation that after an initial thought ended in the following tables:

section:         id, name
section_tag:     id, name
section_tag_map: section_id, tag_id

item:         id, name
item_tag:     id, name
item_tag_map: item_id, tag_id

section_item_map: section_id, item_id

This describes well the data: items belong to sections. Sections and items have multiple tags for each of their "predicates", but they are different kinds of tags (section tags are specific to sections, item tags to items).

I will need to do this kind of query:

Select sections with section_tag's (3, 4). Easy.
Select items with item_tag's (1, 2) that belong to a section with section_tag's (3, 4). It didn't take long to realize what a mess of queries this requires.

I'm planning to simplify the initial idea, setting a single tag table and through app logic make items "inherit" tags that are meant to be set for sections. So it will be more like this:

section: id, name
item:    id, name
tag:     id, type, name

section_item: section_id, item_id
section_tag_map: item_id, tag_id
item_tag_map: item_id, tag_id

The upside is that queries will be much simpler. The downside is that I'll duplicate data and have to take care of syncing item tags when the tags from a section change.

What I'm weighting is if I should worry about having a monster query like this (first design):

SELECT
    i.*
FROM
    item AS i
INNER JOIN
    section_item_map AS sim
ON
    sim.item_id = i.id
INNER JOIN
    (SELECT
        s.*
    FROM
        section AS s
    INNER JOIN
        section_tag_map AS stm
    ON
        stm.section_id = s.id
    WHERE
        stm.tag_id IN (3, 4)
    GROUP BY
        s.id
    HAVING
        COUNT(s.id) = 2
    ) AS s
ON
    sim.section_id = s.id
INNER JOIN
    item_tag_map AS itm
ON
    itm.item_id = i.id
WHERE
    itm.tag_id IN (1, 2)
GROUP BY
    i.id
HAVING
    COUNT(i.id) = 2

...or a much simpler one like this (second design):

SELECT
    i.*
FROM
    item AS i
INNER JOIN
    item_tag_map AS itm
ON
    itm.item_id = i.id
WHERE
    itm.tag_id IN (1, 2, 3, 4)
GROUP BY
    i.id
HAVING
    COUNT(i.id) = 4

Am I on the right path or maybe I am missing something crucial here? The system is a single-user Sqlite app with about 20k sections and 100k items.


texts: Why is SQL Server 2014 consuming so much RAM with no active data stored?

texts:
I'm running SQL Server 2014 on my laptop along with a ton of other installed applications, from Visual Studio to video games.

This is obviously not a production server, but for development and debugging only.

I've scripted out a fairly small and simple database, with a few dozen tables and views - and I've inserted less than 100 rows of dummy data for testing.

At one point I accidentally created a 100GB+ index, using some badly formed cross joins - the query creating this index was consuming 100% of the RAM in my laptop (16GB), which slowed everything to a crawl, so I aborted the query rather than waiting for it to finish, then promptly dropped and re-created the entire database with this index removed from the creation scripts.

However, SQL Server 2014 kept consuming all 16GB of my RAM from that point forward, even with the query aborted and the database dropped.

So I rebooted my laptop next, and the RAM usage for SQL Server dropped down to 7GB - where it has remained ever since, regardless whether or not I drop all of the 5MB of existing databases etc.

I'd like to get SQL Server back to a reasonable memory consumption level, but I don't know where to begin looking for possible causes.

I know this is a pretty open-ended question, but I'm willing to do the legwork with a little guidance.

I just don't know where to begin troubleshooting, since I'm not a DBA myself - and apparently Google doesn't know either. :)


texts: Create a function column that is the result of OR between 3 columns

texts:
I have a table with 3 columns (A,B,C).

In each row, only one of the columns is not null.

How do I create a function column or a computed column that will consist from all the non null values?

Something like: column(A or B or C)


texts: SSIS - Import and Export wizard - Dataflow OLE Destination create not executed before injecting data?

texts:
I've created an SSIS package using the Import and Export Wizard using SSDT to generate the package.

After analyzing this package I see that it consists of a SQL Execution Task to create the table and then a Data flow with a OLE DB Source and a OLE DB Destination

What I found odd was that the OLE DB Destination also contains a create statement to create the table. In my logicI assumed that if a table did not yet exist in the destination DB that this statement would create it on the fly.
Sadly tho, without executing the SQL Execution Task (Prep Task) the Data flow fails as it does not see the destination table to inject to.

Would it not make sense that the OLE DB Destination component create the table using the specified CREATE SQL statement (or do I need to check some obscure option for this?) thus making the SQL Prep task obsolete?

From what I can see this is not the case so I'm a bit confused about the logic behind having to both do a SQL CREATE statement in a Prep Task and additionally still define it also as (the same) CREATE statement in the OLE DB Destination component.
Disable'ing the Prep task results in an error stating the table does not exist yet so from this I deduce that the CREATE prep task is mandatory (or at least the table creation) before attempting an injection

Anybody got some more details about this or can help me make sense of it (it being why we need to both define the CREATE in the Prep task and in the OLE DB Destination)?


texts: pt-table-sync: syncing slave from master that has different credentials?

texts:
I have a MySQL slave under my full control and a master server where I have only read-only login. Also, logins are different. At some point replication have stopped due to some kind of problem.

I would like to use pt-table-sync (https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html)  to restore sync between master and slave. However, by default it tries to use same credentials for master and slave. I use --sync-to-master (so it requires only one DSN from command line and attempts to apply its user/password to master that is found out from the slave DB)

How I can tell pt-table-sync to use different credentials to slave and master?


texts:
I’m currently having to deal with a SQL Server transaction log that has got out of control. Disclaimer: I’m not a dba and this isn’t my area of expertise so please bear with me.

Currently I have a 115GB Transaction log file for a 500MB database which has (obviously) been poorly managed for some time for it to get in this state.

The top priority is to reclaim the space on the disk taken up by this file before we run out! I’ve been told increasing the size of the drive is not an option, even temporarily, and based on past growth, we need to act pretty soon.

As I understand it, the best approach is to keep the db in full recovery mode but take regular backups of the log file, monitor this over a period of time and adjust initial size and increment to suit. All okay.

Seeing as we take regular full db backups at midnight, would it be safe for me to temporarily put the database into Simple Recovery Mode (after one of these backups has run), shrink the log file to reclaim (virtually all of) the space and then put it back in to Full Recovery with the backup strategy mentioned above?

My thinking is that if something happened around this time, we could simply restore the full backup without using the logs.

UPDATE

A few extra details in reply to some of the answers and comments:

We do want to retain the ability to do a point-in-time restore so the
database should remain in full recovery mode.
The reason that the t-log file has grown so large is that it has never been
backed up. Verified as log_reuse_wait_desc returns ‘LOG_BACKUP’.


texts: SQL Server to MySQL migration, replace '\xA0' with a regular space

texts:
I am having some trouble while migrating data from SQL Server to MySQL--it seems some data does not want to place nice. I am recieving the error: Incorrect string value: '\xA0\xA0' for column 'Address1'

As I am trying to automate data migrations that will happen regularly, I need to find a solution I can put into a script. I am using wbcopytables.exe at the moment, but I do not necessarily need to.

In just trying to find an appropriate solution to later implement in the script, I have tried running the below in SQL Server to fix the source data:

UPDATE [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
SET Address1 = REPLACE(Address1 COLLATE Latin1_General_CI_AS, N'\xA0', ' ')

and

UPDATE [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
SET Address1 = REPLACE(Address1 COLLATE Latin1_General_CI_AS, N'\u00A0', ' ')

but that is not fixing the source data, and my Google-Fu is failing me. Thoughts?