texts:
Say I have 3 tables Products, Orders and Orders Product

Products Table

ID | StockNum | Description | Price
4  | 452SB    | Black       | 45.65
5  | 752SA    | Green       | 17.12

Orders Table

ID  | OrderNum | Total  |
14  | 12312    | 79.89  |
15  | 12313    | 45.65  |

Orders Product Table

ID  | OrderID  | Product  | Qty   |
45  | 14       | 452SB    | 1     |
46  | 14       | 752SA    | 2     |
47  | 15       | 452SB    | 1     |

If product information changes (Price or Description) this would throw off all previous order information, whats the best solution for this?. Also does it make more sense to be storing the product ID instead of the StockNum?

** Update **
Does This Make More Sense?  Also everything marked with ID at the end is FK

Products Table

ID | StockNum | ProductDetailsID |
4  | 452SB    | 8                |
5  | 752SA    | 9                |

Products Details Table

ID | Description | Price
8  | Black       | 45.65
9  | Green       | 17.12

Orders Table

ID  | OrderNum | Total  |
14  | 12312    | 79.89  |
15  | 12313    | 45.65  |

Orders Product Table

ID  | OrderID  | Product  | Qty   | ProductDetailsID |
45  | 14       | 452SB    | 1     | 8                |
46  | 14       | 752SA    | 2     | 9                |
47  | 15       | 452SB    | 1     | 8                |


texts: How to give precedence to rows from one table over matching rows in another?

texts:
I've got (key, value) pairs stored in two different tables - company_settings and branch_settings. A key can exist in none, one or both of the tables.

In case the key exists in both tables, I'd like to use the value stored in the branch_settings table. Otherwise, if available, I'd like to fall back to the value stored in the company_settings table.

company_settings
-------------------------
| key    | value
-------------------------
| key.A  | 4             |
-------------------------
| key.B  | 5             |
-------------------------

branch_settings
-------------------------
| key    | value
-------------------------
| key.A  | 1             |
-------------------------

So, if I query for key.A the result should be 1 since key.A exists in both tables and I want to give higher precedence to records from branch_settings table.

If I query for key.B the result should be 5 since key.B exists only in company_settings table.


texts: DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query

texts:
I have data in a java object as data1, data2.

data1 and data2 together forms a composite key in myTable where I want to insert the object.

The writing is happening as a batch. Like 10 insert statements are prepared using 10 objects and are executed as a batch.

I want to insert the above data with the constraint: data1 + data2 should not already be present in myTable i.e. data1 + data2 should be unique --- if unique then write else just ignore.

The query I am using is:

Insert into mySchema.myTable(column1, column2)
  select 'abc', '123'
  from SYSIBM.DUAL
  where not exists
        ( select 1
          from mySchema.myTable A
          where 'abc' = A.column1
          and '123' = A.column2
        )

Running above query independently for single set of data runs successfully.

However, while running in batch scenario I am getting "com.ibm.db2.jcc.b.ie: Non-atomic batch failure." error.

I think it has something to do with using SYSIBM.DUAL in batch scenario.

Code which is failing:

Insert Query:

Insert into mySchema.myTable(column1, column2)
  select ?, ?
  from SYSIBM.DUAL
  where not exists
        ( select 1
          from mySchema.myTable A
          where ? = A.column1
          and ? = A.column2
        )

Statement Setters:

ps.setString(1, item.getColumn1());
ps.setString(2, item.getColumn2());
ps.setString(3, item.getColumn1());
ps.setString(4, item.getColumn2());

where item is the java object holding the two columns to write.

Error is:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar

[Insert into mySchema.myTable(column1, column2) select ?,?
from SYSIBM.DUAL
where not exists
(select 1 from mySchema.myTable A where ?=A.column1 and ?=A.column2)];

nested exception is com.ibm.db2.jcc.b.ie: Non-atomic batch failure.

The batch was submitted, but at least one exception occurred on an
individual member of the batch.

Use getNextException() to retrieve the exceptions for specific batched elements.

Thanks,
Nik


texts:
The cursor query and select value query returns rows if I run it in mysql but when in a cursor it always exits out of loop.

Anything wrong here?

I've added "BEFORE LOOP", "EXIT" and "IN LOOP" so it prints where it is but it always starts with BEFORE LOOP and then ends with EXIT.

CREATE PROCEDURE getTotal()
BEGIN

DECLARE HOSTID INTEGER;
DECLARE cITEMID INT;
declare finished bool default false;
DECLARE Total INT;
declare cur1 cursor for SELECT itemid  FROM items WHERE hostid = 10579;
declare continue handler for not found set finished = true;

open cur1;
   loop_1: loop
        fetch cur1 into cITEMID;
        SELECT "BEFORE LOOP";
        if finished then
            SELECT "EXIT";
            leave loop_1;
        end if;
        SELECT "IN LOOP";
-- Test query
      SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1;

-- Final select query will look like this.
-- SET @Total := @Total + (SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1);
-- SELECT @Total;

  end loop;
close cur1;

END //

DELIMITER ;

Queries:

SELECT itemid  FROM items WHERE hostid = 10579;
| itemid |
| 12345  |
| 12346  |
| 12347  |

SELECT value from history_uint WHERE itemid = 12345 ORDER BY itemid DESC LIMIT 1;
| value |
| 1     |

SELECT * from history_uint;

| itemid | value | clock (unixtimestamp) |
| 12345  | 13     | 4364564654654        |
| 12346  | 1      | 4364564654657        |
| 12347  | 16     | 4364564654654        |
| 12345  | 13     | 4364564654756        |
| 12346  | 2      | 4364564654753        |
| 12347  | 15     | 4364564654756        |

Note: The clock column value is just made up.


texts: Update two columns of two tables using inner join

texts:
I have two tables in which there are two common columns (isActive & group_id)
I want to update the isActive column of two table joining the group_id.

This is the query I tried but it shows error.

UPDATE A set isActive = 0, B.isActive = 0
from OperatorGrouping A
inner join OperatorGroupingSteps B
on A.group_id = B.group_id
where A.group_id = 9


texts: Enabling "log_queries_not_using_indexes" disables "long_query_time"?

texts:
I noticed that, in Mysql, when enabling log_queries_not_using_indexes, slow_query_log and long_query_time, the generated slow query log file will contain a lot of queries even if the query time is far less than long_query_time.

It seems that once the log_queries_not_using_indexes is enabled, Mysql will log all the queries that not using indexes no matter what the "long_query_time" is.

Is there a way to overcome this? I mean, is there a way to log all the queries which take longer time than long_query_time, regardless of whether they are using indexes?


texts: Periodically very slow execution of simplest queries

texts:
Inspecting MySQL slow query log I found out that in certain periods of time there are a bunch of queries in log which are supposed to be very fast but were actually very slow.

For example:

# User@Host: ***[***] @  [***]
# Query_time: 14.532574  Lock_time: 0.050162 Rows_sent: 18  Rows_examined: 18
SET timestamp=1483014348;
CALL get_games();
# User@Host: ***[***] @  [***]
# Query_time: 15.287524  Lock_time: 0.008114 Rows_sent: 18  Rows_examined: 18
SET timestamp=1483014348;
CALL get_games();
# User@Host: ***[***] @  [***]
# Query_time: 15.637633  Lock_time: 0.027461 Rows_sent: 18  Rows_examined: 18
SET timestamp=1483014348;
CALL get_games();
# User@Host: ***[***] @  [***]
# Query_time: 15.070246  Lock_time: 0.050137 Rows_sent: 18  Rows_examined: 18
SET timestamp=1483014348;
CALL get_games();

Where procedure get_games is just a simple SELECT from table with 18 rows and normally executes immediately.

MySQL [db_receipt]> show create table game;
MySQL [db_receipt]> CREATE TABLE `game` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` smallint(5) unsigned NOT NULL COMMENT 'Гейтовый идентификатор игры',
  `product_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Название игры для печати в квитанции',
  `arm_url` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Url в АРМ-е',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

MySQL [db_receipt]> show create procedure get_games;
+-----------+----------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure                                                                                | character_set_client | collation_connection | Database Collation |
+-----------+----------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| get_games |          | CREATE DEFINER=`receipt_adm`@`%` PROCEDURE `get_games`()
l_proc: begin

    SELECT  `product_id`, `product_name` FROM `game`;

end | utf8                 | utf8_unicode_ci      | utf8_unicode_ci    |
+-----------+----------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+----

There are also a lot of other slow simple queries such select a row by primary key etc.

What can cause such a performance degradation? How can I troubleshoot a problem to find a reason?

EDIT: MySQL Server has very good characteristics and normally queries run fast. It's a production server under high load.

EDIT2: Another slow query example (on another database), also very simple:

# Time: 161230 12:13:39
# User@Host: ***[***] @ *** [***]
# Query_time: 4.780936  Lock_time: 0.000069 Rows_sent: 0  Rows_examined: 1
use ***;
SET timestamp=1483089219;
update ShoppingCart set cartStatus='ACQUIRED' where shoppingCartId=23423;


texts:
I am using encryption algo DES/CBC/PKCS5Padding. How do I encrypt data using this in DB2?

In Pl/SQL I used DBMS_CRYPTO package with DES_CBC_PKCS5 from DBMS_CRYPTO Block Cipher Suites - https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_crypto.htm

Version is DB2 v10.5.0.5 and it will run on a Linux box. Distro may be different. Query - SELECT service_level, fixpack_num FROM TABLE (sysproc.env_get_inst_info());


texts: Query Plan Different When Using Compiled Values

texts:
I have pulled out an execution plan from the plan cache and from the XML pulled out the compiled values. Then I have run the query in SSMS using the compiled values and the query plans are different even though they run against the same database.

Can anybody please explain why this occurred?


texts: Prevent reset of auto_increment id in Innodb database after server restart

texts:
I recently read that because of how InnoDB recalculates the AUTO_INCREMENT value when the server restarts, any records on the high end of the ID list may have their IDs reused.

Normally, this isn't a problem, because when a user is deleted everything associated with the ID is deleted from other tables too.

But I'm deliberately leaving their forum posts orphaned, labelled as "Posted by =User #123=", so that past conversations are retained. Clearly, should an ID be reused, this will be a problem.

I've never had this issue before because there were always enough new users to make it unlikely for an ID to be reused in this way. However on my new project signups are rare and inactive user deletions frequent (especially since the "Open Alpha" accounts only last for three days as a preview), and such ID reuse has happened three for three now.

I have "fixed" the issue by saving the correct value for AUTO_INCREMENT elsewhere and using that instead of relying on the internal value. Is there an actual way to have InnoDB remember the actual last value?


texts: Getting both Having and Not Having in same row

texts:
I have a query which I get the customers that have more than 1 orders
However, I would like to get the number of customer that has only 1 orders as well and divide them like 1 orders / more than 1 order

So that my table will have 3 colums

one_order, more_than_one_order, divided

I tried different approaches like duplicating the query like =1 but it is not the proper way

Query

SELECT COUNT(*)
FROM (
select customer_id from orders GROUP BY customer_id HAVING COUNT(order_id)>1
) A


texts: Replication Alternatives for Contained Databases

texts:
My team and I are in the process of designing a new application using the microservice architecture approach. Each application is going to to be responsible for releasing changes to its own database using a tool not unlike flyway (https://flywaydb.org)

To ensure that this approach works we are looking to use contained databases to help ensure that were not dependent on anything thats external to its database and to help ensure that the app is the only thing making changes to the db structure etc.

Other teams in the company have, in the past, been dependent on replication to pull data from our dbs so that they can report on it alongside data from other systems. The requirement for the access to this data in their system is still valid so I am looking at other means of getting the data copied from out system into theirs. They are only looking for information in intervals (such as hourly) rather than a live copy. Is there any way of us being able to provide this in a sensible way, whilst still using contained databases.


texts:
This is one of my first tries to code something. I'm trying to develop a code, to add first name, last name, and date, randomly.

I have this for now:

declare @date date,
        @val1 int,
        @val2 int
set @date = convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate()))

    insert into tblexecution ( fname,lname,brtday )
    values ('name1','Name2',@date)

This way, I need to change manually the name1 and name2.

is there a way to use, values of another table, in a variable, for this table? I don't know how to explain it but I have a table called Adventureworks2008.person.person (you guys know this database) with a lot of random names ( Fields > FirstName,LastName ), and I want to use this values, in my @val1 and @val2 variables, to be something like this:

declare @date date,
        @val1 int,
        @val2 int
set @date = convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1,
 getdate()))

    insert into tblexecution ( fname,lname,brtday )
    values (@val1,@val2,@date)

so I just loop ( or press f5 a lot ).

Edit1:

With this, I have at least a random int:

select right(rand(),2)

Edit2:

I could do this, But I think this is a poor job:

insert into  execution..tblexecution
select distinct  firstname, lastname,
    convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1,getdate())) as brtday
    from adventureworks2008.person.person

Edit3:

I could make this:

declare @date date,
        @val1 nvarchar(200),
        @val2 nvarchar(200)
set @date = convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate()));
set @val1=(select  top 1 firstname
               from adventureworks2008.person.person
              order by newid() );
set @val2=(select top 1 lastname
               from adventureworks2008.person.person
              order by newid() )

    insert into tblexecution ( fname,lname,brtday )
    values (@val1,@val2,@date)

is this the best way to use random variables in a query?


texts: timeout on updating nvarchar(max) after converting datatype

texts:
I recently Upgraded to 2008 Enterprise and took the opportunity to change the biggest table in my db with the maintenance window.

ran the following to change my ntext field

ALTER TABLE acomEstabLocale_tbl ALTER COLUMN [description] nvarchar(MAX)
GO
update acomEstabLocale_tbl set [description] = [description]

when I ran the second statement i got the following

length of LOB data (77404) to be replicated exceeds the exceeds configured maximum

ok, so after a poke around I found the sp_configure and ran :-

EXEC sp_configure ‘max text repl size’, 100000

but now I receiving the following

nvarchar max Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I'm looking to temporarily extend this query timeout period, can the just be done using the remote query timeout setting on the server properties ? or is there a T-SQL statement for just this query


texts: AWS RDS Really Odd Error... #1041 OUT OF MEMORY, Buffers OK and Memory OK

texts:
I have an AWS RDS (MySQL 5.6.35) db.m3.medium that has given me a random error when attempting to modify a table's structure in the past two weeks:

#1041 - Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

I've never encountered this error on even a much smaller instance with much less memory. It should be noted that encryption is enabled on this instance, so that might be adding a little bit of load.

When I check the memory statistics in the console, it doesn't appear to be even using anywhere near 100% usage. It doesn't appear that the instance is swapping at all either. After a reboot, I can modify tables fine. This table is very small... < 10 columns, little overhead, and not many rows (<100). Oddly enough, we have not modified the default buffer sizes in RDS (3/4 of instance memory allocation). Not only this, after checking the buffer tables, everything seemed OK as well.

Am I missing something here?

UPDATE: It happened again. Rebooting the RDS server appears to mitigate whatever problem is occurring. Here's a screenshot of buffer statistics at the time of the incident (this is BEFORE the reboot that fixes the problem)...

UPDATE 9/1/2017: I thought that we mitigated the issue for a while after we upgraded our RDS instance to m4.large... and for a while, it did. However, while performing some migrations earlier this evening... sure enough, error #1041 - OUT OF MEMORY. I immediately checked our CloudWatch reports for anything out of the ordinary, and our freeable memory is really high, not even concerningly fluctuating. Additionally, a reboot fixed the issue. I have yet to see it happen again, but I'm sure in a few weeks after some transactions it will happen again. Is this possibly a sign of a corrupted database or something? We have NEVER had any issues reading or writing to the database, and all applications are using their databases on this server just fine. We just can't initiate ALTER TABLE changes from inside phpMyAdmin...


texts: Round robin vs Demand Parallelism partitioning

texts:
This is related to having a common key driver on top of the query with a nested loops, and the parallelism for the rows coming out of driver being either demand type or round robin type. I would have assumed demand partitioning would perform better but I get opposite results.

I started the queries within the same try at the same time in SQL Server. When the queries running I monitored dm_exec_query_profiles dmv constantly. I noticed the Round robin versions start a lot faster, they are inserting a lot more rows a lot faster into the Table Insert portion in the dmv also they pick up a lot more rows quicker from the driver side parallelism portion. Thinking logically demand partitioning should be more advantageous since our SQL server is usually over 50-60% cpu, litespeed backups running, has 64 cores etc. I was able to balance rows processed on threads a lot better with round robin partitioning but also the data within partitions are so out of balance I noticed some threads in demand partitioning only processes 1 record from the driver whereas the average records from the driver is around 196. With the demand partitioning I order the rows within partitions descending vs in round robin I try to balance the rows better.

Should I always use round robin instead, why does round robin starts processing rows a lot faster than demand partitioning, can I do more optimization for demand partitioning?

The query plans are in the One Drive link, I couldn't find another way of posting them here (pastetheplan only accepts xml, which does not capture the extra information like wait stats & duration captured by Plan Explorer).

  CompareRoundRobinToDemand_DM_2_5114.pesession
   CompareRoundRobinToDemand_RRB_2_3046.pesession

started at the same time, Round Robin was considerably faster

  CompareRoundRobinToDemand_DM_3_5228.pesession
   CompareRoundRobinToDemand_RRB_3_4367.pesession

started at the same time, Round Robin was faster again

  CompareRoundRobinToDemand_DM_4_4813.pesession
   CompareRoundRobinToDemand_RRB_4_3577.pesession

started at the same time, Round Robin was faster again

Thank you in advance.

Query Plans

I am able to balance round robin rows almost perfect whereas for demand I just order the driver in rows descending order, balancing the rows in demand is an option in my procedure but did not produce better results. In my observation demand performs better when overall CPU usage is less and Round robin performs better when server is busier. I noticed demand creates one extra thread compared to round robin and also overall cpu utilization in demand is slightly higher than similar RRB version.

i am aware of imbalance of row distribution in demand. the memory grant is also on purpose, it's not using that much memory at the end. the first 16 records passed from driver is the same in demand vs round robin, but round robin starts processing them a lot faster for some reason. I want to understand why. I also want to understand when it's more beneficial to use demand or round robin. It seems like when server is idle demand works faster and when server has existing load round robin is faster, thats an observation so far, does that have a basis I dont know.


texts:
I have lot's of large objects ((n)test, varchar, image types) in my SQL-Server tables. I read the following article about sp_tableoption "In-Row Data" and "large value types out of row" but did not understand those tons of text.

It's explained that large object data types can depending of the settings be stored in the table or outside anywhere. It's called the "LOB storage space". Where is this space? What is the benefit of those settings? Is this suitable to raise read-performance?


texts:
Can you please any one help me for the following
TNSNAMES.ORA is located in Windows 2012 Server? Can you please give the correct path for the same.


texts: Oracle: are objects created by sys in a schema the same as objects created by that user?

texts:
For example, are there any differences between:

SCOTT@ORCL> create index a_idx on a(x);

and

SYS@ORCL> create index scott.a.idx on scott.a(x);


texts: Login fails even though it exists when failing over to mirror

texts:
I have mirroring set up on a production database.  I have a login setup on both the database serving the database and the mirror.  It has identical login information and configuration.

When I failover the database, the ASP.NET application fails over to the second database (it has a failover partner identified), but the login stops working.  For some reason after a bit it starts working.  When I fail back over to the original, production database, the ASP.NET application is kicked out again--even though I know the login exists on production because the application was able to login originally.

Here's the chain of events:

Production + Mirror have same login
Fail over to Mirror, login rejected.  Starts working for some mysterious reason.
Mirror fails over to original production, login that was initially working now not working.


texts: Attach SQL Server 2008 R2 on SQL Server Express 2008

texts:
I have a database on SQL Server 2008 R2.

I want to attach it on a SQL Server Express 2008 instance running on Windows 2003.

I set the SQL server compatibility level on the database to SQL Server 2005 in SQL Server 2008 R2 Management Studio before detaching.

But I can't attach this database to my SQL 2008 instance running on Windows 2003 due to a "compatibility error".

How can I get around this?


texts: When possible, are clustered indexes that are declared as unique more performant than clustered indexes that aren't declared as unique?

texts: When applicable, will using the UNIQUE constraint for a clustered index help improve performance in any way under any scenario?


texts: Getting "invalid authorization specification" when creating linked server

texts:
I have two databases. Database A is on DBServer\Instance1 and Database B is on DBServer\Instance2. I need to create a view inside Database B that looks into a table in Database A. I was told I need to add DBServer\Instance1 as a linked server to DBServer\Instance2 first. That's what I'm trying to do.

Just for testing, I created a user account named linked_server_acct and gave it all the roles on both servers (Under Security > Logins). I can log in to each instance using SQL Management Studio, so I know for a fact that the login works, but I'm not able to use it for some reason when creating the linked servers. It fails with "invalid authentication specification - 7399'.

I can't think of a reason why an account that is basically a super admin of a server can't authenticate to it. Any help would be appreciated.

DBServer\Instance1 linked server script:

/****** Object:  LinkedServer [DBSERVER\INSTANCE1]    Script Date:

06/17/2015 16:17:39 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DBSERVER\INSTANCE1', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBSERVER\INSTANCE1',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBSERVER\INSTANCE1',@useself=N'True',@locallogin=N'linked_server_acct',@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO


texts: Query to get ORACLE_HOME path in Oracle 11G

texts:
Can anyone help me out with an PL/SQL query to get the ORACLE_HOME path in Oracle 11G?

I require it to be fetched from the Oracle DB only & not via the Operating system's environment variables.

PS: I'm a DB newbie.


texts: Storing logical WAL in the same database

texts:
I have a simple Postgres setup (one machine, one disk) and would like to record a changelog for some of the tables. As I would like to access that changelog from the same web app that uses the database, it would be ideal to store the row-by-row changes in Postgres as well.

It is critical that the logging doesn't slow the machine down too much during times of high load. I would like to avoid setting up triggers (unless you believe they are the best option here).

I know about Debezium and Kafka, but setting all of that up seems like a dreadful amount of complexity for what I'm trying to achieve. What is the easiest way to accomplish what I'm trying to do? I'm open to other options too.

Edit for clarification: I don't need the log entry to be written alongside the changes in one atomic transaction – I'd rather it's queued up to happen at some unspecified time later. If some timestamps are wrong or (worst case) a log entry is missing, it's not the end of the world in my situation; write performance is more important.


texts:
Just started out with MySQL 5.5 on Ubuntu Server 14.04 and ran into a strange issue.

I always show the settings of the variables by typing mysql --help.
Just to get familiar with MySQL-Server I wanted to change local-infile to true. So I went to my.conf of MySQL and under mysqld I added local-infile = true.

After restarting MySQL-Server I typed again mysql --help getting:

local-infile                      FALSE

By changing the client and server port from 3306 to 3305 I checked if the output of mysql --help is just a list of default setting but it is not.
The list now showed that the server is listening on 3305.

So I went into mysql typing

SHOW GLOBAL VARIABLES LIKE 'local_infile'

getting following output

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

So why is the variable output inconsistent?

I hope this question is related to the database mysql and database administrators is the right place to ask this kind of question. I hope it is not directly related with Ubuntu. If so please bear with me.


texts: PostgreSQL 11 and pgagent connection string issue

texts:
On PostgreSQL 11 running on Windows 2012 server, I created a job with pgagent and pgAdmin4.
In the step, in connection string I used the password parameter and it works.
Instead, I would like to use passfile parameter with a pgpass.conf, located in C:\Users\POSTGIS\AppData\Roaming\postgresql\according to the doc
I tried many ways ton write the passfile unsuccessfully.
For example I wrote :
host=localhost port=5432 user=POSTGIS passfile=C:\\Users\\POSTGIS\\AppData\\Roaming\\postgresql\\pgpass.conf dbname=xxxx
...with single or double backslashs or slashs.
But I always have a Please enter a valid connection string message.I am out of ideas.


texts: Can filtered indexes help improve queries that are based on a time entered or should this be avoided?

texts:
I'm just learning about the new filtered indexes in MS SQL 2008 and I'm trying to understand where they would hurt and where they would help.

I can see that adding a filtered index to an employees table to index only the current employees would be a good idea.

But what about applying a couple filtered indexes to a large table based on time. For example, say I have a table that has an enteredOn date/time field and the table has many years worth of data. If queries are typically done based on date and some other field such as enteredby would it be good to have a few filtered indexes that have a where clause limiting the index to a timespan? In my example the query would be something like

select enteredOn, Description, ...
from myTable
where enteredOn > '2011/01/01' and enteredBy = "My, User"

Would having an filtered index for each year be reasonable or would that cause to much of a problem when the below was queried for instead?

select enteredOn, Description, ...
from myTable
where enteredOn > '2009/06/01' and eneteredOn < '2010/06/01' and enteredBy = "My, User"


texts: How expensive is it to rename column name on a big table?

texts: Say we have table in an Oracle DB with millions of rows - how expensive is it rename it? My guess (and hope) is that it's just a metadata change, so the size of the table has no impact, but I'd like to verify (couldn't find any useful results).


texts: How to prevent a highly dynamic PL-SQL query from injection using bind variables

texts:
I have a procedure as you can see below:
create or replace procedure Injection_test(qry1         varchar2,
                                           qry2         varchar2,
                                           user_id      varchar2)
is
   final_query varchar2(1000) := '';
begin

  if (qry1 is not null) then
     final_query := 'select c_num from z_test_a where userid = ''' ||user_id || ''' and ' || qry1;
   if (qry2 is not null) then
     final_query := final_query || 'intersect  ';
   end if;
  end if;

 if (qry2 is not null) then
  final_query := final_query || ' select c_num from z_test_b where userid = ''' ||user_id || ''' and ' || qry2;
 end if;

--dbms_output.put_line(final_query);
  execute immediate 'insert into Z_final_result (c_num)'||final_query;
 commit;
end;

As you can see , the variable final_query is highly dynamic based on two input variablesqry1 and qry2. Since I'm new to Oracle pl-sql injection , I don't exactly know how to prevent this piece of code from sql injection . I know the basic concepts of injection and how it occurs
-for example when we have null or 1=1 for one of the input variables,all the records of the table will be inserted in the final table- but I do not know exactly how to change this procedure and usebind variables to prevent injection from happening.
I was wondering if you could help me out with this
Thanks in advance


texts:
My scenario is a shop/warehouse and I have a Product table which contains basic product details including it's name, price, quantity on hand etc. I have a Customer table that has the shops customers and their details. And then I have a table that is confusing me: the Despatch table. The business rules for the Despatch table are as follows:

  Its purpose is to assist the warehouse staff in quickly locating
  products that are needed for the daily delivery run to customers. It
  contains for each product the number of units needed for delivery. The
  warehouse shelf location is also stored to facilitate quick retrieval
  of the product. Thus the table stores product name, shelf location and
  the number of units required for dispatch. Note that the product name
  is stored instead of a product code as the name is much more
  meaningful to the warehouse staff than a cryptic code. In order to
  optimise performance the table is preloaded with the product name and
  shelf location for each product.

Dispatch table

Disp_id     Number(4)
Prod_name   Varchar2(30)
ShelfLoc    Varchar2(6)
Quantity    Number(5)

Then finally I have a Sales table which holds all the sales transactions in the shop and is the bridge table between the Product, Customer and Dispatch table.

My confusion with the Dispatch table is that logically it seems like it would behave as though there's a one-to-many relationship between Product and Dispatch tables, but the business rules make it seem like it's a one-to-one as it asks me to preload all the products even if there are no deliveries/dispatches to be done? I guess I don't fully understand the concept of 'dispatch'.


texts: How does shrinking a SQL Server log file affect performance?

texts:
I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is no longer available with 2008 and later databases.

I do have a script that truncates the log file:

USE [MyDatabase]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC shrinkfile('MyDatabase_log', 1)
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT
GO

This truncates the log file completely, but my question is: Does this affect performance?

I perform two Full backups daily so the log should not really be necessary as far as data roll-forward is concerned.


texts: Error when restore database using dialog - SQL Server 2016

texts:
I'm trying to restore backups for a DB, but every time I select the files, like this:

As soon as I click on "Options" or on "Script" (not even to execute the actual restore!), I get the following error:

"An exception occurred while executing a Transact-SQL statement or batch".

Any ideas of what can be happening? If I select only the Full Backup, it works, but it doesn't work if I select all the Transaction Logs.

It's also weird that the backups are not sorted by date & time. If I recall correctly it used to let you even select the exact point in time for recovery.

Am I doing something wrong? Does that mean a backup might be corrupted (if so, how do I check that)?

All backups are performed using Hallengren's script and are checked, verified and compressed.

UPDATE:

I did a Profiler and saw that the error happens when this statement is executed: RESTORE FILELISTONLY FROM  DISK= '....

And the next error is displayed:

Msg 3231, Level 16, State 1, Line 1
The media loaded on "C:\restore-temp\SQL02_Database_FULL_20180827_051921.bak" is formatted to support 1 media families, but 11 media families are expected according to the backup device specification.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

UPDATE 2: (PARTIAL SOLUTION)

It works if I restore the files one by one. So it's not a problem with the files, but rather with the SSMS. For some reason I cannot select multiple files in the Restore dialog. It used to work, not sure what changed..


texts: How to read an excel file from a network machine using SQL?

texts: I am trying to import some excel file data into a SQL table. I thought of using OPENROWSET for this. The issue is that the csv file is location in a different machine in the same network. How do I read this excel file? If this cannot be done using OPENROWSET, are there any other alternatives? Please advice.Please note that I cannot go with Linked servers as I don't have the privileges to client's machine to install relevant drivers.


texts: Why does PostgreSQL ignore auto_explain.log_analyze = 0 in is postgresql.conf file and how can I turn on auto_explain for all sessions?

texts:
I have a strange problem with PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180303 (Red Hat 7.3.1-5), 64-bit (that was the complete output from select version();).
My problem is I want to turn on the auto_explain to chase down some problem with triggers that aren't firing. I need this auto explain to give me all the details for a short time to debug this. But not just in my session, I need it to be in all sessions that all application servers access.
I am showing you the problem right here:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_nested_statements = true
auto_explain.log_triggers = true
auto_explain.log_timing = false

it doesn't do anything unless I set in my session
SET auto_explain.log_min_duration = 0

then it will log the auto explain for that session. But all the application server sessions continue mute.
This is some sort of bug I found. Here is how I know it.
If I put
manu_confuse = foo

into the postgresql.conf file, then no error is thrown.
If I put
auto_explain.foo = bar

into the postgresql.conf file, and not the shared_preload_libraries, then only when I say
LOAD 'auto_explain';

will it tell me that there is no auto_explain.foo property to set.
However, it simply ignores the auto_explain.log_min_duration parameter, no matter what value I give it.
For example, if I say in the .conf file;
auto_explain.log_analyze = 'foobar'

it will complain that the value to log_analyze must be a Boolean.
But when I say in the .conf file
auto_explain.log_min_duration = 'foobar'

it will not complain in any way. It just ignores it. And no matter how often I repeat some bogus assignment of auto_explain.log_analyze, just ignored.
I find that very strange. How has been able to turn auto_explain on for all sessions by default?
PS: A Related question is this, but it is very different. I'm only referencing it because it brought me to this site to ask.


texts: Postgressql - could not fork new process for connection: Cannot allocate memory

texts:
Postgres instance restarted multiple times with out of memory errors. could not able to get what process would have consumed all memory.
2020-09-03 04:54:14 PDT LOG:  could not fork new process for connection: Cannot allocate memory
TopMemoryContext: 799496 total in 13 blocks; 63080 free (17 chunks); 736416 used
  TopTransactionContext: 8192 total in 1 blocks; 7960 free (0 chunks); 232 used
Here are the current settings. Postgres version is 10.5.12

effective_cache_size                | 16GB
maintenance_work_mem                | 1GB
max_wal_size                        | 4GB
max_worker_processes                | 8
temp_buffers                        | 8GB
work_mem                            | 256MB
shared_buffers                      | 32GB
max_connections                     | 3000

vm.overcommit_memory = 2
vm.overcommit_ratio = 80

-sh-4.2$ free -g
              total        used        free      shared  buff/cache   available
Mem:             62           9           0          36          52          16
Swap:             0           0           0

Can you please suggest if any configuration changes required.


texts: Table design for a simple messaging system (ala Facebook or whatsapp)

texts:
I'm trying to build a very simple messaging system:

  a list of conversation with shows the profile pic of my contac, the last message of that conversation and conversation not read will be
  simply displayed as 'non-read'
  when entering a conversation I get the list of message

Here is how I have started designing my database table (I use MySQL)

CREATE TABLE conversation (
  id INT NOT NULL AUTO_INCREMENT,
  user_one_id INT NOT NULL,
  user_two_id INT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_u1_u2 (user_one_id, user_two_id),
  FOREIGN KEY (user_one_id) REFERENCES user(id) ON DELETE CASCADE,
  FOREIGN KEY (user_two_id) REFERENCES user(id) ON DELETE CASCADE
);

CREATE TABLE message (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  message TEXT NOT NULL,
  conversation_id INT NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
  FOREIGN KEY (conversation_id) REFERENCES conversation(id) ON DELETE CASCADE
);

Two table, one that store the conversation and an other that list all the messages (I haven't yet included the read feature)

Using these two tables I have written this query to get the list of conversation for the userid=1

select c.id, u.nickname, u.profile_picture, m.message, m.created
        FROM user u, conversation c, message m
        LEFT JOIN message m2
        ON (m.conversation_id = m2.conversation_id AND m.id < m2.id) -- this is to select last message
        WHERE
            CASE
              WHEN c.user_one_id = 1
              THEN c.user_two_id = u.id
              WHEN c.user_two_id = 1
              THEN c.user_one_id= u.id
            END
        AND
        c.id = m.conversation_id
        AND
        (c.user_one_id = 1 OR c.user_two_id = 1)
        AND
        m2.id IS NULL ORDER BY m.id DESC;

This will display the name of the person which the messages was sent/receive from, its profile picture and the last message of that conversation. It's quite a complicated query and don't really know it will perform when I will have millions of messages and conversations..

I would like to know if my tables design can be improved (which would maybe make my SQL queries easier/simpler)?


texts: Why does my columnstore index not fill the entire rowgroup?

texts:
I'm using [vColumnstoreDensity] to monitor the health of my columnstore indexes.
I've noticed that I've got one index that looks strange.
As far as my understanding goes, the columnstore index will fill a rowgroup before starting the next rowgroup. For best index performance, fuller row groups are better.
Having said all of this, I have one table that I'm having trouble understanding. Even after an index rebuild using the largest resource class (to give the maximum amount of memory possible to the build process) the [vColumnstoreDensity] view still shows this index as being spread across many partially full rowgroups.

COMPRESSED_rowgroup_count
4936
COMPRESSED_rowgroup_rows
2693512978
COMPRESSED_rowgroup_rows_MIN
468
COMPRESSED_rowgroup_rows_MAX
739443
COMPRESSED_rowgroup_rows_AVG
545687

Edit:
This is a clustered columnstore index.


texts: COUNTING results from self-referenced table recursively

texts:
I have a table called Clients. And at this table, I have an idFather to know who added this row to the table.

Sample:

id | user    | idFather | createdAt
---+---------+----------+-----------
01 | raphael | null     | 01-06-2016
02 | jose    | 01       | 03-06-2016
03 | ryan    | 01       | 02-06-2016
04 | alyssa  | 03       | 04-06-2016
05 | laysa   | 04       | 04-06-2016
06 | karen   | 02       | 04-06-2016
07 | roger   | 03       | 04-06-2016

Now i need know how much peoples an user invited indirectly. Sample:

The user (01 - Raphael ) invited the users ( 02 and 03 ) so:
    Raphael invited 2 directly
    And user (02 - Jose) invited no one
    But user (03 - Ryan) invited (04 and 07) total 2
    And user (04 was invited by 03) invited also the user (05) total 1

So Raphael invited 2 directly ( this i did not know )
And Raphael invited 3 indirectly ( this i need know )

I i'm trying look 7 levels under the user...
i'll try explain with this tree:

EDIT:
If the users from lv1 was hard to kick out from Couting... I can do this math on my system... i just need do recursively by 7 lvs... and count just lv1 and get the difference.

I'm looking to get a query result like this:

user    | indirectInviteds
--------+-----------------
raphael | 4

I'll search just 1 user per time... i`ll not try select ALL clients and look directly.. it will explode...!!!

Thanks for the help guys!


texts: SELECT DISTINCT in extra space data from a single column ( no duplicates )

texts:
Hello i am having problems with this column data
cities
Charlotte
Cincinnati
Cleves
Columbus
Fairfield
Fresno
Los  Angeles -- HERE IS THE PROBLEM
Los Angeles
Madison
Manhatttan  Beach
New York
Orange
Palo Alto
Phoenix
Sacramento
San Francisco
Takoma  Park -- maybe this city must have problems also
Tarrytown
Valencia
Washington

How i can filter that value, what is the easy way with trim? i did my research but i find long sql statements that i dont understand if that could help is a simple error.
This is the query:
-- 3. Write a query that will list all the cities that have customers with a heading of Cities. Only
-- list each city once (no duplicates) and sort in descending alphabetical order.

select distinct customer_city as cities
FROM customers
ORDER BY customer_city ASC

-- left Los  Angeles


texts: What are typical scenarios where the user might require a data-change-audit-trail?


texts: How to monitor query load, counts, and durations in PostgreSQL

texts:
I'd like to understand load (average active), count, and, average time of various queries.

This is an AWS RDS Performance Insights dashboard. This is the sort of information that I want, though as you can see, it's half broken. (Issue has been escalated to technical support, so likely I'll hear back in mid-November.)

Queries can get long, say up to a couple thousand characters.

Are there any good ways to get this usage/performance information? This would seem essential to improving real-world DB performance but so far I haven't turned up much.


texts: Transaction independent writes inside a trigger

texts:
I am using sql express 2008 r2 express edition.

Is it possible to do writes inside a trigger independent from the the transaction of the statement which causes the trigger to fire?

I have triggers working for simple auditing on some tables in my DB. I have one table for keeping the auditing data (Look here for details) It works fine except i don't want the auditing data be vanished when the transaction rolledback. So I want to write audit data to audit table independent from the transaction of the statement that causes my auditing trigger to fire.


texts: What is the data type 'name' in PostgreSQL

texts:
I can not find any references to the data type 'name' in the postgres documentation but I am seeing it as a data type on the pgagent.pga_jobstep  table for the column 'jstdbname'. The udt_name is also 'name'. Selecting the rows from that table makes them appear as if they are strings.

It is not listed here: Postgres data types


texts: Handling SQL updates for production deployments

texts:
I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment.

I run a pretty traditional LAMP web application in the cloud. The source code is VCS'ed with Git, as well as database schema (split SQL files for structure and default data).

Deploying a new environment is quite a joy, development or production. Dev environments are deployed with Vagrant plus some nice shell provisioning script I wrote. Production environments are deployed using a subset of the Vagrant provisioning script, which is also very easy to do.

When the development cycle imposes a change to the SQL schema (structure or data), things starts to break. In that case, developers changes the core SQL scripts so all the changes gets into the VCS, which is totally fine for brand new environments.

The problem sits within already deployed production environments: for those, I have to manually get all the patches for the SQL changes since the revision actually running in all production servers first so I can apply them manually after. This takes a long time to happen, is a quite fragile task and error prone.

Initially, I thought I can move the SQL changes away from the core SQL files smaller subsets, letting the core SQL files as a starting point. When there is something to change at SQL structure, I could just tell devs to create a new SQL file with only the changes from that development cycle.

So, for instance:

- structure_begin.sql: the SQL schema as it is now.
|- structure_devcycle1.sql: first set of changes to the structure
|- structure_devcycle2.sql: second set of changes to the structure, and so forth...

Then, by using Git hooks, I can selectively and automatically deploy them on production. But I don't know if this is a good approach. And here I can ask:

Does somebody out there have solved this puzzle?
What are the best practices for release management and deploy automation for SQL changes in production environments?
Are there any open source tools that can help in this process? (I'm aware of Sqitch, but I'm not quite sure if it is the right one for the job.)


texts:
I've setup the profiler to catch these deadlock events:

Deadlock graph
Lock: Deadlock
Lock: Deadlock Chain

Al available columns are ticked (TextData, LoginName, SPID, etc.)
So far, I was able to catch some deadlocks.
However, sometimes it happens that I get only a list of deadlock chains (like 30 events in a few seconds), but then no deadlock and no deadlock graph is caught.
What does such a case mean? Did a deadlock occur or not? Or was it like "deadlock was around the corner" but could be resolved?


texts: How to join the latest previous record with SQL

texts:
I have a table whose schema is like this:

CREATE TABLE product_shipping(
    product_id CHAR(10),
    product_name CHAR(10),
    deal_dt DATETIME,
    deal_reason_no SMALLINT,
    cancel_dt DATETIME,
    cancel_reason_no SMALLINT
);

Once the shipping date is arranged, the deal_dt and deal_reason_no will be set and cancel_dt  and cancel_reason_no is null. However product_id is not unique in the table since the every product shipping can be cancelled. Once cancelled cancel_dt and cancel_reason_no will be set. If the cancelled product is bought by other customer then the shipping is recorded in the new row, and product_name can be changed in the new shipping.

I want to query the product_id and deal_dt whose deal_reason_no is 1, 3 or 5 and deal_dt is between '2014-04-01 00:00:00' and '2014-04-11 00:00:00' and the latest previous deal_dt and latest  previous cancel_dt whose cancel_reason_no is 2 or 4 for the product_id.

I think the SQL could be like this:

SELECT
C.product_id as product_id,
C.deal_dt as deal_dt,
R.deal_dt as previous_deal_dt,
R.cancel_dt as previous_cancel_dt,
FROM product_shipping C
LEFT JOIN product_shipping R ON (???)
WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5')
AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00'

But I'm not sure how to join to get latest previous record.

I'm using Informix 11.70.

Edit: Add my idea. Is this reasonable?

SELECT
C.product_id as product_id,
C.deal_dt as deal_dt,
MAX(R.deal_dt) as previous_deal_dt,
MAX(R.cancel_dt) as previous_cancel_dt,
FROM product_shipping C
LEFT JOIN product_shipping R ON (C.product_id = R.product_id and TO_CHAR(R.cancel_reason_no) MATCHES ('2', '4') and R.cancel_dt <= C.deal_dt)
WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5')
AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00'
GROUP BY 1,2


texts: Secondary replica database file location in Availability Group

texts:
I inherit a setup of two nodes (primary & secondary replica) Availability Group.
On my secondary replica node, the SQL server instance default files are stored at:

My default primary replica node  files are as follow:

When I look at the synchronizing database's properties on my secondary replica, I see its database files as follow

They are primary replica node directories.  Is this how it works in Availability Group?
That is when you look at the file group of your secondary replica database, they show the paths of the primary replica database even though the data file and log file are stored at a different local directory?


texts: Putting SQL Server data files in a different partition

texts:
I have a SQL Server and I put the data files (the .mdf) on a different partition.

Windows runs from C: and the data files and logs are on D:

Is it advantageous or disadvantageous to separate the data files on different partitions?

There is only one disk and it has been partitioned to C: and D: My main focus is SQL Server performance all around like reading and writing.


texts: Should a meta_key or other key-value table columns use a HASH or BTREE index?

texts:
I've noticed that my WordPress database has BTREE indexes everywhere.
By my understanding, BTREE indexes allow more efficient < > and BETWEEN queries, but are otherwise less efficient than a HASH query for = and != queries.
This makes sense for some columns that might be filtered numerically (dates obviously, and IDs potentially, – though in practice ID > x doesn't seem very likely in WordPress), but I can't see any benefit for columns like meta_key which stores the text key of a key-value pair, or indeed the post_id column of a post_metadata table, which will almost without exception be JOINed to the posts table.
Would a HASH index be more performant? If so, is there any reason that my database is using BTREE indexes for these columns, or is this just a default? If not, what am I lacking in my understanding of indexes?


texts: Is there a way to actually limit InnoDB data dictionary?

texts:
I would like downgrade AWS instance to save the costs however due to the number of tables and workload (logical backups) InnoDB data dictionary grows extremely large.

mysql> SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES;
+----------+
| COUNT(*) |
+----------+
|  1020034 |
+----------+
1 row in set (4.61 sec)

mysql> SELECT COUNT(*) FROM information_schema.INNODB_SYS_INDEXES;
+----------+
| COUNT(*) |
+----------+
|  2628181 |
+----------+
1 row in set (4.99 sec)

-

mysql> SHOW GLOBAL VARIABLES LIKE 'table_%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 400   |
| table_open_cache           | 1     |
| table_open_cache_instances | 1     |
+----------------------------+-------+
3 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Open%table%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Open_table_definitions   | 400      |
| Open_tables              | 1        |
| Opened_table_definitions | 2312885  |
| Opened_tables            | 22403462 |
+--------------------------+----------+
4 rows in set (0.00 sec)

-

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_mem_dictionary';
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| Innodb_mem_dictionary | 4517841711 |
+-----------------------+------------+
1 row in set (0.00 sec)

table_definition_cache

  For InnoDB, table_definition_cache acts as a soft limit for the number
  of open table instances in the InnoDB data dictionary cache. If the
  number of open table instances exceeds the table_definition_cache
  setting, the LRU mechanism begins to mark table instances for eviction
  and eventually removes them from the data dictionary cache.

Any idea why this is not happening? I was wondering if enebling innodb_file_per_table would help in this case.

Some extra info:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 8589934592 |
+-------------------------+------------+
1 row in set (0.00 sec)

mysql> SELECT @@version, @@version_comment;
+--------------------+---------------------------------------------------------------------------------------------------+
| @@version          | @@version_comment                                                                                |
+--------------------+---------------------------------------------------------------------------------------------------+
| 5.6.30-76.3-56-log | Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 |
+--------------------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-

# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)

# free -m
              total        used        free      shared  buff/cache   available
Mem:          14881       14239         326           6         315         378
Swap:          4095        1237        2858

# ps -o %mem,rss,vsize -C mysqld
%MEM   RSS    VSZ
94.2 14364148 16262404


texts: How to manipulate join order of PostgreSQL optimizer?

texts:
I am working on table join order optimization.

While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL?

How can I update table join order while query processing?


texts: Comparing duplicates and choosing the duplicate with most recent date in a join

texts:
Assume i have table A:

id:    name:    timestamp:
1      Bob      2018-05-31 04:46:37
2      Alice    2019-01-31 07:13:56
1      Bob      2019-01-15 18:23:74

And i want to join with another table B:

id:    name:    Preference:
1      Bob      Car
2      Alice    Bike

ID alone isnt a primary key. however ID + timestamp IS an unique key. However, how would i make to only join the 1 part of the duplicate, that has a the newest date? So that i would get a joined table like:

id:    name:    Preference:    Timestamp:
1      Bob      Car            2019-01-15 18:23:34
2      Alice    Bike           2019-01-31 07:13:56

By if i have duplicates, then compare the date between the duplicates and choose the newst


texts: How to get status start_date and end_date from status_track table?


texts: How bigger page size effect at performance?

texts:
I know in case of Cluster index bigger page size improve peformance for range and multipoint query.

Does bigger page size can effect to degrade performance when we have non cluster index .Because normally rows are not existed near each other. In this situation, big page size read more rows which it does not need to read .

Same case for  Write opeartion (insert and update) .Normally we need to update and insert sigle row .

Does one IO take same time for 4K and 16 KB ?


texts: Listener Port 1433 for Always on Availability Group

texts: In our Availability group I have a reporting server where report writers will connect directly and also crystal reports will be used, If the listener port is 1433 and the clients also gets connected through port 1433 to the reporting server that is part of the Availability group will there be any port conflict.


texts:
I'm working on a custom application that needs to determine changes to a particular column, on particular rows in our database (MS SQL 2008R2).

When the row is created a created & modify timestamp are inserted. Anytime any of the columns change, the modified timestamp is updated.

I' need to be able to fetch all the records where one particular column was changed between the time my application last ran, and now. Further more, i really only care if this one particular column value changed from a "N" to a "Y".

I want to do all this, without altering the DB schema, or using triggers due to their overhead.

My research led me to Change Data Capture, would this be a good use case to use Change Data Capture?

Is it possible to limit the overhead on CDC to an item only changing to a specific value, my table has approx ~200k rows, with 300mb in index and 200mb in data, and rows are heavily modified, so i'm concerned about the amount of data that will be captured.

The only alternate approach that i am aware of, is to capture the parent event (row created) in a separate audit table, and then compare it to the original table each time the application runs to surmise the change, is there an advantage to doing it this, or is there any simpler way to do it?


texts: SQL Server Profiler 2005 on remote SQL Server 2008 Express?

texts:
Is it able to trace remote SQL Server 2008 using SQL Profiler 2005? If yes, anyone knows what else should I do, if during loging in appeares this message "Unable to locate trace definition file for microsoft sql version 10.0.0"?

I tried to go on by this link (http://blogs.msdn.com/b/sqlserverfaq/archive/2011/01/14/unable-to-read-trace-definition-file-microsoft-sql-server-tracedefinition-n-n1-n2-xml.aspx), but my SQLPath looks good. Any suggestions?


texts: Database design for a simple association

texts:
I'm not so good in db/tables design, I'm mainly a frontend web developer, but sometimes I do really small apps. Always I find myself wondering if my thinkings are correct.. So I try to ask here :)

The db is for an app for helping a local association to manage its associates and their annual cards/subscriptions. Basically:

Once associated, the person is an associate forever (no need to renew every year)
Still, the associated needs an annual card to participate to the association's activities. So he/she needs to eventually renew his/her subscription to the activities, yearly. It's not mandatory, though: he's not forced to renew every year. He can be associated without having the current year subscription/card.

So, one associate can have many cards (one per year). One Card can have only one Associate.

I imagined just 2 tables:

Associates
   id
   name
   surname
   etc...

Cards
   associate_id
   year

It should be enough. But, should I maybe instead use a Year table? I'm not really sure this would be any useful in this specific case. Also I would then have a many-many relation between years and associates, so I guess I would need a third table (YearAssociate).

What do you think?


texts:
Our SQL Server 2000 database .mdf file is 27Gb large which seems much larger than is plausible. Using the following query we tried to analyse table sizes:

select cast(object_name(id) as varchar(50)) AS name,
    sum(CASE WHEN indid<2 THEN rows END) AS rows,
    sum(reserved)*8 AS reserved,
    sum(dpages)*8 AS data,
    sum(used-dpages)*8 AS index_size,
    sum(reserved-used)*8 AS unused
from sysindexes with (nolock)
    where indid in(0,1,255) and id>100
    GROUP BY id with rollup
    ORDER BY sum(reserved)*8 desc

The results were as follows:

Name           Rows       Reserved  Data     Index_Size  Unused
NULL           15274279   26645456  5674592  17361464    3609400
BigTable         875966   16789712   471096  13349816    2968800

How can we find out which objects are causing this massive NULL space usage?
It seems that approx 26GB are "reserved" for NULL, 16GB for BigTable - is this basically a waste of space or are real records involved?


texts:
Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column.

This seems like the most obvious approach. However, alarm bells are ringing in my head because I am using a VARCHAR column for a primary key, which might slow down operations when querying the table.

I could introduce a third column called cat_id INT AUTO_INCREMENT and set that to be the PK, but it would introduce a new column which have no meaning.

Apart from which one would be faster, what other considerations should be taken?

N.B. I predict there will be at most 1000 categories or so so the number of rows are not very high. However, the categories PK column will be a reference column for many foreign keys, in other tables.

Should I use (unique) usernames as PK's also?


texts: Filtering previous month data with case statement

texts:
These are my available attributes:

DT_CURRENT_DT Month Year
11-OCT-18     10    2018
12-OCT-18     10    2018
13-OCT-18     10    2018
14-OCT-18     10    2018
15-OCT-18     10    2018
16-OCT-18     10    2018
17-OCT-18     10    2018

I need a logic that will pull last month data based on sysdate.

where DT_CURRENT_DT >=  trunc (sysdate, 'MM') - interval '1' month
and DT_CURRENT_DT <   trunc (sysdate, 'MM')

However when sysdate = November 2018 then data must be pulled from October 15th till end of October. For other months it must behave as code above.

UPDATE (NOV 11) - CASE code version:

WHERE  DT_CURRENT_DT >= CASE
                       WHEN extract ( month from trunc (sysdate, 'MM')) = 11 and extract ( year from trunc (sysdate, 'MM')) = 2018
                       THEN to_date('15-OCT-2018')
                       ELSE  trunc(sysdate, 'MM') - interval '1' month
                       END
AND DT_CURRENT_DT <   trunc (sysdate, 'MM')


texts: What does [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'WIN-443PT0LULQU\Administrator', error code 0x534

texts:
I'm going slightly nuts.  I'm a programmer by trade - and I only dabble in NT security and DB setup issues.

However, we had a server fail to reboot after a MS Update.  I've restored the entire machine from Windows Backup - but I've realized that I cannot rebuild the machine because one cannot realistically use a Windows Backup to restore a database to a different machine, AFAICT.  Rather, I need actual DB backups to do that.

In pursuit of having regular SQL Server backups, I'm running into the title error:

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'WIN-443PT0LULQU\Administrator', error code 0x534

Essentially, I've enabled SQL Server Agent viz SSMS, then I created a management plan to do full backups on our SQL server instance, and scheduled it for daily operations.

However, when I manually ask it to execute that job - to ensure all is working correctly - I get the error:

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'WIN-443PT0LULQU\Administrator', error code 0x534

That sounds to me like an NT permissions issue - that whatever it is - XP Agent? - attempts to retrieve the permissions of the aforementioned user - and that fails.

Part of what is bizarre here - is there is not machine 'WIN-443PT0LULQU' - that doesn't exist - WTH is SQL Server forcing that in there for in the first place?!

I want this to run as the Administrator - on the local machine 'BUILD-SERVER' - not that garbage name it keeps forcing...

I'm way out of my depth here and this whole process is crazy-making.

How in 2018 is it not possibly to click a few wizard NEXT style buttons and configure Windows / SQL Server to back its freakin' self up regularly correctly?


texts: Options and best practices for Multiple Result Sets in Postgres11

texts:
I want to get two (small) result sets and have the benefit of precompilation and 1 round trip to the database.  SQL Server allows a stored procedure to contain multiple select statements.

SELECT u.username, ... FROM User u WHERE u.userId = p_userID;
SELECT r.roleName, ... FROM Role r WHERE r.userId = p_userID;

When retrieving data from each result set, the client does something like:

using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        result1.Add(reader.Get<int>("username"));
        ...
    }
    reader.NextResult();
    while (reader.Read())
    {
        result2.Add(reader.Get<string>("roleName"));
        ...
    }
}

What's the recommended way to do this in PostgreSQL 11?  (I realize many variations of this question have already been asked and answered regarding multiple result sets, but I would like a concise list of options and a recommended best practice if it exists.)  Here are the options I've found so far:

Use a Function that RETURNS SETOF refcursor.
This lengthy debate is confusing and I'm not sure what the final status is on the issue.  Requires that the function call be wrapped in a transaction(?) and then execute a Fetch on each. (another link).  I'm hoping there is a more straightforward way to do this that doesn't require a transaction.
Don't use a function or procedure, just use inline sql and batch statements.
(This works with my client code, but then I don't get the advantage of precompilation.  We can't have multiple statements in a single PreparedStatement):

SELECT ... FROM TableA a; SELECT ... FROM TableB b;

Don't use cursors, create 2 functions, each returning a Table type that defines the structure of the result.
(Requires a trip to the database for each function call.)
Use the new StoreProcedure in PostgreSQL 11 that were going to support multiple result sets.
(Sounds like this feature didn't make it into version 11)
Combine the data from each result set in a join.
Gives the benefit of precompilation and 1 round trip, but it just feels gross when I consume the data in code.  In my case, I want to get user data and a list of roles for the user.  I suppose I could get data like
userA columns..., role1 columns
userA columns..., role2 columns

Which of these (or some other?) is the recommended way to get multiple result sets?  i.e., is there a way to return 2 table types in a function or procedure?


texts: How to ensure the quick switch from Log shipping to Mirror in SQL Server 2012

texts:
Need some Guidance/steps or any reference to link on how safety and quickly can I switch from Log-shipping to Mirroring as we have very small downtime to complete the activity [5-10 mins].

Also how would one ensure that the secondary LSN matches the one with primary so that mismatch might not cause a problem while setting up the mirroring as required above. Because if it does, then all the process of applying log backups will take additional time and goal above would not meet to change LS to mirror quickly.

Please suggest.


texts: Ok, so everything I read says best practice is to us NT authentication in SQL server. And I can see some advantages to that but using NT auth how do I force users to access the data using only my application and not some other tool such as SSMS? It would seem to me that it would be better to allow only my application to connect to SQL server using SQL auth and password the user does not know and controlling security through the application. Am I missing something here? Is there a way to block SSMS and other database tools from connecting to my application's database?


texts: How do I create a global MongoDB Admin account to see all Dbs?

texts:
I'm working on MongoDB, sysadmin helped setup the install.

I'm confused about MMS the Cloud Manager, I created an account for @admin database

I authenticate successfully but do not see "test" database
LinuxBox> ./mongo myipaddress:27000/admin  -u DBASupport  -p
** if I don't use /admin I default to "test" db

  show dbs;
  admin  0.078GB
  local  0.078GB
  show collections;
  system.indexes
  system.users
  system.version

I am assuming I'm using a GLOBAL DB Admin account I created.. but it looks like it's for the "Admin" database.

Alternatively when I created another account "Test" as the database login "MyTestLogin"  with Admin role...  the Database I default connect to is "Test" database?

I thought since I'm connecting to same port 27000 with same MongoDB instance/service...  I would see admin, local, and test databases??

I know i'm missing something here, but can some one explain

thanks


texts:
I need to backup a Instance of SQL Server 2016, I already have nightly backups of desired databases however I need to backup the users and roles for the instance..... How can i do this? Built in job or custom T-sql I can schedule to run?

I am not very good with SQL if anyone can point me in the right direction


texts: Why CTX_DDL.SYNC_INDEX procedure doesn't work?

texts:
I'm following the Oracle documentation to create indexes with Oracle Text. The problem is, when I update my data and want to synchronize my index with the CTX_DDL.SYNC_INDEX procedure (according to the step 4 of the documentation), there is no change.

I don't post my code because it is exactly the same as the documentation.

An idea about the source of this behavior?


texts: In mongodb, drop collections, but still in db.stats() ? In db.stats(), fileSize is greater, greater than storageSize

texts:
I used db.coll.drop() to drop two collections in my MongoDB database. There are only system.indexes in my database now. But the output of db.stats() is:

mongos> db.stats()
{
"raw" : {
    "rs0/sever01:27017,server02:27017,server03:27020" : {
        "db" : "mydb",
        "collections" : 2,
        "objects" : 1,
        "avgObjSize" : 48,
        "dataSize" : 48,
        "storageSize" : 49152,
        "numExtents" : 3,
        "indexes" : 0,
        "indexSize" : 0,
        "fileSize" : 4226809856,
        "nsSizeMB" : 16,
        "dataFileVersion" : {
            "major" : 4,
            "minor" : 5
        },
        "extentFreeList" : {
            "num" : 128,
            "totalSize" : 2680152064
        },
        "ok" : 1
    }
},
"objects" : 1,
"avgObjSize" : 48,
"dataSize" : 48,
"storageSize" : 49152,
"numExtents" : 3,
"indexes" : 0,
"indexSize" : 0,
"fileSize" : 4226809856,
"extentFreeList" : {
    "num" : 128,
    "totalSize" : 2680152064
},
"ok" : 1
}

Why is the collections number still 2? Why is fileSize is so large?

And when I run show dbs, the output shows that my database is about 4 GB.

I am not sure whether my issue is the same as the issue of dropping database here Removing Database from sharded cluster in Mongo DB.


texts: Query to filter out data based on two logic

texts:
This is a sample table:

  sample_id  |      timestamp       | p_id
============================================
    62054    |  2018-09-25 10:18:15 |  2652
    62054    |  2018-09-27 16:44:57 |  966
    62046    |  null                |  1809
    62046    |  2018-09-25 10:18:15 |  2097

We need to filter out unique sample_id column, but the logic is

IF the timestamp column is null, then return those null column data

    62046 | null | 1809

IF the timestamp column is not null, then return the latest timestamp column data

 62054 | 2018-09-27 16:44:57 | 966

So its great if anyone provide the sql query.

We need somethings like that,

WHERE
IF(
    NOT NULL = all row group by sample_id,
    row where cancelled_at is maximum,
    null column
)


texts: Choosing Between SQL Server 2008 R2 and SQL Server 2012 - Which to learn about first?

texts:
As the title suggests, I'd like to get advice on which version should I learn about first?

Most online courses now emphasize on 2012 and Microsoft no longer gives out exams on SQL Server older than 2012.

My gut feeling says go with 2012 and study the hell out of it.
However I'm currently using SQL Server 2008 R2 at work and the company has no near future plans of upgrading to 2012 yet.

What should I do?


texts: SQL insert which updates one table and based on a field value chooses what other table needs updating

texts:
DB setup: MSSQL
I have a source record coming in via API call. That call is intended to update a system in the database.
The first table is a common table where all systems have some details
SystemId   | SystemName   | Type      | Memory    | Storage  | Etc......
-----------+--------------+-----------+-----------+-----------
92         | Server1      | BlueType  | 16GB      | 10TB     |
96         | Server2      | RedType   | 24GB      | 20TB     |
...
...

Then I have at least 2 tables, each with different fields pertaining to a particular role of the system:
BlueId  | BrokerName   | Threshold | TriggerLevel  | Etc......
--------+--------------+-----------+---------------+-----------
41      | Palooza      | 400       | 4             |
42      | Almamater    | 900       | 3             |
...
...

RedId   | Parent      | MetalCast | Available  | Etc......
--------+-------------+-----------+------------+-----------
87      | Pindle      | Yes       | Yes        |
88      | Feldspar    | No        | No         |
...
...

The source that is providing the data would be submitting it in one statement. That statement would come from a source that knows what "type" it is (red, blue, etc) and would have the appropriate fields with it as it pertains to that type.
For example, if the statement is coming from a "Red" type of system, the statement would come in similar to the following:
SystemName = CrosserTT1
Type = BlueType
Memory = 20GB
Storage = 10TB
BrokerName = PintoCall
Threshold = 250
TriggerLevel = 6
etc....

This means that I need to take this data, update the "Common" table as above with the appropriate fields, determine what the value of "Type" is and based on the type value update the appropriate table with the appropriate fields. In this example above, I'd want to update the common table, and then update the Blue table based on the value of the type. I wouldn't want to even touch the red table in this case.
These are the things I'm wondering:

Is this possible to have an insert statement that in-one-shot can update common, choose which type table to update, then go update that?
Should I have a single table that contains all the fields of all the types and only add data into the columns appropriate to the type?
Should I have a single table INCLUDING COMMON that has all fields of everything system-related and leave null the fields that don't pertain to a record?

I'm trying to determine the best practice is for this kind of thing - for performance and simplicity.
In terms of the insert statement with the example above, would it make more sense to have a Stored Procedure and provide it all the values instead?
Thanks for your valued input.
If you need clarification, please let me know.


texts: Sort all monthly incomes and group by year

texts:
I want to show all my monthly incomes and group them yearly, like in the picture below.
The query is like

SELECT
monthname(date_added) as monthno,
year(date_added) as year,
SUM(amount) as amounts
FROM incomes GROUP BY year ORDER BY monthno

but it groups yearly and hides months of the year:

screenshot


texts: Backing up a MongoDB cluster with mongodump?

texts:
Can anyone indicate the right way to backup a MongoDB database with mongodump?

I have tried:

mongodump --host "cluster0-shard-00-00-2djno.mongodb.net:27017,cluster0-shard-00-01-2djno.mongodb.net:27017,cluster0-shard-00-02-2djno.mongodb.net:27017" \
  --authenticationDatabase admin --ssl --username admin \
  --password mypassword -o /tmp

When I do that I get:

Failed: error connecting to db server: no reachable servers

Verifying connection to an individual node with both the ping and telnet commands such that these nodes are valid.

The mongodump version is 3.2.13.


texts: Oracle Database Server TNS Listener Remote Registration Vulnerability (CVE-2012-1675)

texts:
Some of our Internal DBs Oracle installed in Linux and Windows servers have been highlighted with the issue Oracle TNS Listener Remote Poisoning (CVE-2012-1675).

  This security alert addresses the security issue CVE-2012-1675, a vulnerability in the TNS listener which has been recently disclosed as "TNS Listener Poison Attack" affecting the Oracle Database Server. This vulnerability may be remotely exploitable without authentication, i.e. it may be exploited over a network without the need for a username and password. A remote user can exploit this vulnerability to impact the confidentiality, integrity and availability of systems that do not have recommended solution applied.

All our DBs are 11.2.0.4, but reading all the notes from Oracle or third parts seems that this Oracle's version is not affected.

Anyway,Following the Doc ID 1600630.1 from Oracle Support, I made a change in the listener.ora adding the below string:

VALID_NODE_CHECKING_REGISTRATION_listener_name = ON

We are using NessusScan to highlight the Vulnerabilities on our servers. After i made the change above, we ran the scan and there is still one server highlighted with this error:

  The remote Oracle TNS listener allows service registration from a
  remote host. An attacker can exploit this issue to divert data from a
  legitimate database server or client to an attacker-specified system.

  Successful exploits will allow the attacker to manipulate database
  instances, potentially facilitating man-in-the-middle, session-
  hijacking, or denial of service attacks on a legitimate database
  server.

Checking the server and Oracle Listener there is nothing wrong or any errors, it just come up with the Scan with the above error.
Trying to fix this issue, I found this page:

https://community.oracle.com/thread/4008690?start=0&tstart=0

but mine is not a XE version, my version is:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Please, if anyone had to face this issue with Oracle 11.2.0.4, could share how it has been fixed would be great.


texts: SQL Server 2012: An older Index that used to help a SP overnight

texts:
Hopefully this isn't a repeat question. I haven't had much luck finding an existing answer.

I have an index on a somewhat large table (About 60 million rows, 25gbs with indexes) that has been in existence, unchanged for about 2-3 years and a stored procedure that is also untouched for about 2-3 years that relies on this index. The SQL server that both reside on has its statistics updated at 5AM each morning. This server is a subscriber to another DB that also has the same SP, table, and index. The table is replicated to the server in question.

Literally overnight this stored procedure went from running for under a second to running up to 45 minutes causing CPU to spike to 100%. On the publisher server, this same stored procedure uses the identical table + index still under a second when executed.

While I can't get an actual execution plan, I can see the estimated and the index seek estimated a CPU cost of like 200,000 instead of the usual 7-9.

I tried updating statistics on the table (despite the morning job) with fullscan. I've tried dropping and recreating the index.

Ultimately, dropping this index caused the stored procedure to use a new plan which is much faster (done in seconds), but the actual execution plan of course is asking for the index that I dropped.

Any ideas, in general, what would make a plan and/or index spike CPU like this so suddenly?

Thanks

EDIT:

Link to High CPU Plan

Link to Low CPU Plan

ALTER Procedure Schema1.Object1

@ID char(3) = ?

AS
    SET NOCOUNT ON
        DECLARE Variable1 integer

SELECT Variable1 = Column1 FROM Object2 WHERE Column2 = Variable2

IF Variable1 IS NOT NULL AND Variable1 > ?
  BEGIN

SELECT DISTINCT Object3.Column3 AS Column4

, Object3.Column5
   , Object3.Column6
   ,(Object3.Column5 + ? + Object3.Column6) AS Column7
   FROM Object4
   JOIN Object3 ON Object3.Column3 = Object4.Column3
   WHERE Object4.Column1 = Variable1
        AND Object4.Column8 = ?

UNION SELECT
   DISTINCT Object3.Column3 AS Column4
   , Object3.Column5
   , Object3.Column6
   ,(Object3.Column5 + ? + Object3.Column6) AS Column7
   FROM Object5 Object6
   INNER JOIN Object7 Object8 ON Object8.Column9 = Object6.Column9
   INNER JOIN Object3 ON Object3.Column3 = Object6.Column10
   WHERE Object6.Column11 = ?
   AND Object6.Column8 = ? AND Object8.Column8 = ?
   AND Object8.Column1 = Variable1

UNION select ?, ?, ?, ?
  ORDER BY Object3.Column5, Object3.Column6

END


texts: MySQL: Ignore a Row in a table of a particular Database

texts:
I had replication in place between 3 databases, I am trying to ignore a Row in a table of a particular Database. I tried many ways but i couldn't, and also i couldn't find anything in online that helps me all i see is how to ignore a table in replication but not a row of a table.
please someone help me regarding.

Thanks in advance


texts: can I replace nulls in a PIVOT with zeroes?

texts: I am using the PIVOT function in Oracle and am curious if I can replace the null values with zeroes?  I know I can wrap the entire query in another SELECT and then use COALESCE on the values, but I am curious if there is a shortcut.


texts:
We are currently migrating from DB2 to SQL Server. DB2 supports TIMESTAMP(6-12) while DATETIME2 only supports up to 7 decimals. Since we have many keys represented as TIMESTAMP(12) what would be the best way of migrating these to SQL Server?

I was thinking we should store them as varchar(32) or char(32). This will allow storing all 12 fraction digits.

I assume order by ascending/descending will work the same with varchar/char as a datetime2.

I assume all SQL in the application can remain the same as datetime2 / timestamp values are represented as strings in SQL.

We will however have issues using time functions in SQL Server unless we cast back and forth (which probably won't perform well).

Any better suggestion?


texts:
I'm receiving an LSAlert that my log ship database hasn't been updated.  During digging I found that the log_shipping_primary_secondaries references the proper name of the secondary server, while log_shipping_monitor_secondary has a secondary server name the server had prior to it getting its new production name. (Essentially we spun up the box, migrated everything over then renamed the box to take the place of our production server.  the secondary server the log_shipping_monitor_secondary is referencing is this servers temporary name)
I've attempted changing the name on that table but it still thinks the databases haven't been updated recently. Running the sp_refresh_log_shipping_monitor did not appear to fix the issue either.
I've checked the backup, copy, and restore jobs and they all appear to be running correctly.  It is only the Monitor on the primary that appears to have the issue.
What do I need to do to get the monitor to recognize the new server name and the new restores.
I'd like to avoid resetting up log shipping if at all possible.


texts:
I have a SQL Server 2012 DB with a column that includes Arabic text.  The column is nvarchar(MAX) with collation Arabic_CI_AI_KS_WS.  To test if LIKE searches are working I copied some of the text from the column and pasted it into a query as follows

select * from MyTable where MyColumn like N'%arabic_text%'

I can visually see the text in the column, but the query returns no results.  Searching for latin characters in the column works fine.

What else can I try?


texts:
I am trying to migrate Microsoft SQL Server databases from on-premise to an RDS instance. I have created a SQL Server 2014 Express Edition instance in RDS and I have SQL Server 2014 Express Edition on premise in in my corporate network. I am able to connect to the RDS instance from my on premise SSMS. While using the DMS service to migrate and creating endpoints, I am getting below error.

  Error Details:
  [errType=ERROR_RESPONSE, status=1022506, errMessage=Failed to connect Network error has occurred, errDetails= RetCode: SQL_ERROR SqlState: HYT00 NativeError: 0 Message: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired ODBC general error.]]

I am getting the same error even when I am testing the target endpoint which is an RDS instance. I have kept RDS and replication instance on the same VPC. Port 1433 is open in my system and as I am able to connect to the RDS instance via SSMS so there shouldn't be a port issue.

Please help.


texts: Mysql speed issues with SHOW COLUMNS or SHOW FIELDS

texts:
To avoid making code changes every time we have a database change, we just use the SHOW FIELDS (Alias of SHOW COLUMNS) to get all of the columns for Inserts. We call this multiple times for multiple tables in various scripts.

Recently we have discovered speed issues so we started to look at the temp table disk report and found that the SHOW FIELDS FROM 'sometable' all had 100% hits to the disk temp table.

I've tried turning up our query cache size (yes it is enabled), and I've disabled the meta calculations 'SET GLOBAL innodb_stats_on_metadata = OFF;'

We are still getting 100% disk temp table hits on all of these show queries. Is there an easy way to fix these without a code change? We would like to still do our inserts without updating the columns in the code, and the SHOW FIELDS call is part of a framework and we would like to avoid rewriting it if possible.

Thanks


texts: How to use barman to backup PostgreSQL database correctly?

texts:
I am using PostgreSQL 10 now. Installed newest Barman on RHEL 7.

When I run this command by barman user:

-bash-4.2$ barman receive-wal pg
Starting receive-wal for server pg
ERROR: ArchiverFailure:pg_receivexlog not present in $PATH

Official Introduction

WAL streaming

Barman can reduce the Recovery Point Objective (RPO) by allowing users to add continuous WAL streaming from a PostgreSQL server, on top of the standard archive_command strategy.

Barman relies on pg_receivewal, a utility that has been available from PostgreSQL 9.2 which exploits the native streaming replication protocol and continuously receives transaction logs from a PostgreSQL server (master or standby). Prior to PostgreSQL 10, pg_receivewal was named pg_receivexlog.

IMPORTANT: Barman requires that pg_receivewal is installed on the same server. For PostgreSQL 9.2 servers, you need pg_receivexlog of version 9.2 installed alongside Barman. For PostgreSQL 9.3 and above, it is recommended to install the latest available version of pg_receivewal, as it is back compatible. Otherwise, users can install multiple versions of pg_receivewal/pg_receivexlog on the Barman server and properly point to the specific version for a server, using the path_prefix option in the configuration file.

In order to enable streaming of transaction logs, you need to:

setup a streaming connection as previously described
set the streaming_archiver option to on
The cron command, if the aforementioned requirements are met, transparently manages log streaming through the execution of the receive-wal command. This is the recommended scenario.

However, users can manually execute the receive-wal command:
barman receive-wal <server_name>

It said:

Prior to PostgreSQL 10, pg_receivewal was named pg_receivexlog.

Why it's version 10, got pg_receivexlog error?

Check Backup

When I check backup, got:

-bash-4.2$ barman check pg
Server pg:
    WAL archive: FAILED (please make sure WAL shipping is setup)
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
    replication slot: FAILED (slot 'barman' not initialised: is 'receive-wal' running?)
    directories: OK
    retention policy settings: OK
    backup maximum age: OK (no last_backup_maximum_age provided)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: OK (have 0 backups, expected at least 0)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    systemid coherence: OK (no system Id stored on disk)
    pg_receivexlog: FAILED
    pg_receivexlog compatible: FAILED (PostgreSQL version: 10.12, pg_receivexlog version: None)
    receive-wal running: FAILED (See the Barman log file for more details)
    archiver errors: OK

How to fix the failed items?

Edit

When I check pg_receivewal by postgres user:

$ sudo su - postgres
-bash-4.2$ pg_receivewal
-bash: pg_receivewal: command not found
-bash-4.2$ psql
psql (9.2.24, server 10.12)
WARNING: psql version 9.2, server version 10.0.
         Some psql features might not work.
Type "help" for help.

PostgreSQL version: 10.12, pg_receivewal not exist?


texts: I was just seeking some higher advice on whether or not having your main data on one database, then having separate server database to use for frequent updating and reading will prove to be an optimization or a failure. My logic was that sharing the load like this across 2 separate server databases will be quicker than packing it all in on one shared hosting Thanks for your time.


texts: Users Export From Mysql Version 8.0 to 5.7

texts: I want to exports all users from mysql version 8.0 to 5.7. Can you guide me for this activity. I want same password and privs should migrate.


texts:
I have develop a web application in MVC4 it is a application about credit and financing. Application is used by 40 to 50 users at a time. Application reporting is very heavy takes 2 to 3 minutes in generating.

I am using single db user 'sa'

Now I am thinking to create all application users in server 2008 also.
Each application user will connect with own db user not with 'sa' user
my question is that is It right decision?

It will increase my application performance?  If it is not right then how i can increase its performance? as 40 users taking reports with single 'sa' user may be take more time instead off each user taking with its own database user.


texts:
When running an ALTER TABLE ... ADD COLUMN ... I get:

Error Code : 1878
Message    : Temporary file write failure.

And on log_error (/DATA/mysql/.err)

2016-06-29 17:27:01 7f11f08a7700 InnoDB: Error: Write to file (merge) failed at offset 3145728.
InnoDB: 1048576 bytes should have been written, only 327680 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html

Context

I'm running MySQL on a VM. Guest is SuSE, host is Windows 7.
Got the error after having dropped the schema and recreating it from a dump.
I had the same error one day ago (except the statement was a create index), and it just spontaneously stopped happening. But this time it's happening for a while.
If I keep retrying, the count of bytes written in the logs gets smaller every once in a while.


texts:
I copied oldDatabase as newDatabase. Modified newDatabase's schema.
Meanwhile, oldDatabase's data are processing and updating.

I want to copy oldDatabase's data to newDatabase.

What I have done is:

Clean the data of newDatabase
Generate data only script of oldDatabase
Run this script in newDatabase.

And I got a lot of error messages like:

Cannot insert explicit value for identity column in table 'MyTableName' when
IDENTITY_INSERT is set to OFF.

Invalid column name 'MyColumnName'.

Violation of PRIMARY KEY constraint 'PK_MyTableName'. Cannot insert duplicate
key in object 'dbo.MyTableName'.

Some tables and rows data are affected. But, tables with above error are not affected. How can I fix it? Is there a best way to make this stuff?

PS

I run my script using this command ,

sqlcmd -S myServerName -U userName -P passowrd -i PathOfMyScriptFile


texts: Why can't you do basic operations AFTER an aggregate in a window function? Like (max(x)+5), vs (5+max(x))

texts:
The syntax for aggregates and window functions is confusing, this does not work,

SELECT (max(x)+5) OVER ()
FROM generate_series(1,10) AS t(x);

While this works

SELECT 5+max(x) OVER ()
FROM generate_series(1,10) AS t(x);

That's fine if the operator is commutative, but I'm trying to divide an interval by seconds (in my specific case though I worked around it). Is there anyway to simplify this.

SELECT (max(x)/5) OVER ()
FROM generate_series(1,10) AS t(x);

So another encapsulating query is not needed? Is this a PostgreSQL thing, or a SQL thing? Is there a way to disambiguate the query?