texts: MySQL CREATE table with > 4000 fields (with large sized colums)

texts:
I wish to CREATE a table with 4000+ columns but an error happens.

Traceback (most recent call last):
  File "e:/OECD-Bot/src/csvTomysql.py", line 55, in <module>
    cursor.execute(creatQueryString)
  File "C:\Program Files\Python37\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Program Files\Python37\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Program Files\Python37\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1005 (HY000): Can't create table `csv_db`.`ear_mei` (errno: 185 "Too many columns")


texts:
I want to create a database for my program. Most likely I will use SQLite, although others are an option.

I will have columns a, b, c, d. a is unique and will be my primary key. b, c, d are allowed to contain duplicate value.

However, I don't want combinations of b, c, d to repeat. For instance, sample valid db:

a   b   c   d
0   18  n   kiwi
1   12  v   kiwi
2   15  t   orange
3   12  j   kiwi
4   12  t   banana
5   31  f   apple
6   10  h   kiwi
7   15  y   orange
8   20  b   apple
9   1   m   grapefruit
10  7   s   apple
11  4   h   banana
12  31  t   egg

Sample invalid db (rows 2 and 7):

a   b   c   d
0   18  n   kiwi
1   12  v   kiwi
2   15  y   orange
3   12  j   kiwi
4   12  t   banana
5   31  f   apple
6   10  h   kiwi
7   15  y   orange
8   20  b   apple
9   1   m   grapefruit
10  7   s   apple
11  4   h   banana
12  31  t   egg

Is there standard database rule or concept that applies to this?

I can of course simply do validation in my code, by manually querying the db. I believe I could also construct a fifth bcd column, which concatenates the other three, and is required to be unique. But both of these seem hacky. Is it possible to somehow "declare the columns unique in combination only" in a more direct and obvious way?

For instance, I could declare (b, c, d) as my composite primary key (not sure if SQLite supports this but I think SQL does). That should accomplish my goal. However, I don't want these columns to actually be my PK.


texts:
I have a SQL query as following:

SET @thedate := CURDATE();

create temporay table tmp1(
...
where thedate=@thedate
);

create temporay table tmp2(
...
where thedate=@thedate
);

delete from tbl1;

replace into tbl1(
select *
from tmp1
inner join tmp2
);

I need to have a loop, that @thedate is from 2014-1-1 to today. For each day, a new tbl1 generate, I also need t record it as a tsv, I wrote a script to do it but, I do not know how to do it from 2014-1-1 to today. the script is as following:

now=$(date +"%Y%m%d")
mysql mydb -e "select * from tbl1">$now.tsv


texts: Upgraded from SQL Server Express to standard but CPU usage is maximum 50% on dual core

texts:
I have upgraded SQL Server Express 2008 r2 to SQL Standard Edition on amazon EC2 with a Intel Xeon E5-2670 v2 (Ivy Bridge) Processors with 2 vCPU.

I have verified the edition by running this:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

and getting

10.50.2500.0    SP1 Standard Edition (64-bit)

Also in the Windows Logs -> Application I'm seeing this MSSQLSERVER log:

Detected 2 CPUs. This is an informational message; no user action is required.

I am running the below SQL Query:

SELECT SUM(CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id) + CONVERT(BIGINT, o4.object_id))
FROM sys.objects o1
CROSS JOIN sys.objects o2
CROSS JOIN sys.objects o3
CROSS JOIN sys.objects o4

and seeing this on Windows Task Manager -> Performance:

and seeing this on windows Task Manager -> Applications:

I'm thinking that the SQL Server Express CPU limitation of 1 CPU is not removed.

Does anybody knows why? Does it have to be something with the query?
And how to fix it?


texts: Assign different RAM usage (buffer pool size) per db in mysql

texts:
I have quite some very large mysql databases (dev, production, staging, etc.) on a single server. Total size of indexes of all my databases is 64Gb.
However, the InnoDB Buffer Pool is only 16Go, meaning that often my indexes need to be loaded from disk, which destroys performance of my requests.
Index size of my production database is 16Go. Is there somehow I can tell mysql to keep my production db indexes in RAM ? E.g., by assigning buffer pool sizes per db.
Any other recommended strategy ?


texts: I have an application which uses PostgreSQL. I want to monitor all the queries that the application sends to the database. Is there a way to monitor the queries sent?


texts:
I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database.

These are the tables (given by my professor):

Here are the problem statements and the solutions I have come up with:

  1) From that database, get supplier numbers for supplier who supply project J2, in supplier number order.

select SNO
from SPJ
Where JNO = 'J2'
Order by SNO ASC;

  2) Get part numbers for parts supplied by a supplier in LA

select DISTINCT PNO
from spj
where SNO in
(select SNO
from Suppliers
where CITY = 'LA' ) ;

  3) Get part numbers for parts supplied by a supplier in LA to a project in LA:

select PNO
from spj
where JNO in
(select JNO
from projects
where CITY in
(Select CITY
from Suppliers
Where CITY = 'LA' ));

  4) Get the total quantity of part P2 supplied by supplier S3

select SUM(QTY)
From spj
where PNO = 'P2'
and SNO = 'S3' ;

  5) for each part being supplied to a project, get the part number, the project number, and the corresponding total quantity

select PNO, JNO, QTY
from spj

  6) Get project names for project supplied by supplier S1 located in HON

Select Jname
from projects
where CITY = 'HON'
and JNO in
(Select JNO
from spj
where SNO = 'S1' );

  7) Get part numbers for parts supplied to any project in LA

select PNO
from spj
where JNO in
(Select JNO
from projects
where CITY = 'LA' );

SQLFiddle with test data here


texts:
In one of our production servers, we need to enable xp_cmdshell for some app functionality.

With our current security measures, it is disabled in all instances and needs to be enabled for just one specific SQL Server.

How bad is it to enable xp_cmdshell?
Is it really a big thing to worry about?
Any specific measure that can be taken to enable it and still limit its use?

Our application team has created a trigger which kicks off another invoice process and they need xp_cmdshell for that.


texts: Oracle Database Change Notifications only sent every 15 minutes, not immediately

texts:
I'm registering a listener for Oracle Database Change Notifications through JDBC (using the EclipseLink implementation of JPA, Oracle 11.2.0.3, Oracle JDBC thin driver 11.2.0.4).

I'm getting notifications, but all of them in one chunk every 15 minutes. The 15 minutes are based on absolute time, regardless of when I start listening. It seems like the job sending the notifications only runs at a fixed interval of 15 minutes on the server.

If I register a PL/SQL procedure directly on the server to receive notifications, the notifications are instant.

What options do I have, to reduce the interval? I'm aiming for instant or every few seconds. Are there any configuration options for the corresponding job that I'm missing?


texts: How to auto start instance of sql server 2012 localdb on startup?

texts: How do you auto start instance of sql server 2012 localdb on startup?


texts: Can I calculate ROW_NUMBER() for only consecutive records?

texts:
I need to calculate sequence numbers for consecutive values.  That sounds like a job for ROW_NUMBER()!

DECLARE @Data TABLE
    (
    Sequence    TINYINT NOT NULL PRIMARY KEY,
    Subset  CHAR(1) NOT NULL
    )
INSERT INTO @Data (Sequence, Subset) VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'A'),
    (4, 'B'),  -- New subset
    (5, 'B'),
    (6, 'A')   -- New subset

SELECT
    Sequence, Subset,
    ROW_NUMBER() OVER (PARTITION BY Subset ORDER BY Sequence) AS SeqWithinGroup
FROM
    @Data

I expected the PARTITION clause to make the count reset at each change in Subset, but instead SQL Server collects all values for a given Subset value and numbers them.  Here's what I expected, and what I got:

Sequence Subset Expected Actual
-------- ------ -------- -----
1        A      1        1
2        A      2        2
3        A      3        3
4        B      1        1
5        B      2        2
6        A      *1*      *4*

When SQL reaches line #6, it resumes numbering subset "A", whereas I see it as the first line of a new subset that just happens to also be named "A".

Is there a way to make ROW_NUMBER() partition strictly, rather than the default behavior?

There are a number of questions here and elsewhere about counting consecutive values with SQL.  However, I have not yet seen one that addresses repeated values in the PARTITION BY field(s).  Most deal only with increasing values, often dates.


texts:
First of all, I am not DBA but I have to perform some DBA jobs. I am trying to read an extended file using,

  SELECT [object_name],[event_data],[file_name],[file_offset],[Event_Time]
  FROM OPENQUERY(MyServer,'master.dbo.READ_AUDIT @P_RootFile_Name=''Path\DDL*.xel'',@P_File_Name=''Path\file.xel'',@P_Offsit=0')

I am getting this error,

The offset 0 is invalid for log file .xel  Specify an offset that exists in the log file

How do I calculate the offset of a xel file?

ALTER proc [dbo].[READ_AUDIT] @P_RootFile_Name NvarChar(260),@P_File_Name NvarChar(260)=NULL,@P_Offsit bigint =NULL
as

Select * from
(
select *,

DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CONVERT (XML, event_data).value('(/event/@timestamp)[1]', 'datetime2')) Event_Time
from sys.fn_xe_file_target_read_file(@P_RootFile_Name, null, @P_File_Name, @P_Offsit)

) as T

order by T.Event_Time ASC


texts: ARCHIVELOG in Oracle 12c multitenant PDB

texts: In a Oracle 12c multitenant DB instance with multiple PDBs, is it possible to enable ARCHIVELOG mode on a single PDB?


texts: Dropped tables are not going to Recycle Bin

texts:
To start, I am a student and just getting my feet wet with Oracle.

Quick history

Virtual Box XP running Oracle express with SQL Developer.
Created a user and tablespace for myself. (Key here is I'm not on System)
I have checked to make sure my Recycle Bin is active with

  SHOW Parameter recyclebin;

command.
I have also tried alter session to on.

Long story short Create table, add some data, drop table with standard drop command with no purge at the end and table does not go to recycle bin and hence can't use the flashback command to retreive it. Any idea as to why my recycle bin would not be functioning as it should, or am I missing something obvious? I am very green so assuming the latter :)
Any feedback would be appreciated!


texts: View(df) in R throw (function(x, df1, df2, ncp, log = FALSE))

texts:
I connected R with SQL Server (in Visual Studio 2017) the connection succeeded this is Setting.R

 settings <- as.environment(list())
   dbConnection1 <- 'Driver={SQL Server};Server=
   (local);Database=SaVeITDB;Trusted_Connection=yes'

this is SqlQuery.sql also executing the below query succeeded

-- Place SQL query retrieving data for the R stored procedure here
select AOIName from AreaOFInterest a, ProjectsAOI p where p.AOIId = a.AOIId;

and this is Script.R when I executing it throw this function in df file

 source("Settings.R")
 library(RODBC)
 conn <- odbcDriverConnect(connection = dbConnection)
 df <- sqlQuery(conn,  iconv(paste(readLines('c:/users/comnet/source/repos/rproject3/rproject3/sqlquery.sql', encoding = 'UTF-8', warn = FALSE), collapse = '\n'), from = 'UTF-8', to = 'ASCII', sub = ''))
 View(df)

this df file (appears with lock icon)

function(x, df1, df2, ncp, log = FALSE) {
if (missing(ncp))
    .Call(C_df, x, df1, df2, log)
else .Call(C_dnf, x, df1, df2, ncp, log)}

Edit

It's executing correctly without any error after run Visual studio as admin


texts: What is the cause of sleeping connection?

texts: What is the possible cause of sleeping connections?  Can I say one of the causes is because of the uncommitted transactions? How does this related?


texts: Will DIFF backup size be reduced if we shrink the data files?

texts:
In our Prod env, DIFF backup job was failing due to insufficient space in the drive ( Total free space 36 GB ). That means DIFF backup file was generating more than 36 GB.
I investigated and found that the DB size 80GB has 70GB of free space. I immediately cleared 60GB space from data files and then I was able to take DIFF backup successfully( DIFF Backup size generated 10 GB).

Given that backups only take backup of data inside the DB not free space, why did the above scenario happened?


texts: Routing data from one SQL Server to another

texts:
Is there a way in SQL Server 2012 to have data being written to one table routed to another SQL Server on another host?

To clarify, we are using Hibernate Envars to write audit logs. Hibernate Envars does not allow this data to be sent to another server instead of the server where the audit log was created by Hibernate.

I'd like for these audit logs to be sent to another server. Is there a way to configure SQL Server to write specific tables to another server?


texts: DB design for employee working hours constraints

texts:
I have a simple employee schedule database (I use PostgreSQL):

employees:
 - id
 - name
 - email

shifts:
- start
- stop
- employee_id

Now there is need to apply constraint for working hours. Employee can input when he/she is unavailable to work. It can be specific time period one time, or recurring once (er every 2,3...) days, weeks etc. The table looks like this:

constraints:
- start
- stop
- recurring (boolean)
- recurring_type (string)
- recurring_every_x (integer)
- employee_id

On my website i need to check quickly if employee is available for specific period. These are options I thought about:

Dynamic

When there is need to determine if employee can work within given time window we go through every instance of constraint connected with this employee, and calculate it. We have repeat this calculation every time.

Availabilities table

We create another table, which holds availabilities for each emplyee e.g. 1 year ahead, like this:

availabilities:
- start
- stop
- day
- employee_id

We fill data 1 year ahead with start=beginning of day, end=end of day, and when employee inputs new constraint we cut off time spans (removing some rows (days) from availabilities table or splitting them to two periods).

For example:

availability for 2015-05-05 is at beginning (day: 2015-05-05, start- 2015-05-05 00:00, end: 2015-05-05 23:59)

employee enters constraints "can't work 2015-05-05 10-15". We apply it:
(day: 2015-05-05, start- 2015-05-05 00:00, end: 2015-05-05 9:59)
(day: 2015-05-05, start- 2015-05-05 15:00, end: 2015-05-05 23:59)

This requires complex logic while new constraint is added. Also when removing constraint we have to remove all availabilities and recreate them, because constraints can overlap.

Which solution is best here, or maybe there is another way?


texts: Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

texts:
I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine.

SELECT empname,array_agg(phonenumber) AS phonenumbers
FROM employee LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid

The employee table might contain large number of rows. I want to fetch only some employees at a time. For example I want to fetch 3 employees with their phone numbers. I am trying to run this query.

SELECT empname,array_agg(phonenumber) AS phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS employee
LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid

But I get this error. ERROR:  column "employee.empname" must appear in the GROUP BY clause or be used in an aggregate function
The only difference between two queries is that I am using sub query in the latter to limit the rows before joining. How do I solve this error?


texts: Same Query plan, no new query competition, same hardware, but stored procedure runs slowly

texts:
We have an SSRS report that is based on about 8 stored procedures which was running fine up until a few days ago. The report used to run in about 2 hours. Last Sunday (September 15 2019) it suddenly started timing out based on the SSRS timeout setting. We changed this to more time and found that it finishes after 6 hours. We're also seeing CPU contention when this report runs that we didn't see previously. What's strange is that I can't seem to find any apparent changes that would account for this:

The SP is using the same query plan it's used for weeks. So, in QueryStore you can see it completing in its usual 2 hours for days and suddenly spike 9/15. Even after a restart, the same plan was picked and it isn't being forced.

The underlying server is virtual, but we didn't change any resources until a few days later when we ended up adding more cores (which helped). Our VM guy isn't seeing any underlying hardware contention and from what I've seen, he's right.

The SSRS report itself wasn't changed. Same subscription, same configuration (other than the timeout setting being changed after the first night of bad performance). Statistics are set to update right before this report kicks off.

I'm pretty stumped here. I hate that for now we've had to settle with just throwing more resources at it. I tried searching for this issue online, but no one else seems to have posted about this specific scenario. Any suggestions? Below is the part of the code that takes up to 6 hours total.

EDIT: Link to Query plan: https://www.brentozar.com/pastetheplan/?id=SJ0XtSWwH

SELECT DISTINCT Object1.Column1
    , Function1(CASE
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN ?
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN ?
            ELSE ?
            END) OVER (PARTITION BY Object1.Column1) AS Column4
    , Function1(CASE
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN Function2(Column5,Object2.Column6, Object3.Column7)
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN Function2(Column5,Object2.Column6, Object3.Column7) * ?
            ELSE ?
            END) OVER (PARTITION BY Object1.Column1) AS Column8
    , Function1(CASE
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN Function2(Column5,Object2.Column6, Object3.Column7)
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN Function2(Column5,Object2.Column6, Object3.Column7) * ?
            ELSE ?
            END) OVER (PARTITION BY Object1.Column1)
            / NULLIF(Function1(CASE
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN ?
            WHEN Object1.Column2 IN (?) AND LEFT(Object1.Column3,?) = ?
                THEN ?
            ELSE ?
            END) OVER (PARTITION BY Object1.Column1), ?)
            / ? AS Column9
INTO Object4
FROM Object5 Object1
JOIN Schema1.Object6 Object7 ON Object7.Column1 = Object1.Column1
LEFT JOIN Schema1.Object8 Object9 ON Object9.Column10 = Object1.Column10
    LEFT OUTER JOIN Object10 Object3 ON Object3.Column10 = Object9.Column10
                        AND Object3.Column11 = ?
    JOIN (SELECT Object11.Column10, Function3(Object11.Column12) AS Column13
        FROM Object12 Object11 (NOLOCK)
        WHERE Object11.Column14 = ?
        GROUP BY Object11.Column10) Object13 ON Object9.Column10 = Object13.Column10

    JOIN Object12 Object2 (NOLOCK) ON Object2.Column10 = Object13.Column10
                        AND Object2.Column12 = Object13.Column13

    LEFT JOIN Database1.Schema1.Object14 Object15 ON Object2.Column15 = Object15.Column16
                                  AND Object15.Column14 = ?
                              AND Object2.Column17 IN (?)


texts: What type of database server/service to use for an increased insert rate?

texts:
Currently I am using MySQL and it processes 10,000 inserts per day, and soon it is going to be 200,000 inserts per day.

Now I am using a normal server which is okay with 10,000 inserts but processing 200,000 will require a large DB server. So my question is, what type of database server will I need to migrate to that could manage such a large insert rate?


texts: MS SQL difference between two latest records

texts:
Given table is :

create table events (
      event_type integer not null,
      value integer not null,
      time timestamp not null,
      unique(event_type, time)
  );

need to return difference of value for event_type between latest and second latest registered.


texts: Generate a new Sub Plan/Sequence ID in SQL 2005/8 Business Intelligence Development Studio

texts:
I have what is effectively a template SSIS package (well a maintenance plan actually) that is copied and used as the basis of new maintenance plans.
In this package there is a Sequence (or a sub-plan) containing a number of tasks.
I know I can change the package's ID in BIDS, i.e.:

However I can't change the ID of the sub-plan sequence, the ID is greyed out:

Neither BIDS for SQL Server 2005 or 2008(R2) allow me to alter this value.
Is there a way to achieve this?
Rationale:
To give you some background, these packages are automatically installed on a server using dtutil. A script is then run to create a SQL Agent job for the package. Part of that script executes a MSDB system stored procedure called msdb.dbo.sysmaintplan_subplans. This stored procedure is used to join up the dots between a job, a plan and a sub plan.
For example:
EXEC @ReturnCode = msdb.dbo.sp_maintplan_update_subplan
     @subplan_id = @thesubplanid,
     @plan_id = @theplanid,
     @name= @subplanname,
     @description = 'Created by Provisioner',
     @job_id = @jobid,
     @schedule_id = @jobscheduleid_out,
     @allow_create = 1

@plan_id is the package ID and @subplan_id is the sequence or sub-plan ID as shown in the screenshots above.
sysmaintplan_subplans adds a record to the table msdb.dbo.sysmaintplan_subplan, however the primary key is the subplan_id. This is why I need to be able to change the ID of the sub-plan in BIDS.


texts: How to escape XML characters in TSQL before converting to XML?

texts:
I have XML structure which is input param for my stored procedure. It contains element with & (which is escaped in XML). When I extract that element to VARCHAR I receive & which is not valid XML character. I need to escape it, before converting to XML again. How to do that without REPLACE?

I have the following text: param1=xyz&para2=dasdasdfdas&param3. It is a part of query string. I converted it to XML and send it as a part of XML structure:

<zzz xmlns="http://example.com">
  <aaa>aaa</aaa>
  <bbb>param1=xyz&amp;para2=dasdasdfdas&amp;param3</bbb>
</zzz>

Inside the stored procedure I need to extract it. I do that with:

ISNULL(NULLIF(LTRIM(RTRIM(@XMLInput.value('declare default element namespace "http://example.com"; (zzz/bbb)[1]', 'NVARCHAR(250)'))), ''), '');

I after that the value contains normal text (&-s are not escaped - &).

After some processing I need to put that string inside other XML. I make:

CAST( ... AS XML);

Because &-s are not escaped I got error.


texts: SCAN listener redirects connection to another port

texts:
I have setup RAC. I have two listeners LISTENER and LISTENER_DG.
LISTENER is listening port 1521
LISTENER_DG is listening 1530

When client connects to the database using SHARED connection mode even indicating port 1521 during connection, scan listener sometimes forwards connection to the vip ip on port 1530 and because netadmins just opened 1521 port for client and not 1530, connection fails.

This happens only on shared connection mode. DEDICATED works good.

LISTENER_DG (port 1530) is used for data guard broker  and for it I am using port other than 1521.

Why does scan forward connections to 1530 event client uses 1521 port in tnsnames.ora?


texts: How to display queries of separate tables in adjacent columns?

texts:
I have two tables - one titled "planning constraints" which contains the 'sot_allowed' time intervals, and one titled "planning" which contains the 'sot_contribution' time interval.

Here are the schema of the two tables (edited for readiability):

                         Table "public.planning_constraints"
   Column    |           Type           |          Modifiers
-------------+--------------------------+-------------------------------
 start_time  | timestamp with time zone |
 end_time    | timestamp with time zone |
 sot_allowed | interval                 |

                         Table "public.planning"
      Column      |           Type           |         Modifiers
------------------+--------------------------+----------------------------
 start_time       | timestamp with time zone |
 end_time         | timestamp with time zone |
 sot_contribution | interval                 |

I can query them separately and produce the totals that I want.  The query of the "planning_constraints" table is:

SELECT
  date_trunc('day', start_time - INTERVAL '18 hours')::date AS planning_day,
  sum(sot_allowed) AS minutes_allowed
FROM planning_constraints
WHERE start_time>='2016-11-26 18:00:00+00' AND start_time<'2016-12-03 18:00:00+00' AND comment like '6%'
GROUP BY planning_day
ORDER BY planning_day;

which produces:

 planning_day | minutes_allowed
--------------+-----------------
 2016-11-26   | 01:24:00
 2016-11-27   | 01:38:00
 2016-11-28   | 01:29:00
 2016-11-29   | 01:43:00
 2016-11-30   | 01:32:00
 2016-12-01   | 01:41:00
 2016-12-02   | 01:43:00
(7 rows)

The query of the "planning" table is:

SELECT
  date_trunc('day', start_time - INTERVAL '18 hours')::date AS planning_day,
  sum(sot_contribution) AS minutes_planned
FROM planning
WHERE start_time>='2016-11-26 18:00:00' AND start_time<'2016-12-03 18:00:00+00'
GROUP BY planning_day
ORDER BY planning_day;

which produces:

 planning_day | minutes_planned
--------------+-----------------
 2016-11-26   | 01:19:12
 2016-11-27   | 01:38:23
 2016-11-28   | 01:27:36
 2016-11-29   | 01:40:39
 2016-11-30   | 01:27:16
 2016-12-01   | 01:38:28
 2016-12-02   | 01:42:50
(7 rows)

I would like to present the results of these queries in adjacent columns, along with the difference between them.  Here is my attempt to do so:

SELECT
  date_trunc('day', c.start_time - INTERVAL '18 hours')::date AS planning_day,
  sum(c.sot_allowed) AS minutes_allowed,
  sum(p.sot_contribution) AS minutes_planned,
  (sum(c.sot_allowed) - sum(p.sot_contribution)) AS diff
FROM planning_constraints c, planning p
WHERE c.start_time>='2016-11-26 18:00:00+00' AND c.start_time<'2016-12-03 18:00:00+00' AND c.comment like '6%'
AND p.start_time>='2016-11-26 18:00:00' AND p.start_time<'2016-12-03 18:00:00+00'
GROUP BY planning_day
ORDER BY planning_day;

which produces:

 planning_day | minutes_allowed | minutes_planned |   diff
--------------+-----------------+-----------------+-----------
 2016-11-26   | 576:48:00       | 43:37:36        | 533:10:24
 2016-11-27   | 672:56:00       | 43:37:36        | 629:18:24
 2016-11-28   | 611:08:00       | 43:37:36        | 567:30:24
 2016-11-29   | 707:16:00       | 43:37:36        | 663:38:24
 2016-11-30   | 631:44:00       | 43:37:36        | 588:06:24
 2016-12-01   | 693:32:00       | 43:37:36        | 649:54:24
 2016-12-02   | 707:16:00       | 43:37:36        | 663:38:24
(7 rows)

The 'minutes_allowed' and 'minutes_planned' columns do not present the correct values.  I feel like I am missing something small here, however, I have been unable to find a solution.


texts: Boolean logic with BITs and Booleans in SQL Server

texts:
How can I mix boolean logic with bitwise and/or operators in SQL Server, specifically SQL Azure Database and/or SQL Server 2016?

To demonstrate what I'm trying to do, consider the following script:

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
--INSERT INTO @Data VALUES ((@Flag1 | @String LIKE '%qwerty%') & @Flag2)
SELECT * FROM @Data

The script works fine as-is but the moment you uncomment out that second INSERT, all hell breaks loose. I understand this is invalid syntax because I'm mixing boolean logic with bitwise operators. But what is the right way to get this logic in there? Without going crazy with CASE WHEN statements, is there some way to do this?


texts:
http://en.wikipedia.org/wiki/Database_engine

mentions about database engines aka storage engines. What all storage engines can be used with the Oracle database?


texts: SQL Server 2017 & External Python Modules

texts:
I'm doing some PoC work with Python integration in SQL Server 2017.

I have got the basic steps working, & have completed this example successfully:
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/run-python-using-t-sql

For the PoC I'm trying to complete, I need to us some external python modules (for example tensorflow) that don't seem to come with the standard python libraries that are bundled with SQL Server.

In a standard python IDE, I would just do install with pip or git clone. If I do this on the server that is running SQL Server it completes successfully, but I can't seem to use the external modules from TSQL.

Error message is:

ImportError: No module named 'tensorflow'

Does anyone know if it is possible to do this?

I've tried googling, but not much coming back. I guess as this is such a new feature that there isn't a huge amount of knowledge in the community.


texts: ASPState Log file growing tremendously - SQL Server 2008

texts:
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Web Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

I have an ASPState database online, and its log file ASPState_log is growing tremendously. The current size is 536gb.

How can I maintain this log file?

I take a daily backups through SQL Agent job. Can I truncate the logs?

What is the best practice to keep the log file size in control?


texts: Simple question about schema (is this supposed to happen?)

texts:
I've created a simple database, a table, a schema, and a table with that schema ( so, 2 tables ).

--sysadmin
create database dba_schema
go

use dba_schema
go

create table tabela1 ( id int )
go

--LOGIN---------------------------------------------
USE [master]
GO
CREATE LOGIN [schema_user] WITH PASSWORD=N'schema_user',
DEFAULT_DATABASE=[dba_schema], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [dba_schema]
GO
CREATE USER [schema_user] FOR LOGIN [schema_user]
GO
USE [dba_schema]
GO
ALTER USER [schema_user] WITH DEFAULT_SCHEMA=[dbo]
GO

( Manually I gave read, write and DLL permissions )
------------------------------------------------

create schema schema1
go

create table schema1.tabela1 ( id int )
go

Then I connected with the user schema_user ( that has now only read, write, and ddl permission ).

But, even not giving permission to that schema, I could select both tables ( dbo schema, and the schema I've created )

select * from dbo.tabela1

select * from schema1.tabela1

Is this supposed to happen? I thought that, when we create a schema, we should GRANT permission to that schema, for a user to select from it, but as I see, after creating a schema, we should DENY permission on it, for user not to select. Is this approach right? Its default schema is DBO.


texts:
I have a query which needs to filter against the result of a scalar UDF. The query must be sent as a single statement (so I can't assign the UDF result to a local variable) and I cannot use a TVF. I am aware of the performance issues caused by scalar UDFs, which include forcing the entire plan to be run serially, excessive memory grants, cardinality estimation problems, and lack of inlining. For this question please assume that I need to use a scalar UDF.

The UDF itself is pretty expensive to call but in theory the queries can be logically implemented by the optimizer in such a way that the function only needs to be calculated once. I have mocked up a greatly simplified example for this question. The following query takes 6152 ms to execute on my machine:

SELECT x1.ID
FROM dbo.X_100_INTEGERS x1
WHERE x1.ID >= dbo.EXPENSIVE_UDF();

The filter operator in the query plan suggests that the function was evaluated once for each row:

DDL and data prep:

CREATE OR ALTER FUNCTION dbo.EXPENSIVE_UDF () RETURNS INT
AS
BEGIN
    DECLARE @tbl TABLE (VAL VARCHAR(5));

    -- make the function expensive to call
    INSERT INTO @tbl
    SELECT [VALUE]
    FROM STRING_SPLIT(REPLICATE(CAST('Z ' AS VARCHAR(MAX)), 20000), ' ');

    RETURN 1;
END;

GO

DROP TABLE IF EXISTS dbo.X_100_INTEGERS;

CREATE TABLE dbo.X_100_INTEGERS (ID INT NOT NULL);

-- insert 100 integers from 1 - 100
WITH
    L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO dbo.X_100_INTEGERS WITH (TABLOCK)
SELECT n FROM Nums WHERE n <= 100;

Here is a db fiddle link for the above example, although the code takes about 18 seconds to execute there.

In some cases I may not be able to edit the function's code because it is supplied by a vendor. In other cases I am able to make changes. How can I force a scalar UDF to be evaluated just once in a query?


texts: How to select first row from a join that returns multple rows on the primary key

texts:
This is related to this question:  Joining multiple tables results in duplicate rows

I have two tables that I am joining.  They share a key. The person table has one name per primary key but the email table has multiple emails per personId.  I want to only show the first email per person.  Presently I get multiple rows per person because they have multiple emails. I am running SQL-Server 2005.

EDIT: This is T-SQL.
First email is literally the first email row per person.

Edit 2:
First email as I see it would be the first email row that shows up in the join as SQL works through the query.  I does not matter which email shows up.  Only that no more than one email shows up.  I hope that makes it clearer.

Table1: Person
Table2: Email

Select Person.PersonName, Email.Email
From person
left join on Person.ID=Email.PersonId;


texts: Export database in one file with every dataset existing

texts:
I've got a unusual question.
The human resources needs to export an old DB to a new one.
And the guys managing the new DB wants one csv file with every dataset existing.
I did a Relational database and now i find it difficult to write the select statement.

I did this:

SELECT *
FROM teilnehmer, personen, seminare, termine, kategorien, haeuser
WHERE teilnehmer.sid = seminare.sid
AND teilnehmer.pid = personen.pid
AND termine.sid = seminare.sid
AND seminare.katid = kategorien.katid
AND personen.hausid = haeuser.hausid
ORDER BY personen.pid

but i only get ~1500 entrys.
I know thats to little, because when i look for a Name and all the workshops he/she did, i get much more than what is in this export.

I don't need the answer i would appreciate if someone could give me a little hint in how to get this done.
Till yet i had only like one join for the data i'd needed to display but this confuses me.


texts: Security or performance risks using SQL CLR

texts: Are there any particular security or performance risks in using the CLR in SQL Server ?


texts: MySQL utf8mb4 Migration - Replication Considerations

texts:
We're planning to migrate a MySQL database with a mix utf8 and latin1 tables to the new utf8mb4 character set per this article: https://mathiasbynens.be/notes/mysql-utf8mb4.

Our server is 5.7.22 and the live database has several hundred InnoDB tables, is several GB large and is setup in a master/slave replication.  I've successfully ran the conversion scripts on a copy of the database on a test server.  It takes about 3 hours to complete.

When this is done live, should replication be disabled and the conversion scripts be ran against the master and slave individually or is it OK to run against master with replication active?

We are planning a maintenance window for this update - all updates/queries from our applications will be suspended during this process.

I'm unsure about this because I've been reading about the MySQL 8 Upgrade (a separate, future project for us) and MySQL mentioned performing large operations requiring table rebuilds with replication disabled:
https://dev.mysql.com/doc/refman/8.0/en/replication-upgrade.html.  However, this may be relevant only in the context of the version 8 upgrade, which we aren't doing simultaneously.  Thanks.


texts: If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?

texts:
We have an issue with MySQL InnoDB performance.
We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts processing them, it changes this status field to 1, then in case of an exception, the status goes to 2 or in case of successful processing, the status is set to 3.
The records remain in this table for three days then they are deleted. This way, the record count grows to around 1.7M and remains there.

If I run this:

describe SELECT * FROM temptable where status=0

It shows around 900k examined records, and the query itself returns 0 matching rows in about 5 seconds. There is an index on the status field. If I drop this index and recreate it, then the examined rows value goes down to 1 and starts to grow again, as new records are inserted.

If I query records with the STATUS value of 2 (Exception state), and none of these records existed ever, then the examined row count stays at 1 and the query returns instantly.

SELECT * FROM temptable where status=0
0 row(s) returned   5.538 sec / 0.000 sec
SELECT * FROM temptable where status=2
0 row(s) returned   0.000 sec / 0.000 sec

You can reproduce the same behaviour by creating a table like this:

CREATE TABLE temptable (
    someprimary int(11) NOT NULL AUTO_INCREMENT,
    status int(11) NOT NULL,
    PRIMARY KEY (someprimary),
    KEY IND_STATUS (status)
) ENGINE=InnoDB;

Fill it with around 100000 zero values, then update some zeros to ones:

update temptable set STATUS=1 where mod(someprimary,11)=0

Set the remaining zeros to 3:

update temptable set STATUS=3 where STATUS=0

Now there are some STATUS=1 records, many STATUS=3 records and none with the value 0 or 2.  You can now run the aforementioned selects and see for yourself if the query where we search for 2 returns instantly and the search for 0 requires some noticeable time to complete.
Is there any way to solve this issue and get the results instantly if there are only between 0-10 records matching the query and there is an index on this field?

Update:

SELECT * FROM temptable where status=0;
Empty set (1.56 sec)

describe SELECT * FROM temptable where status=0;
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+-------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows   | Extra       |
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+-------------+
|  1 | SIMPLE      | temptable | ref  | IND_STATUS    | IND_STATUS | 4       | const | 144651 | Using index |
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

SELECT * FROM temptable where status=2;
Empty set (0.00 sec)
describe SELECT * FROM temptable where status=2;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | temptable | ref  | IND_STATUS    | IND_STATUS | 4       | const |    1 | Using index |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

(total record count is now 289303)
SELECT count(1) db, STATUS FROM temptable group by STATUS;
+--------+--------+
| db     | STATUS |
+--------+--------+
|  25808 |      1 |
| 262864 |      3 |
+--------+--------+
2 rows in set (1.55 sec)

Thank you!


texts: MariaDB - how to drop partition on master but not on slave

texts:
I've read about filtering replication and I only run into information I know already, like you can select only a database or a table to do replication on.

My use case is that I want to have a slim and fast master with main data organized into partitions - one partition per day (each day a new partition is created). The master only keeps most recent 10 days of data and I need the slaves to keep all.

How do I ALTER TABLE ... DROP PARTITION on master without being replicated on slaves?

I seems to me that somehow I have to instruct the master not to write this operation into bin-log, but how do I do it?


texts: How to restore TDE encrypted DB on AWS (with certificate not stored in master)?

texts:
The way Amazon RDS enable TDE feature is to generate a certificate stored in master. However, I need to restore a database that is encrypted by another certificate.
I have the cert file, private key file and password.
But I can only create a certificate with files and password in ANY OTHER DATABASE EXCEPT MASTER.
Could you please give me some advice on how to restore TDE encrypted DB with certificate not stored in master? Or is there a method to to back up certificate at master in Amazon RDS?
Thanks in Advance!


texts: Why is this query sometimes slow, and what can I do to make it faster?

texts:
I have a query to bin some log lines on a pre-processed "timeslice" field (this is unique every 15 minutes, for example "2018-04-11-15-3-3") There are approximately 6,000,000 rows in the database.

The performance seems slow and inconsistent; sometimes it will return a single timeslice and take 0.5 seconds, sometimes it will return thousands and take 0.0002 seconds.

SELECT  count(*), time_slice
    FROM  logs
    WHERE  deployment_id = 123
      and  type="FOO"
      and  received_at BETWEEN "2019-04-01 00:00:00" AND "2019-04-30 23:59:59"
    GROUP BY  time_slice

timeslice is a VARCHAR(18)
deployment_id is an INT(10)
type is a VARCHAR(16)
received_at is a DATETIME

I've created an index on each of those columns, which helps. It's still very inconsistent.

EXPLAIN SELECT tells me that it's using the deployment_id and type indexes.

Alternatively, is there a better way to bin lines on a 15 minute interval?


texts: drop postgres table with minimal locking

texts:
I'm trying to run the following Postgres migration script

DROP TABLE some_table;
DROP TABLE another_table;

The problem is that this script causes excessive locking when run on the production database and therefore has to be aborted. Is there a way to reduce the locking? I've considered removing all indices on these tables and/or foreign keys before executing the DROP statements, but I don't have an easy way to simulate the production environment in order to test whether this will have a positive or negative influence on the locking.


texts: How do you join tables at different points based on data in the first table?


texts: Report an error in merge's when-not-matched-then clause

texts:
Is it possible to report an error (raise exception or anything) in merge's when-not-matched-then clause? I'm aiming for something like

WHEN NOT MATCHED THEN RAISE NO_DATA_FOUND;

Or anything that will tell the caller that script failed.


texts:
One of our SQL Server 2014 database transaction logs is growing very fast and I cannot find the root cause for this.

Database in Simple recovery mode. During full backup the log file grows to its limits (800GB) and backup fails.

I checked the transactions during the backup and did not find anything strange.

All transactions are our regular loading and no maintenance jobs (index rebuilds etc.)

I am aware of that backup keep the log from reuse but this mean 800GB changes during the backup period and this does not make any sense.

I had compared this DB to similar DB size, loading and backup duration.
The second DB Log is not growing more than 400GB.

DB size is 7TB. Backup time is ~20H. The only time log is growing is during backup. Log_reuse shows correct Backup_Restore. No long running transactions.

The problem is the "speed" of log growth - compared to similar sites with same load and same backup duration, the log is 4 time less.


texts: Queries running slower on production (failover cluster) than on homologation env

texts:
I have a Windows Server 2012 cluster with two nodes running SQL Server 2012 for production and a Windows 2012 single server with SQL Server 2012 for homologation. I just restored the production database backup on homologation and tried some queries. The queries on production are taking longer to execute than on homologation environment.

This servers are all Hyper-V VMs, with 24GB RAM and two Intel Xeon 2.90GHz each. Why this different performance?

Is it possible to run a query on a specific node without changing the current active node in the failover manager?

UPDATE

Actually the servers are like this:

Dev:
2 Xeon E5-2667 (15M Cache, 2.90 GHz)
16GB RAM
SQL Server Min Memory: 4096
SQL Server Max Memory: 8192
Query Cost: 43,0161
Execution time: 16s

Homolog:
2 Xeon E5-2667 (15M Cache, 2.90 GHz)
16GB RAM
SQL Server Min Memory: 4096
SQL Server Max Memory: 8192
Query Cost: 42,6906
Execution time: 14s

Production (1 cluster manager and 2 nodes):
2 Xeon E5-2667 (15M Cache, 2.90 GHz)
24GB RAM
SQL Server Min Memory: 4096
SQL Server Max Memory: 16456
Query Cost: 45,5453
Execution time: 65s

Previously, the dev and production servers were using Min Memory = 0 and Max Memory = 2147483647, but it seems that changing the Max Memory didn't make any difference.

PS: I've executed the maintenance plan to rebuild the index and update the statistics after changing the Min and Max Memory.


texts: Setting maxdop 1 vs setting the threshold for parallelism high

texts:
I am looking at a possible fringe case for parallelism settings.

I've long since followed the credence that maxdop 1 is a viable option for Microsoft Dynamics CRM databases. As the nature of the database is strictly OLTP, and parallelism has little to no benefit for a CRM instance, I've always set CRM databases to maxdop 1.

However, I have in my care now a database instance that has:

Microsoft Dynamics CRM database
An ETL database that is used primarily for OLAP purposes

I've asked our business partners and the way they see the performance importance is as follows:

CRM has to be fast, period
ETL imports should not be throttled, but come second to CRM performance

As such, I'm inclined to sacrifice parallelism on the OLAP database, and set maxdop 1 to benefit the CRM maximally.
Yet some middle ground might exist.

If I were to set the cost threshold for parallelism to a number which excludes everything that CRM throws at it, but according to my query plans might grab some of the OLAP transactions, would there be any adverse effects for the CRM?

What, if anything, is the impact of keeping a threshold so high, it is almost never reached, versus setting maxdop 1, am I incurring some hidden cost in making SQL Server take the threshold into account when calculating query plans?


texts: How to know when/if I have too many indexes?

texts:
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").

From my understanding every added index can make an SQL SELECT query faster but also an UPDATE or INSERT query slower since the indexes have to be adjusted.

What I wonder is, when do I have "too many" indexes/statistics?

Maybe there is no clear answer on this but some rule-of-thumb.


texts: MySql - filter bad words in multiple tables using triggers

texts:
I have for example two tables blog_posts and image and each record in these tables have a text field, that may contain a bad word in it.

so the idea is, I want to check these records and change these bad words with a couple of stars (like ****) the number of stars is fixed and not dependant on words length.

and I have a bad words table called slang.

so what I have so far is like this:

I have two trigger for each table (blog_post and image), one for update and one for insert. so the bad words will filtered out on insert or update. the content of each trigger is the same as below:

BEGIN
  DECLARE i INT;
  DECLARE s VARCHAR(1000);
  DECLARE r VARCHAR(1000);
  SET i = 1;
  SET s = '';
  REPEAT
    SET s = (
      SELECT REPLACE(split, COALESCE(slang, ''), '***') FROM
        (SELECT strSplit(new.text, ' ', i) AS split) s
        LEFT JOIN slang w ON s.split = w.slang
      LIMIT 1
      );
    SET r = CONCAT_WS(' ', r, s);
    SET i = i + 1;
    UNTIL s IS NULL
  END REPEAT;
  SET new.text = r;
END

the strSplit procedure is defined like so (based on a StackOverflow or maybe a StackExchange question I don't remember it):

BEGIN
  DECLARE output VARCHAR(1000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END

so I want to make a trigger to update the blog posts and images text field each time we add or edit a record from slang table. so if I add someSlang into slang table, all images and blog posts that contain someSlang in their text field should get updated and place **** for each instance of someSlang.

I'll appreciate any help and hints.


texts: What's the difference between aud$ and dba_audit_trail tables in Oracle database?

texts: I'm studying about audit in Oracle database and when I configure the audit using audit_trail = db the Oracle stores in the two tables


texts:
I am about to start a project and currently working on data design. While I am done in defining schema of db, I am just wondering to ask from experts.

I have only one table in which I am going to insert rows (no update) and from same table I am going to query the data set. Rows contains different columns. Columns for which I am going to query are kept in separate columns and the data which is needed to give as ouput will store in single column as JSON data.

I have experience of MySQL and I am looking for some other options to get fast query. My db size could contains billion of records.

I have seen many options like Cassandra, dynomodb etc but all these options make me confuse to decide on which db I could chose. I have also seen BIG query Postgre SQL.

I am looking for open source and where I could avoid any monetary expenses. I am looking for querying large datasets and want response to be max of 500ms.

Thanks in advance for help and suggestions.

Regards,
Harpinder


texts: How do I search by attribute in DynamoDB?

texts:
I inherited a site that uses DynamoDB for its database, which I know nothing about unfortunately, and I'm trying to change a user's email for them.  There appears to be a users table in DynamoDB, but I don't know the user's id, only their username and current email, so at this point, I am just trying to find the record before updating it.  When I attempt to scan for these values in the web interface, I get no results; it just keeps searching.  If I scan in the web interface for an email address which I know exists, I also get no results.
Using these placeholder values,
Username: example
Email: example@example.com

I have tried a few commands using AWS CLI, but I get various errors:
aws dynamodb get-item --table-name users --key '{"Username": {"S": "example"}}'

This yields this error:
Unknown options: example}}', {S:

I have also tried this:
aws dynamodb query --table-name users --key-condition-expression "Username = :v1" --expression-attribute-values "{ \":v1\" : { \"S\" : \"example\" } }"

This yields this error:
An error occurred (ValidationException) when calling the Query operation: Query condition missed key schema element: id

I have also tried printing out all users, but it appears to fail for anything but the smallest tables:
aws dynamodb scan --table-name users

This yields this error:
An error occurred (ProvisionedThroughputExceededException) when calling the Scan operation (reached max retries: 2): The level of configured provisioned throughput for the table was exceeded. Consider increasing your provisioning level with the UpdateTable API.

Not sure what to do at this point.  Any advice?


texts: Reducing I/O operations for often-updating data in MariaDB

texts:
I have a big table where I update something like 10k rows per second on average, but just one column. Currently it looks to me that MariaDB writes those changes rather instantly onto the disk creating a constant significant write I/O.

However, for this one column, I don't care too much, if the data is saved to disk in real time, I rather want to have less write I/O on the disk and a higher risk of data loss.

Is there any way I can tell MariaDB to cache this data for a bit longer time in memory before writing it to the disk?

Basically, the column is a "information_last_verified_at" timestamp that I regularly update and I can live with it being a few minutes off in the unlikely event of some server problems.


texts: Large long-running reporting queries are causing very slow new mysql connections

texts:
Usually, mysql's connecton time is in the 0.001 second range. However, when I run a large reporting query (that spans multiple tables with aggregates), I get tons of warnings saying that new mysql connections are taking between .5 and 2 seconds just to connect! Why would a single large query affect connection times so much?

Doing research online, I can't seem to find which mysql settings would need to be tweaked to mitigate the issue.

I'm on MySQL 5.6, using all InnoDB tables.


texts: Ora - 00088 error: command cannot be executed by shared server

texts:
I get this error from the database when I run the query:

select status, error from v$archive_dest where dest_id=2;

It's a dataguard environment. I have never seen this problem before.

I'm not quite sure what this means or how it can be fixed.

What is generally the cause of this error?


texts: Is it possible to query for the job schedule description?

texts: Working on putting together a script to analyze all of the active jobs and I have nearly everything I need, but if possible I would like to be able to query for the schedules description as well. Is this possible? I am able to query for the name and ID out of msdb.dbo.sysschedules but it doesn't seem to have the schedules description. Where can I find this information?


texts: Cannot upgrade to v12 due to sql_identifier data type

texts:
I am attempted to upgrade Postgres 11.8 to 12.3 on an AWS RDS instance. The upgrade fails with the following error:

Your installation contains the "sql_identifier" data type in user tables
and/or indexes.  The on-disk format for this data type has changed, so this
cluster cannot currently be upgraded.  You can remove the problem tables or
change the data type to "name" and restart the upgrade.
A list of the problem columns is in the file:
tables_using_sql_identifier.txt

I do not have access to this file. I believe it is not available to AWS users. I can find the sql_identifier columns with the following:
SELECT
atttypid::regtype, *
FROM   pg_attribute
WHERE  attrelid = 'information_schema.columns'::regclass
ORDER  BY attnum

I can update all of the columns to be of type "name" but changing the information_schema tables seems aggressive and I cannot find any information about this error anywhere on the internet. Any thoughts are appreciated.


texts: Best order of execution of mysql maintenance tools

texts:
After a change of data type or index for exemple. Given some queries to do maintenance tasks. What's the best order of doing these tasks?
ANALYSE TABLE foo;
CHECK TABLE foo;
OPTIMIZE TABLE foo;
REPAIR TABLE foo;
ALTER TABLE foo Engine=X; --defragment task

Does order matter? Like after an OPTIMIZE (which arrange disk space) which could eventually lead to corruption, it's better to do a CHECK after?
Does one task affect another and does one task is a repeatation of another? Like I have the feeling that OPTIMIZE is the same as defragment and CHECK is the same as REPAIR.


texts: SQL Server Maintenance Plan - Multiple vs Single Plan / Cleanup History

texts:
First Question:
I have created multiple Maintenance Plans for SQL-Server via SSMS. Now I'm wondering what is the "best" for similar plans: Multiple or single plans with subplans? Is it safe to use multiple tasks in one subplan?
At the moment I have 7 separate plans:

Backup - Full (weekly)
Backup - Diff (daily)
Backup - Transaction (every 30min)
Delete old "bak" files (older than 6 months, executed weekly)
Delete old "trn" files (older than 6 months, executed weekly)
Delete old maintenance log files "txt" (older than 6 months, executed weekly)
Cleanup history (older than 6 months, executed weekly)

For example I could create one backup-plan with 3 subplan (full, diff, transaction - because of different times) and a second plan with one subplan (executed weekly) with 4 tasks inside (delete bak, trn, txt, history) executed at the same time. What would be your recommendation?
I'm monitoring the failed jobs with icinga2 => for the second subplan with 4 tasks => will this reported as failed job if one single task fails?
Second Question:
How big can the history get - with the knowledge of the first question => How long should I keep it?  For example I have chosen for the cleanup history 6 months => "synchronized" with deleting bak/trn files - because if is not equal either I can't select the restore "point in time" (history deleted, bak/trn files available) in the GUI or I will see a "point in time" (GUI) without having bak/trn files for it.
But I'm not sure how big the database (containing the history) can get? Is it "safe" to only delete it all 6 months or should I cleanup it more often?


texts:
I've had trouble finding a straight definition of normal forms online.

I don't want any examples or metaphors, just what each form is defined by in a nice format ! I believe there is more than 4 normal forms so it would be greatly appreciated if you could provide the extra information.

Please complete any missing information I've found or lay it out in a better format :

1FN:

Each cell is to be single valued
Entries in a column must be of the same type
Rows must be uniquely identified

2FN:

All attributes must be dependent of the key

3FN:

Rows must be uniquely identifiable only by the key

4FN:

No multi-valued dependencies


texts: Query with "Logical operator (AND)" that does not return the desired result set

texts:
I have a table:
create table test_table
(customer_num  NUMBER,
 dep_group     VARCHAR2(50),
 dep_type      VARCHAR2(50),
 balance       NUMBER)

the table has two records:
                  " test_table "
 --------------------------------------------------
 customer_num   dep_group   dep_type    balance
     1            x           x1          10
     1            x           x2          10

The query below must return two records of the table but it does not, and it's weird!!
select *
from test_table
where ((dep_group = 'x') AND (dep_type = 'x1') AND ((balance) > 1)

           AND

     ((dep_group = 'x') AND (dep_type = 'x2') AND ((balance) > 1)))

when I run each part of the query separately , it gives me the desired record
Q1:
 select *
 from test_tablet t
 where (dep_group = 'x') AND (dep_type = 'x1') AND ((balance) > 1)

Q2:
 select *
 from test_tablet t
 where (dep_group = 'x') AND (dep_type = 'x2') AND ((balance) > 1)

I mean customer = 1 matches both conditions and the original query must give me the two records of the table. Is there something wrong with my query or my understanding of the logical operators?
Thanks in advance.


texts: I keep getting "file is open in another program" when i try to copy LDFs from one production to dev. Is there another way to copy these ldfs to dev server?


texts:
I have a table as follows:

DROP TABLE IF EXISTS #PersonnelRecord;

CREATE TABLE #PersonnelRecord
(
    person_id int NOT NULL,
    company_id int NOT NULL,
    entry_date date NOT NULL,
    exit_date date NOT NULL,

    CONSTRAINT PK_PersonnelRecord PRIMARY KEY (person_id)
);

INSERT INTO #PersonnelRecord
    (person_id, company_id, entry_date, exit_date)
VALUES
    (1, 24, '2004-03-17', '2010-12-31'),
    (2, 24, '2011-04-18', '2019-11-28'),
    (3, 25, '2017-02-10', '2019-10-20'),
    (4, 34, '2004-03-17', '2010-12-31'),
    (5, 24, '2004-03-17', '2999-01-01'),
    (6, 24, '2010-03-20', '2999-01-01');

SELECT *
FROM #PersonnelRecord pr
ORDER BY pr.person_id;

db<>fiddle link

So a table that shows when people have entered and exited a company. 2999-01-01 means the person is still at the company.

I would like to add a count to each row of how many people are working at the company at time of the exit date. The result would be:

|-----|
|  3  |
|-----|
|  3  |
|-----|
|  1  |
|-----|
|  1  |
|-----|
|  2  |
|-----|
|  2  |
|-----|

How could I achieve that? Thanks for the help.


texts: tables for different sources of payments

texts:
Selling software products. I have a table "licences". Each software licences is created due to a payment. I am creating tables to accept payments from different sources (e.g. manual or PayPal.) I'm having trouble creating the tables for the payments and the foreign keys to see the association between the licence and a its payment and to ensure data integrity.

OPTION 1

My initial design was to have a single payments table and then more details tables for the various payment types. The common columns go in the payments table and the uncommon columns go in the details table.

licences
--------
id
product_id
account_id
payment_id

payments
--------
id
amount
currency
details_table
details_id

paypal_payments
---------------
id
ipn
payer_status
payer_email
...

manual_payments
---------------
id
notes
...

The details_table could have a value "paypal_payments" or "manual_payments" and is an indication of the payment type. With this design, a foreign key on details_id is not easy to make, enforce, or have cascade.

OPTION 2

To rescue the design in option 1, I thought about moving the foreign key to the details tables.

licences
--------
id
product_id
account_id
payment_id

payments
--------
id
amount
currency
details_table

paypal_payments
---------------
id
payment_id
ipn
payer_status
payer_email
...

manual_payments
---------------
id
payment_id
notes
...

This does not seem ideal because a paypal payment and a manual payment could both refer to the same payment which is nonsense.

OPTION 3

I've thought about just pushing everything into one payments table and allowing NULL depending on the source of the payment. A trigger procedure could ensure that certain columns are not NULL if the particular type requires it.

licences
--------
id
product_id
account_id
payment_id

payments
--------
id
amount
currency
type
ipn
payer_status
payer_email
...
notes
...

I know this is the inelegant sledgehammer approach and I don't want to do it.

What to do? I've encountered this issue in many thought experiments and have always been stumped.

Thanks.


texts: Why is my server with memory pressure when I have 4.3GB available memory?

texts:
I'm doing a couple of tests in my local computer and I noticed that the plan cache was being wiped every couple of minutes.
After researching a bit I noticed on my error log that my instance was with memory pressure and paging out...
I really can't understand what's going on as I do have my perfmon open all the time and I do have 4.3GB available memory and the instance is not caped in memory limit.

Do you guys have any idea what's going on?
I also would like to take this opportunity to ask if anyone knows a way to catch when does the lazy writer gets executed ?Is there any way to track him down ?

Thank you all!
@@Version: Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
sys.dm_os_ring_buffers

<Record id = "101241" type ="RING_BUFFER_RESOURCE_MONITOR" time ="4419025954">
<ResourceMonitor>
    <Notification>RESOURCE_MEMPHYSICAL_LOW</Notification>
    <IndicatorsProcess>2</IndicatorsProcess>
    <IndicatorsSystem>2</IndicatorsSystem>
    <IndicatorsPool>2</IndicatorsPool>
    <NodeId>0</NodeId>
    <Effect type="APPLY_LOWPM" state="EFFECT_ON" reversed="1">1114779</Effect>
    <Effect type="APPLY_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>
    <Effect type="REVERT_HIGHPM" state="EFFECT_ON" reversed="1">791590828</Effect>
</ResourceMonitor>
<MemoryNode id="0">
    <TargetMemory>388744</TargetMemory>
    <ReservedMemory>34086804</ReservedMemory>
    <CommittedMemory>388740</CommittedMemory>
    <SharedMemory>0</SharedMemory>
    <AWEMemory>0</AWEMemory>
    <PagesMemory>151288</PagesMemory>
</MemoryNode>
<MemoryRecord>
    <MemoryUtilization>48</MemoryUtilization>
    <TotalPhysicalMemory>16664584</TotalPhysicalMemory>
    <AvailablePhysicalMemory>4534516</AvailablePhysicalMemory>
    <TotalPageFile>30857376</TotalPageFile>
    <AvailablePageFile>8330020</AvailablePageFile>
    <TotalVirtualAddressSpace>137438953344</TotalVirtualAddressSpace>
    <AvailableVirtualAddressSpace>137404308856</AvailableVirtualAddressSpace>
    <AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace>
</MemoryRecord>


texts: Retrieving data from stored procedure's cursor

texts:
I want to use the ResultSet in java program to get data from Stored procedure's cursor. Is that possible with SQL Server?

My code:

CREATE PROCEDURE dbo.SampleProcedure AS
DECLARE @FirstName varchar(64)
DECLARE @LastName varchar(64)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEE

OPEN c1

FETCH NEXT FROM c1
INTO @FirstName, @LastName

CLOSE c1
DEALLOCATE c1

When I use EXEC SampleProcedure, I just got the output as "Command(s) completed successfully" and the Resultset shows null in java program. So what to change in my code so that my ResultSet retrieves data?

I want to know how to prepare ResultSet in the SQL Server code so that I can retrieve the same in java program.


texts: PostgreSQL: Generate a series of dates for each group in a table

texts:
I have a balances table in PostgreSQL 9.3 that looks like this:

CREATE TABLE balances (
  user_id INT
, balance INT
, as_of_date DATE
);

INSERT INTO balances (user_id, balance, as_of_date) VALUES
  (1, 100, '2016-01-03')
, (1,  50, '2016-01-02')
, (1,  10, '2016-01-01')
, (2, 200, '2016-01-01')
, (3,  30, '2016-01-03');

It only contains balances for dates that a user has made a transaction. I need it to contain a row for each user with their balance on each date in a given date range.

If the user does not have a row for a given date in the range, I need to use their balance from the day before.
If the user created their account after a given date in the range, I need to avoid creating a row for that user/date combination.

I can reference an accounts table to get users' create_date:

CREATE TABLE accounts (
  user_id INT
, create_date DATE
);

INSERT INTO accounts (user_id, create_date) VALUES
  (1, '2015-12-01')
, (2, '2015-12-31')
, (3, '2016-01-03');

My desired result looks like this:

+---------+---------+--------------------------+
| user_id | balance |        as_of_date        |
+---------+---------+--------------------------+
|       1 |     100 | 2016-01-03T00:00:00.000Z |
|       1 |      50 | 2016-01-02T00:00:00.000Z |
|       1 |      10 | 2016-01-01T00:00:00.000Z |
|       2 |     200 | 2016-01-03T00:00:00.000Z |
|       2 |     200 | 2016-01-02T00:00:00.000Z |
|       2 |     200 | 2016-01-01T00:00:00.000Z |
|       3 |      30 | 2016-01-03T00:00:00.000Z |
+---------+---------+--------------------------+

Note that rows have been added for user 2 for 2016-01-02 and 2016-01-03, carrying over the previous balance from 2016-01-01; and that no rows have been added for user 3, who was created on 2016-01-03.

To generate a series of dates in a date range, I know I can use:

SELECT d.date FROM GENERATE_SERIES('2016-01-01', '2016-01-03', '1 day'::INTERVAL) d

...but I am struggling with LEFT JOINing that series with each set of rows grouped by user_id.


texts: How to optimize performance for dimension attributes?

texts:
I'm looking to enhance the cube processing time and someone suggested me to set "AttributeHierarchyEnabled='False'" for attributes which were not used at user defined hierarchies but I'm not clear with the "AttributeHierarchyEnabled" property and it's functionalities.

Can anybody just explain me when to set

  AttributeHierarchyEnabled='True'

and

  AttributeHierarchyEnabled='False'

and it's effects ?

Also I would like to know the use of "AttributeHierarchyVisible" and "IsAggregatable" properties.

Please give me some information about it which is going to be very useful for me.


texts: How do I ensure only one instance of a job is running?

texts:
SQL Server 2014 Standard Edition.

We have a job that runs from 20-40 min. We only want one instance of this job running, but we don't want huge gaps between runs.

So, scheduling it every 60 min is not ideal, because there could be 30-40 dead min. Ideally, we would schedule it every 30 min, because that would give us the ideal of either running every 10 min, or running back to back.

On a Windows OS scheduled task, there are settings to control how to handle job instantiation when the job is still running.

How do I get some control on SQL Server?
How do I get to my ideal (or close to it)?


texts: PostgreSQL 9.3 slaves don't recognize hstore as a type

texts:
I am using the postgres built in replication with two slave nodes and a single master. On the master node (as well as the slaves) the hstore extension is installed. But whenever a query using hstore is ran on one of my slave nodes I get an error that type "hstore" does not exist.

So for example the query

SELECT product::hstore->make FROM products;

Would give me back all of the different product makes if it is ran on my master node. But as soon as I run it on the slave I get

ERROR:  type "hstore" does not exist

I have used \dx on the slaves to make sure the hstore extension is installed, and it does show as being installed. Do you have any advice on how I can fix this error?


texts: SUM() 3 columns with alias into a fourth one

texts:
This is one part of my procedure:

 select nome,

       --casos expedidos (- 3 meses)
       isnull((SELECT      count(cod_rep)
                    FROM   tbReps
                    where  tbreps.cod_status = 7
                    and          tbreps.cod_perito_primeiro=cod_usuario
                    AND          (MONTH(tbreps.datahora_emissao) = MONTH(@DATA1)
                    AND          YEAR(TBREPS.datahora_emissao) = YEAR(@DATA1))),0) as Laudos_Expedidos1,

       --casos expedidos (- 2 meses)
       isnull((SELECT      count(cod_rep)
                    FROM   tbReps
                    where  tbreps.cod_status = 7
                    and          tbreps.cod_perito_primeiro=cod_usuario
                    AND          (MONTH(tbreps.datahora_emissao) = MONTH(@DATA2)
                    AND          YEAR(TBREPS.datahora_emissao) = YEAR(@DATA2))),0) as Laudos_Expedidos2,

       --casos expedidos (- 1 meses)
       isnull((SELECT      count(cod_rep)
                    FROM   tbReps
                    where  tbreps.cod_status = 7
                    and          tbreps.cod_perito_primeiro=cod_usuario
                    AND          (MONTH(tbreps.datahora_emissao) = MONTH(@DATA3)
                    AND          YEAR(TBREPS.datahora_emissao) = YEAR(@DATA3))),0) as Laudos_Expedidos

It will create this:

How can I sum, for example, LAUDOS_EXPEDIDOS1 + LAUDOS_EXPEDIDOS2  Into a 4º column? like this:

I'm pretty sure it's something really easy. I'm trying to use sum with the aliases, with no success.


texts: SQL Server SSL connection - What am I missing?

texts:
I am trying to set up my SQL Server 2008 R2 instance so that it only accepts SSL connections.  My understanding is that once this is set up, any client machine should need to have a certificate installed in order to connect.

I have followed the instructions on http://msdn.microsoft.com/en-us/library/ms191192(v=sql.105).aspx and done a lot of reading on a lot of other sites as well.  I have created a certificate, selected it in the SQL Server Configuration Manager (Network Configuration/Protocols/Properties), and I have set 'Force Encryption' to true.  I restarted the service running my instance.

After doing all this, when I try to connect to my instance remotely, I succeed.  Normally I like succeeding but in this case I haven't installed a certificate my development machine would recognize (this is where my instance is) and when I look at the connection properties, it says the connection is not encrypted.  If I choose to try an encrypted connection from the client (selecting this in SQL Server Management Studio's Options in the connection box), I get the certificate problem I expect.

So my questions are:  am I right that if things are set up correctly the connection from the client should fail (because it lacks an appropriate certificate) without my having to configure anything on the client?  And is there something obvious that I might be missing?  Some other place where I have to change a property, maybe?  I am hoping that someone who has done this before will have some ideas.

By the way, in creating my cert I followed SQL Dude's instructions at http://thesqldude.com/tag/makecert/.  The cert's name is my dev machine's full qualified domain name.


texts: Troubleshooting why I need to frequently run sp_refreshview

texts:
I'm an accidental dba for our ERP database (SQL Server 2017). ERP had become unresponsive for a user and I determined that a query running against a view was running very slowly.  I thought it might be related to a recently installed vendor service pack.  I tried running sp_refreshview against the view, and the query ran quickly again.
The odd thing is, the problem keeps coming back even when we haven't been running any updates that would change the tables the view relies on.  Each time running sp_refreshview gets things running normally again.  I added the call to sp_refreshview to the nightly maintenance job but that didn't eliminate the problem.
I'm looking for suggestions on how to identify what is causing this problem.
Thanks.


texts: How can you have a Buffer cache hit ratio of 100% with PLE of 103?

texts:
I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE checker from Denis Gobo and got a Buffer cache hit ratio of 100% with PLE of 103, how is that possible?
The buffer chache hit ratio of 100% is saying the 100% of pages needed are being found in memory, but the PLE is saying pages are only staying in memory for a couple of minutes.
Maybe there is some PLE value, below which buffer chache hit ratio does not add value?
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value, OBJECT_NAME
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND OBJECT_NAME = 'SQLServer:Buffer Manager'

Why I care:
My instance is SQL 2017 CU13, heavy OLTP, with 4GB of RAM for the SQL instance (single instance, 6GB RAM on the server).
I know that more than 1.5GB of RAM is devoted to the Plan Cache, according to some sources of the 4GB of RAM here, the Plan Cache should be able to use 3GB (found similar details several places, but all are old and none are Microsoft)
Select ( SUM(size_in_bytes)) /1024 /1024 AS size_in_MB FROM sys.dm_exec_cached_plans

I have not used any xevents yet to measure plan eviction, just trying to understand things.


texts: How does case-insensitive collation work?

texts:
Default collation type in SQL Server allows for indexing against case insensitive strings yet the case of the data is persisted.  How does this actually work?  I'm looking for the actual nuts and bolts, bits and bytes, or a good resource that explains it in detail.

create table casetest (fruitnames nvarchar(50) not null);
create unique index IX_fruitnames on casetest(fruitnames);

insert into casetest values ('apples');
insert into casetest values ('Pears');
-- this insert fails
insert into casetest values ('pears');

-- this yields 'Pears' as a result
select * from casetest (forceseek) where fruitnames = 'PEARS'

update casetest set fruitnames = 'pears' where fruitnames = 'pEArs'

-- this yields 'pears' as a result
select * from casetest (forceseek) where fruitnames = 'PEARS'

Questions About SQL Server Collations You Were Too Shy to Ask by Robert Sheldon covers how to use collation.  It does not cover how collation works.  I'm interested in how an index can be efficiently created/queried not caring about case, while simultaneously storing case data.


texts:
I've been having allot of issues with my mysql databases on Windows dying on me. About once a week or so it will refuse to work and I'll spend all night poking at it & a few computer restarts later things will magically start working it seems.

I've done some searching but didn't turn up much.

What would cause these errors whenever I try to access a table?

2014-01-06 20:10:14 c7c InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2014-01-06 20:10:14 c7c InnoDB: Error: Fetch of persistent statistics requested for table "memory28_web"."core_settings" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.


texts: Do I need separate indexes for each type of query, or will one multi-column index work?

texts:
I somewhat know the answer to this question already, but I always feel as though there is more I need to pick up on the topic.

My basic understanding is that generally speaking, a single index that just includes all the fields you might be querying/sorting on at any given time isn't likely to be useful, yet I have seen this type of thing. As in, someone thought, "Well, if we just put all this stuff in an index, the database can use it to find what it needs", without having ever seen an execution plan for some of the actual queries being run.

Imagine a table like so:

id int pk/uid
name varchar(50)
customerId int (foreign key)
dateCreated datetime

I might see a single index including the name, customerId and dateCreated fields.

But my understanding is that such an index would not be used in a query like, for example:

SELECT [id], [name], [customerId], [dateCreated]
   FROM Representatives WHERE customerId=1
   ORDER BY dateCreated

For such a query, it seems to me that a better idea would be an index including the customerId and dateCreated fields, with the customerId field being 'first'. This would create an index that would have the data organized in such a way that this query could quickly find what it needs - in the order that it needs.

Another thing I see, perhaps as frequently as the first, is individual indexes on each field; so, one each on name, customerId and dateCreated fields.

Unlike the first example, this type of arrangement seems to me sometimes to at least be partially useful; the query's execution plan may show that at least it's using the index on the customerId to select the records, but it's not using the index with the dateCreated field to sort them.

I know this is a broad question, because the specific answer to any particular query on any particular set of tables is usually to see what the execution plan says it's going to do, and otherwise take the specifics of the table(s) and queries into account. Also, I know that it depends on how often a query might be run as opposed to the overhead of maintaining a particular index for it.

But I suppose what I'm asking is as a general 'starting point' for indexes, does the idea of having specific indexes for specific, frequently-pulled queries and the fields in the WHERE or ORDER BY clauses make sense?


texts: How to add a constraint to check if the date entered (column) is today's date

texts:
I was having this "database" exam and I bumped into the following question.

Alter table "x" to add a constraint that only lets you enter today's date in "x.datecolumb".

I clearly have no idea how to do so, so ...?


texts: Query multiple tables with foreign key filled or is null

texts:
If I have two table like:

table1 with id, field1 and id_table2 and
table2 with id and field2;

Can I retrieve all rows on table1 also if the foreign key is null without using union?
If I write:

select table1.id, table1.field1, table2.field2 from table1, table2
where  table1.id_table2=table2.id

it will return only the rows that contains id_table2!

if I write:

select table1.id, table1.field1, table2.field2
from   table1, table2
where  table1.id_table2 = table2.id
union
select table1.id, table1.field1, '' as field2
from   table1
where  table1.id_table2 is null

It should work correctly but It seems too much expensive to manage this simply situation! There is something better in mysql5+?


texts:
We have a deadlock issue in our environment. We need to select some rows and then update them.
Those queries are very frequent so we use a rowlock and updlock hints in select statement.
Those queries sometimes are causing deadlocks.
We believe that the reason is that two queries start locking the same rows on the same index but in different index "direction".
So when the same queries run simultaneously they can sometimes lock each other.
Do this assumption makes sense?
If yes, is there anything we can do to force "rowlock locking order"?


texts:
I am attempting to use HTML in my sql server send mail, but my email just shows what I type instead of formatting properly.  Can someone show me what needs to be altered in order for this to appear as HTML

declare @body1 varchar(4000)
set @body1 = '

        '

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mod',
    @from_address = 'modis@modisglobal.com',
    @recipients= 'rsmith@gmail.com',
    @subject= 'Test Email',
    @body = @body1


texts: SQL Server Service Account on Windows Server 2016 Workgroup Cluster

texts:
I am trying to install SQL Server 2016 failover clustered instance on a Windows Server 2016 workgroup cluster. I have tried using a virtual account and also a local account that exists on both nodes.

Both types of account throw the error:

  The specified Service Account is not valid. Please provide a domain account to continue

What type of account should I use for installing SQL Server 2016 failover clustered instance on a Windows Server 2016 workgroup cluster?


texts: What is a good way to verify that a restore completed successfully in SQL Server?

texts:
Right now, we test our SQL backups once a month by pulling the most recent backup file and restoring it onto a test database. If SQL Server says the restore is successful, we spot-check a few tables to make sure they have data in them.

Is it safe to assume that the restore was completely successful if SQL Server says it was? Is there a good way to verify that the restored data/indexes/etc are correct?


texts: Passing column names dynamically to UNPIVOT

texts:
I have a table with following data

First         Second        Third         Fourth        Fifth         Sixth
2013-08-20    2013-08-21    2013-08-22    2013-08-23    2013-08-24    2013-08-25

And using UNPIVOT

SELECT Data
    ,DATENAME(DW, Data) AS DayName
FROM Cal
UNPIVOT(Data FOR D IN (
            First,
            Second,
            Third,
            Fourth,
            Fifth,
            Sixth  )) AS unpvt

I get the following result

Data        DayName
2013-08-20  Tuesday
2013-08-21  Wednesday
2013-08-22  Thursday
2013-08-23  Friday
2013-08-24  Saturday
2013-08-25  Sunday

Now my question is can we pass column names dynamically to the UNPIVOT so that when the columns in the table increases we may not have to alter the statement.


texts:
I am looking here for experts' advice on how to manage updating stats for very large database approx 18 TB.

We have recently started facing performance issues and believe it is due to old stats.

Actually we have a job which runs exec sp_update stats and update with default sample rate, in our case 1.2 %. So we have to manually update stats and see some improvement.

I am sure scheduling FULL SCAN would be a challenge. As per my knowledge I am comparing rows with rows sampled. For example, on one table with size 400 GB and over 100M rows I can see sampled rows is about 2 to 4M. The large tables are partitioned.

We are on SQL Server 2012 Enterprise Edition. Trace flag 2371 is not enabled.

Please suggest how I can make use of updating stats in a much better way for such a large DB and how to play with that sample rate?


texts: INSERT INTO a large amount of data with many clients running SELECT

texts:
I need to run a query like this to insert a large amount of data (millions of rows) into a MySQL table (InnoDB, 5.6.10):

INSERT INTO target_table (a)
SELECT id FROM source_table
ON DUPLICATE KEY a=a;

This usually takes around 200 seconds to run when no clients are accessing target_table.

target_table now has around 20 clients 'polling' to see if data has arrived, so we now have a lot of clients performing queries like this one:

SELECT a FROM target_table WHERE a LIKE '0%';

I'm having trouble with the INSERT INTO statement now that these clients are polling - it fails with the error Lock wait timeout exceeded; try restarting transaction.

I'm a bit confused as to why whatever lock is required for INSERT INTO cannot be acquired because clients are running SELECT statements.

EDIT:

The CREATE statements for these tables are as follows:

CREATE TABLE `target_table` (
  `a` varchar(32) NOT NULL,
  PRIMARY KEY (`a`),
  CONSTRAINT `target_table_source_table` FOREIGN KEY (`a`) REFERENCES `source_table` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `source_table` (
  `id` varchar(32) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


texts: Copying a DB or tables from a local Postgres database to Heroku

texts:
I'm almost a novice with databases.
I created a PostgreSQL database with the python programming language.
I am pleased to note that the database was created as I wished. I could easily verify it from pgAdmin 4, the database administrator installed locally. But now, since it is ready to be used, I would like to upload it to Heroku in order to work with it inside my web application.
I followed the various steps slavishly on the site of Heroku, but I can not complete the various steps, especially at the following command:

set DATABASE_URL=postgres://$(whoami)

I wonder if it's an operating system problem, in the sense that the steps listed on the Heroku site could be os-agnostic and not addressed to Windows, the system I'm working on. I also believe that some commands are directed to connect the database that is located locally, not to migrate on the domain of Heroku.


texts: Best way to track data changes where there are no historical items - CDC or custom programming?

texts:
SQL Server 2016 Standard.
We're being asked to provide data that we just don't have. I'm looking for a way to obtain it.
We have an ERP system that has order statuses that are important to our organization. When the order status changes, it either means it's a completed order, it's in progress, needs attention, etc...
Our ERP system doesn't have historical tracking for things like this. Would this be a prime candidate for CDC? Or is there an easier way of getting this information from a trigger or some sort of custom programming?
Thanks in advance.


texts: How to find matching pairs inside table?


texts: Understanding the use of Sql command parameters

texts:
I am using MySql database and I have a stored procedure to insert data, like this-

CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`(
    IN id varchar(10),
    IN pwd varchar(10),
    IN fname varchar(45),
    IN lname varchar(45),
    IN email varchar(45),
    IN phone varchar(13),
    IN address varchar(145),
    IN r1 varchar(45),
    IN r2 varchar(45),
    IN r3 varchar(45),
    IN r4 varchar(45),
    IN r5 varchar(45),
    IN r6 varchar(45),
    IN r7 varchar(45),
    IN r8 varchar(45)
)
BEGIN
    insert into users
    values(id,pwd,fname,lname,email,phone,address,'yes',r1,r2,r3,r4,r5,r6,r7,r8);
END

In my Asp.net website I have some code like this which calls the stored procedure-

using (cmd = new MySqlCommand("CALL vtsdb.adduser('" + txt_userId.Text + "','" + txt_pwd.Text + "','" + txt_firstName.Text + "','" + txt_lastName.Text + "','" + txt_emailId.Text + "','" + txt_phoneNo.Text + "','" + txt_address.Text + "','" + txt_f1.Text + "','" + txt_f2.Text + "','" + txt_f3.Text + "','" + txt_f4.Text + "','" + txt_f5.Text + "','" + txt_f6.Text + "','" + txt_f7.Text + "','" + txt_f8.Text + "')", con))
            {
                cmd.ExecuteNonQuery();
                con.Close();
            }

What I would like to know-

Do I need to use sql parameters in the above code? I dont see my stored procedure vulnerable to a Sql injection attack. Please guide me if I am wrong.

Also what is the use of mentioning CommandType.StoredProcedure in my c# code while my code still executes well?


texts:
I have production SQL Server machine with 10 separate instances on it: The last instance that was added included an install of SSRS. Since that time I've seen high CPU usage on a random single core (80% or higher), I believe this is related to SSRS (it consistently has the a higher CPU usage than anything on the server, around 2-3%). I'm not having any luck tracking down what exactly it is doing hour after hour, day after day. It's doesn't grow to take over the all the machine resources but I like to figure out what exactly it's doing so I can decide whether I should actually be worried about or not...

EDIT [2015-5-12]: I've been able to confirm that it is indeed the SQL 2012 SSRS process that is the source of the extra CPU load and we are currently running perfmon and waiting for it to re-occur.


texts:
I have a need to forensically remove data from oracle. If I just delete it, my understanding is the data will still actually be in the data file until that space is reused. I'm not concerned about the redo/archive/undo space, those will age out reasonable quickly.

Is there any methods for ensuring data is actually removed from a data file?


texts:
I have a postgres database that has the following structure:

where the fields in column1 and column2 can take any value and where the order column indicates the order in which the original data was inserted. This table contains a lot of rows (with lots of duplicates) and I'm wondering whether there is a smarter way to store this data. Suppose that I were to create a unique id for each value per column and store the data like this:

Suppose that n is the number of rows in the first table and u the number of unique rows in the first table. If u is relatively large then the order column in the second table will have relatively short values in the order column fields, and if u is relatively large the fields in the order column in the second table will be quite wide. I would like to be able to compare the best storage method in both cases.

Question: I want to be able to compare these two tables to determine which storage method is better; is there a formula/method to find out how columns vs rows take up memory?


texts: Large Sleep counts causing THREADPOOL wait?

texts:
When I run sp_whoisactive throughout the day, I usually see we have around 500-700 connections on our server. We are occasionally getting THREADPOOL waits and high signal wait ratios (so we are thread starved at times).

However, these 500-700 connections only 10-25 of them are active. I wasn't sure if a sleeping SPID is still bound to a thread while it sleeps?

Could that be causing our high signal wait ratio and THREADPOOL waits?

I have a feeling our 3rd party application is not properly closing out connections and is causing us issues, but wanted to check first before I bring this up with them. Thanks!


texts: Query about MySQL Semaphores in Engine Status

texts:
During a recent problem / slow down with one of our slave databases I noticed that when I ran SHOW ENGINE INNODB STATUS. I am getting the following:

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 822986039, signal count 17045541908
--Thread 140562108442368 has waited at row0sel.c line 2930 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x7fd752e55c40 created in file buf0buf.c line 938
a writer (thread id 140562088974080) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.c line 2930
Last time write locked in file /tmp/buildd/mysql-5.5-5.5.44/storage/innobase/buf/buf0buf.c line 3168

In fact, there are about 15 instances of this.

Looking at the transactions there are apx 200 transactions listed, of which there are apx half showing as waiting in InnoDB queue, and the other half are fetching data.

All the transactions are SELECT queries, and none of them are showing any locks.

Can anyone tell me what the SEMAPHORES (above) relate to ?


texts: Oracle - ORA-01654: unable to extend index in tablespace

texts:
I referred to the below related question, but it was not helpful in my case:

ORA-01654: unable to extend index <name of the index> by 128 in tablespace <name of the Index tablespace>

I have 2 indexes in the same tablespace that I need to increase the size of.

I have 13 dbf files associated with that tablespace.

How can I make sure I increase the size of correct dbf file to fix the above error?


texts: Is that possible to backup MongoDB sharded cluster without LVM or mongodump?

texts:
There is a "LVM-solution" in MongoDB documentation: http://docs.mongodb.org/manual/tutorial/backup-sharded-cluster-with-filesystem-snapshots/

Can I do the same thing, but without using LVM snapshots, simply copy the files of my secondaries, and include a "mongodump" of my config data?


texts: How to check what type of lock an UPDATE statement uses?

texts: How can I determine if an update statement puts a table lock, page lock, or row lock on a table?


texts: Best Approach for Mirroring Data from SQL Server to MySQL

texts:
I need help in regards to replicating data from a third party point-of-sale system with a SQL Server backend to a MySQL DB.

Specifically, can anyone suggest a best way to mirror data from SQL Server on our MySQL instance?

Current process:

Select a dataset from the distant SQL Server and write to a text file using PHP.
Import the text file using LOAD INFILE using PHP.
Some data sets must be chunked into several iterations.

Drawbacks:

It is slow and cumbersome.
Requires polling their server every x minutes for changes.
Difficult to detect changes as most tables do not have an 'updated time' on the row to indicate a change.  We therefore have to sometimes assume we will need the whole table.
Tables are up to 11 million records (and growing).
Bandwidth is being used inefficiently.
Whenever the schema changes (short notice) on the SQL Server, we have to react to the changes and change every query.

Additional Circumstances:

Our third party SQL Server host will not allow any changes to the system on their end.
Our side (MySQL) is more flexible to system changes.
We cannot change to a SQL Server platform as we are a LAMP shop.
We are very capable IT developers, but this issue seems to warrant additional advice.
Management is against an organic point-of-sale.


texts: SQL Server AlwaysOn ReadOnly Routing to Secondary

texts:
I configured SQL Server AlwaysOn to redirect Read-Only to Secondary and tried to check with ApplicationIntent=ReadOnly setting in SSMS by connecting to the Listener. But my connection is always going to Primary only as revealed by

SELECT @@SERVERNAME

Does anyone know what is wrong with the settings or reason for this?
I checked altering the settings but didn't work.

Setup Details:

  SQL Server 2017
  AG Properties
  Using Admin account to test the routing.