texts: What is a “transition table" in Postgres?

texts:
The page describing what's new in Postgres 10 mentions “Transition Tables for Triggers”.

Transition Tables for Triggers
This feature makes AFTER STATEMENT triggers both useful and performant by exposing, as appropriate, the old and new rows to queries. Before this feature, AFTER STATEMENT triggers had no direct access to these, and the workarounds were byzantine and had poor performance. Much trigger logic can now be written as AFTER STATEMENT, avoiding the need to do the expensive context switches at each row that FOR EACH ROW triggers require.

What is a transition table?


texts: How to search for an ID and its descendants in this query?

texts:
I have the following tables:

Project                Unit
-------------          ---------------
ID  | IDUnit           ID   | IdParent

I have to select all projects that have the IDUnit equal to X and all its descendants.

I am doing the following:

SELECT
    p.*
FROM Project p
WHERE
(

  P_Id    IS NULL

  OR (
    p.IDUnit IN (SELECT ID FROM Unit
    START WITH ID =   P_Id
    CONNECT BY PRIOR ID = IdParent)
  )
)

It works, but the table Unit has too many rows, and the query takes over 5 seconds to complete when there is value on P_Id.

How can I optimize this query?


texts: SQL Server 2016 SP2 - ONLINE REBUILD DEADLOCKed Itself - is this a bug?

texts:
ALTER INDEX [PK_Order_Line_Inventory]
ON [OrderManager_C].[Order_Line_Inventory] REBUILD
WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION= 5, ABORT_AFTER_WAIT=SELF) )

This was satisfied by 3 threads within SPID 58.  However, arguing over the same data page, one of the threads became the DEADLOCK victim.  Process IDs are different, as are the Execution Context IDs and the Txn properties.  There are actually two DEADLOCKs for this same command, one for the 3 threads, the 2nd for the surviving 2 that then became one.

The SQL 2017 Guidelines for Online Index Operations has this paragraph, "Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server Database Engine will select the user or application activity as a deadlock victim.", but as the deadlock involved "itself", my root cause wasn't found.

The table has just a single-column, bigint, Clustered PK, and contains 2.7M rows in 520MB; one FK to its parent in a 1::0/1 relationship.

Questions arise:

Is this a bug?  I've not seen this behavior in over a decade with SQL Server.
Did any of the REBUILD survive?
Is this likely to recur?
I guess I could enhance our Defrag Tool to allow an option to override MAXDOP=4 to =1 for specific tables, but is that really necessary?

I've hunted for similar occurrences but what looked a promising link from SQLCoffee to MS KB978250 met a 404 error and no hits at all from searching MS (Original thread).  That KB spoke of a fix to SQL2008 - if relevant, one would assume it should still be a fix in the 2016 bits...

Can anyone shed any light on this, please?

Graph: On OneDrive


texts:
I am new to SQL Server Admin and am hoping to get some help? I have a database that only contains views of data from another database. It has no tables at all.
Is there a way to backup the data in the views?
Thanks in advance.


texts: Insert queries creating locked objects Oracle

texts:
In my asynchronous Spring Boot application I started noticing that threads where not returning from save() method - they were not dead, still running but hanging/being blocked during the saving to the database part.
To investigate this, I issued a query to see if there were any locked objects, and indeed there were as many locked objects as there were blocked threads in my application. Also, the queries of the blocked objects were 'update' queries which was even more puzzling since the only thing that application does is insert new data - no deletes or updates.
Any ideas what could be causing this? Any suggestions how I could investigate this further?
Thank you.


texts: User called mysql in fresh mariadb install on debian

texts:
I just installed MariaDB 10.4 on Debian 10.
In addition of root, there is a user called mysql.

Can I remove this user ?
What is the purpose of this account ?

Thanks.


texts: What is WITH QUERY EXPANSION MODE in MySQL Fulltext Search

texts:
MATCH (col1,col2,...) AGAINST (expr [search_modifier])

search_modifier:
{
     IN NATURAL LANGUAGE MODE
   | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
   | IN BOOLEAN MODE
   | WITH QUERY EXPANSION
}

Could you please explain to me what QUERY EXPANSION is, and what does MySQL do with this mode?


texts: Multiple Schemas for increasing MySQL (5.7) Performance

texts:
Lets start with my question first:

Does having multiple schemas (1 main and 1 daily - having 5-6 tables with same structure as main, but only working for the day and will be synced back with the main one every night) in MySQL (5.7) server can impact its performance ?

More background:

The problem I'm facing with my database is that the application was working fine under certain load (still does but LA goes up to 8), but past few days I'm fighting over CPU usage ... I dig the problem and the reason for that is that there are locks going on and my workers are stuck trying to insert/update new data.

To go even deeper I'm doing at least 1K inserts per minute, up to 2K updates per minute (some are on the previously inserted records), and more than 4-5K selects per minute.

And I'm not talking for simple integer inserts ... lots of strings, timestamps, integers and 6 indexes (total column number in the table is 32)

My server is fairly powerful (huh, fairly).

16GB RAM
1TB SSD
8 Core, Intel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz
Software: MySQL 5.7

Part of my my.cnf

innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 30G
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8

and still I'm getting LA of up to 8.00

The problem with all this is because all my workers start every minute and they sort of confront each other.

What I came with is following scenario:
- create separate schema with my 'daily' working tables (mostly the SELECTS) and do the inserts/updates in my original schema.

Will that decrease the load on the server?

And one off-topic question: Is LA of up to 8.00 healthy for the server ?

Thanks

P.S. If you need any further info, just let me know I'll share it.


texts: SQL Server Configuration Manager returns remote procedure call failed

texts:
I open the SQL Server Configuration Manager and it returns remote procedure call failed.

This is Windows 7 Ultimate version, SQL Server 2008 R2 Express version with advanced services.

Previously I installed SQL Server 2008 R2 Express version and since I needed the full text search I uninstalled SQL Server 2008 R2 Express and then installed SQL Server 2008 R2 Express with advanced services.

P.s my RPC background service is running.

I executed the following command and it returned an error:

 C:\Users\venkat>mofcomp.exe "C:\Program Files (x86)\Microsoft SQL    Server\100\Shared\sqlmgmproviderxpsp2up.mof"

 Microsoft (R) MOF Compiler Version 6.1.7600.16385
 Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
 Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof
 MOF file has been successfully parsed
 Storing data in the repository...

Error:

  An error occurred while processing item 1 defined on lines 4 - 7 in file C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof:
  Error Number: 0x80041003, Facility: WMI
  Description: Access denied
  Compiler returned error 0x80041003


texts: MySQL, query optimization with join on huge table

texts:
I'm trying to execute a query on MySQL (Database created from a GTFS schema)
I have two tables like these:

trips

trip_id   |  route_id
---------------------
1         |  409
2         |  409
3         |  409
4         |  409
5         |  671
6         |  671
7         |  671
8         |  105
9         |  105L
10        |  105L
11        |  105L

The other table:

stop_times

trip_id   |  stop_id
---------------------
1         |  A
2         |  B
3         |  C
4         |  C
5         |  A
6         |  A
7         |  B
8         |  A
9         |  B
10        |  C
11        |  D

I'm trying to join those tables to find all stops with every route id associated, without duplicates, like this:

result

stop_id   |  route_id
---------------------
A         |  409
A         |  671
A         |  105
B         |  409
B         |  671
B         |  105L
C         |  409
C         |  105L
D         |  105L

The problem is that those tables are huge, trips contains 200k rows while stop_times contains about 6M rows.

The query that I tried is:

SELECT DISTINCT trips.route_id, stop_times.stop_id
FROM trips
LEFT JOIN stop_times
  USING (trip_id)
ORDER BY stop_times.stop_id

Explain output is:

 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trips
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 201128
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: stop_times
         type: ref
possible_keys: trip_id
          key: trip_id
      key_len: 52
          ref: muoviroma.trips.trip_id
         rows: 13
        Extra: NULL
2 rows in set (0,00 sec)

SHOW CREATE TABLE outputs:

mysql> SHOW CREATE TABLE trips\G
*************************** 1. row ***************************
       Table: trips
Create Table: CREATE TABLE `trips` (
  `route_id` varchar(20) NOT NULL,
  `service_id` varchar(20) NOT NULL,
  `trip_id` varchar(50) NOT NULL,
  `direction_id` bigint(20) NOT NULL,
  `shape_id` bigint(20) NOT NULL,
  KEY `trip_id` (`trip_id`),
  KEY `route_id` (`route_id`),
  KEY `service_id` (`service_id`),
  KEY `direction_id` (`direction_id`),
  KEY `shape_id` (`shape_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0,00 sec)

mysql> SHOW CREATE TABLE stop_times\G
*************************** 1. row ***************************
       Table: stop_times
Create Table: CREATE TABLE `stop_times` (
  `trip_id` varchar(50) NOT NULL,
  `arrival_time` time DEFAULT NULL,
  `departure_time` time DEFAULT NULL,
  `stop_id` varchar(20) NOT NULL,
  `stop_sequence` int(11) NOT NULL,
  KEY `trip_id` (`trip_id`),
  KEY `stop_id` (`stop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0,00 sec)

Those queries are performing in about 20 seconds, I'm searching a query that performs in less than 500ms, what can I try?


texts: How to implement correctly from SQL to NoSQL (DynamoDB)?

texts:
I am just new in NoSQL and DynamoDB. I try to moving from using AWS RDS to DynamoDB.

I have table user which can have more than one of work experiences and can have more than one of interest (table of interest have master data in interest table). There will be a lot query filtering by user by their work experiences and interest. Example: Find user who have work experiences title is 'Programmer' or find user who have interest is 'SQL, NoSQL'.

User Table:
- user_id (PK)
- name
- birthdate
- ...

Work Experience Table:
- user_id (PK)
- work_sequence_id (PK)
- work_title
- work_description

Interest Table:
- interest_id (PK)
- interest_name

User Interest Table:
- user_id (PK)
- interest_id (PK)

So I try to convert it to DynamoDB:

User Table:
- user_id (PK/Partition Key)
- name
- birth_date
- ...

Work Experience Table:
- user_id (PK)
- work_sequence_id (SK/Sort Key)
- work_title (global secondary index)
- work_description

For the interest table, I want to re-structure the table. Basically, beside filtering user by interest, I just want to get the most popular interest for suggestion word (filter by word that contain 'search_word'). Example if there is 7 user have interest in 'SQL' and 10 user have interest in 'NoSQL'. When user search the word 'SQ' the word 'NoSQL' will appear first and 'SQL' will appear second.

So I create like this:

User Interest Table:
- user_id (PK)
- interest_name (SK)

Interest Table:
- interest_name (PK)
- count (SK)

Then I will use the count to get the popular interest.

Usually I use the join to get user data, work experiences, and interests also using aggregated function to get most popular interest. Now I need to use four query operator to get all the data. Basically, I need efficiently and effectively store the data in term of cost.

Do you have any database design suggestion for this scenario in DynamoDB?


texts: centos container, installing mariadb server

texts:
I am trying to run a mariadb server on a docker image(CentOS Linux release 7.3.1611 (Core)).

I install mariadb with command yum install mysql. after installing complete, i try to execute command mysql but i got this error:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

the directory in /var/lib/mysql is empty

the my.conf file in /etc contains:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

with running command systemctl start mariadb I got this error:

  Failed to get D-Bus connection: Operation not permitted

Did I make any mistake?


texts: Is it a normal practice when there're 2 columns and if one is true the other must false, and vise versa?

texts:
I have something similar to this, but not precisely it:
I sell only coffee and tea, and for the next 50 years I'll be selling only these. In a table "drinks" I have 2 columns: is_coffee? and is_tea?. Only one of these can be true, obviously, and if one is true, the other must be false.
Also, I have some columns that are specific to only coffee, when is_coffee? is true, and others to only tea.
Does it make sense hold all of them in a single table? Is it a normal situatation when there're 2 columns and if one is true the other must false, and vise versa?


texts: How to get information about the current cluster configurations with list of datacenters?

texts:
Is any way to get information about the current cluster configurations with a list of datacenters?

I have tried to fetch this list from system.local using cqlsh with setting the consistency level equal to ALL, but it shows me only one datacenter.

Basically I want to check that all datacenters which I have created are constituting the one cluster.


texts: More than 4000 chars gives string literal too long error on oracle

texts:
While updating a field that will have more than 4000 chars, I was given a error :

ORA-01704: string literal too long

So, going through few blogs, I got this:

declare
    vClobVal varchar2(32767) := 'long text'
begin
    update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY = vClobVal
    where FKOL_OFFICEWISE_LETTER_ID=240;
end;

This worked for me when fired at Toad.
Now, I created a stored procedure and compiled as :

CREATE OR REPLACE PROCEDURE FMIS3.UPDATE_LETTER_BODY  (
    body_text IN FMS_K_OFFICEWISE_LETTER.FKOL_LETTER_BODY%type,
    condition_id IN FMS_K_OFFICEWISE_LETTER.FKOL_OFFICEWISE_LETTER_ID%type
) IS
begin
    update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY = body_text
end;

which is called as :

 call UPDATE_LETTER_BODY('long string',201);

and this does not work for more than 4000 chars again. Can't I define the size of varchar2 as it gave me error? Any suggestions ?


texts: MySQL UPDATE statement using a table created on the fly

texts:
I want to update a table, say Table 1 using a table that I create on the fly (saytemp). To achieve this I have the following query:

UPDATE Table1 SET COUNT = temp.ctemp FROM ( SELECT A, SUM(UNO) AS ctemp FROM Table1
                                            GROUP BY A
                                            ORDER BY A)
                                      AS temp,Table1 t1
                                      WHERE temp.A = t1.A;

It works well in SQL Server 2008 but MySQL  throws out the error

syntax error, unexpected FROM, expecting 'END_OF_INPUT' or ';'

Any idea what I am missing here?


texts: I am looking for a robust open source tool for monitoring SQL server. Ours is a small organization and we don't have any monitoring tool in place and would like to know if any open source tool is available so that we can start monitoring lower environments to start with.


texts: Transpose MySQL rows as column names - DYNAMIC

texts:
My goal is to be able to calculate the time difference between the ticket timestamp being created and closed and if reopened Add time from being reopened to closed again etc..
I have an existing mySQL table called ost_ticket_event as follows:

I need to display this table like below where the state could be dynamic, ie: created, closed, assigned, transfer or reopened.
The state for one ticket_id could be closed/reopened more than once etc.:

ticket_id//////CREATED///////////////CLOSED//////////////REOPENED/////////////CLOSED2//
1-----------18-01/15 12:00 -------18-01/15 13:00------18-01/15 13:30------18-01/15 14:00
2-----------19-01/15 10:00--------20-01/15 09:00-------------NULL------------NULL

I tried LEFT JOIN Group CONCAT but cant seem to get it to produce the desired results.

This is my query:

SET group_concat_max_len=20000;

SET @a = 0;
SET @b = 0;
SET @num := 0;
SET @ticket_id := '';

SET @line1 =    CONCAT (
 'SELECT ',
 (
  SELECT CONCAT('ticket_id,', GROUP_CONCAT(' state',@a:=@a+1,', timestamp',@a))
  FROM ost_ticket_event as ts1
  WHERE ticket_id =
  (
   SELECT ticket_id
   FROM ost_ticket_event tm
   GROUP BY ticket_id
   LIMIT 1
  )
 ),
 ' FROM ost_ticket_event' ,
 (
  SELECT CONCAT(' ', REPLACE(REPLACE(GROUP_CONCAT(' LEFT JOIN ( SELECT ticket_id as         ticket_id',@b:=@b+1,'| state as state',@b,'| timestamp as timestamp',@b,' FROM ( SELECT *| @num := IF(@ticket_id = ticket_id| @num + 1| 1) AS row_number| @ticket_id := ticket_id AS dummy FROM ost_ticket_event ORDER BY ticket_id) AS x',@b,' WHERE x',@b,'.row_number = ',@b,') as t',@b,' ON  ticket_id',@b,' = ticket_id'),',',' '),'|',','))
  FROM ost_ticket_event as ts2
  WHERE ticket_id =
  (
  SELECT ticket_id
      FROM ost_ticket_event tm
   GROUP BY ticket_id
   LIMIT 1
  )
 ),
 ' GROUP BY ticket_id'
);

PREPARE my_query FROM @line1;
EXECUTE my_query;

This is the output:


texts:
I have a simple table with a column of type REAL.

There are 2 rows in the table and the values are 0.23 and 0.24. If I run the following:

SELECT AVG(MyColumn) FROM dbo.MyTable

I would expect to get a result of 0.235 but actually I get 0.2349999901234.

Can somebody please tell me why?


texts:
Can someone explain the difference between these types of VACUUM in PostgreSQL?

I read the doc but it just says that FULL locks the tables and FREEZE "freezes" the tuples. I think that's the same. Am I wrong?


texts: selecting next todo item from child table

texts:
I've a table with millions of records, relationship is one (object) to many (object_items).

object_items:

CREATE TABLE `object_items` (
  `item_name` varchar(50) NOT NULL DEFAULT '',
  `object_id` int(10) unsigned NOT NULL DEFAULT '0',
  `sequence` int(10) unsigned NOT NULL,
  `completed` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_seq_object_id` (`sequence`,`object_id`),
  KEY `idx_object_id` (`object_id`),
  KEY `idx_seq` (`sequence`)
) ENGINE=InnoDB AUTO_INCREMENT=3408237 DEFAULT CHARSET=utf8mb4

Sample Data:

+-----------+-----------+----------+-----------+------+
| item_name | object_id | sequence | completed | id   |
+-----------+-----------+----------+-----------+------+
| ABCD      |        10 |        1 |         1 |    1 |
| BCDE      |        10 |        2 |         1 |    2 |
| CDEF      |        10 |        3 |         1 |    3 |
| DEFG      |        10 |        4 |         0 |    4 |
| ABCD      |        11 |        1 |         1 |    5 |
| BCDE      |        11 |        2 |         1 |    6 |
| CDEF      |        11 |        3 |         0 |    7 |
| DEFG      |        11 |        4 |         0 |    8 |
| ABCD      |        12 |        1 |         1 |    9 |
| BCDE      |        12 |        2 |         1 |   10 |
+-----------+-----------+----------+-----------+------+

Desired Result:

+-----------+-----------+----------+-----------+------+
| item_name | object_id | sequence | completed | id   |
+-----------+-----------+----------+-----------+------+
| DEFG      |        10 |        4 |         0 |    4 |
| CDEF      |        11 |        3 |         0 |    7 |
+-----------+-----------+----------+-----------+------+

The Query I run:

select
  a.*
from object_items a
where a.sequence = (
  select min(sequence)
  from object_items b
  where a.object_id = b.object_id
    and b.completed = 0
)

Which actually works but when I use limit, but if I run count(*) it just dies.

Explaining the query:

+----+--------------------+-------+------------+------+---------------+---------------+---------+-----------------+---------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key           | key_len | ref             | rows    | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+---------------+---------+-----------------+---------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL          | NULL          | NULL    | NULL            | 3268598 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | ref  | idx_object_id | idx_object_id | 4       | db.a.object_id  |      21 |    10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+---------------+---------+-----------------+---------+----------+-------------+

Is there a better way to get next TODO item that's not complete yet, by sequence, only for the objects those have at least one item to be done, with heavy database like this?

Thanks


texts: Enforcing that a user should have one or more email addresses

texts:
I'm trying to design an application where a user can authenticate oneself with one password and one or more email addresses. I'm not sure how to enforce this constraint.

The tables so far are simple:

Table users:

id | password |
--------------------------------------

Table email_addresses:

user | email |
--------------------------------------

However, in the current situation, a user can also have zero email addresses, thereby preventing the user from authenticating oneself.

What's the best way to deal with this type of constraint?


texts: Many-To-Many Relation by storing comma-separated ids in a column?

texts:
Is it OK to sometimes instead of making a join table for a many-to-many relation, store the ids of entity A in a column of entity B? It would look something like this:
// A
id | name
---------
10 | foo
20 | bar

// B
id | name | AIds
-----------------
30 | buzz | 10,20

I only need to know which A's are owned by B when looking at B, and not the other way around (and I can handle the logic in code, joins are not necessary). Hence the lack of a join table.
Or are there some bad aspects to this I'm not seeing?


texts: How do you determine the direction of a relationship?

texts:
Suppose I have the following relationship diagram:

How would you describe the relationship determined by the red line?

Reading from left to right you would say each order has only one customer so it could be described as a "many to one" whereas from right to left each customer can have many orders so you could say it is "one to many".

Without direction it seems to me to be futile to try and discuss a relationship yet I rarely see people say, it is a one to many (from customer to order) or many to one (from order to customer).

So my question is why is this the case (is it just implied from context?) or does the primary key have some relation, as in we wouldn't really store an order_ID in the customer table (I am not a DBA but I don't think this make sense from a design point of view) whereas we would have to store a customer as a foreign key in the order table otherwise we wouldn't know who bought the stuff, so would this give precedence to reading from right to left and we say the red line is a one to many relationship?

Any clarification would be great!


texts: How Long Would Take to Upgrade an 1T Oracle DB from 10g to 11g?

texts: How long would take to upgrade an Oracle DB which has 1T data from 10g to 11g usually/roughly? I need to estimate the down time for it since it is a prod db. Thanks much!


texts: Is it possible to create a custom log table. For example I have 10 schema and I would like to log if somebody is using the "areva" schema. I want to know the last executed command time and the user. Is this possible in MySQL?


texts: How to store multiple data in a cell inside MySQL?

texts:
I want to store multiple dates in a row. Each row has a unique id and each corresponding id can have multiple action dates. The number of dates may differ for each row. I have multiple ID's and each ID has multiple dates.

ID   Date1        Date2        Date3        ...          ...         ...
1    2018-05-19   2018-04-09   2018-08-09
2    2016-01-30   2017-05-04   2016-06-07   2020-05-17   2021-06-23  ...
3    2001-05-06
4    2018-11-05   2013-06-09

What is the best way to store multiple dates for a given id?


texts: Query to collect the recent TimeSampled row using inner joins in MS SQL

texts:
I have written below inner join query to get the data from my database tables. But  output of this data is coming up with almost 140+ lines. Output is below. I need data in only one row with the most recent value by "TimeSampled"

select DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where ObjectName = 'Processor'  AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
AND DisplayName Like 'BLRW1ETST01.jcp.com'
AND CounterName = '% Processor Time'
GROUP BY DisplayName, ObjectName, CounterName, InstanceName, SampleValue
ORDER BY  TimeSampled desc

How I can retrive results with only last Time Sampled row with respect to DisplayName. I have multiple servers (DisplayName) like this to retrieve the data.

Thanks,
Ramu Chittiprolu


texts: Best design to reference multiple tables from single column?

texts:
Proposed schema

First and foremost, here is an example of my proposed schema to reference throughout my post:

Clothes
----------
ClothesID (PK) INT NOT NULL
Name VARCHAR(50) NOT NULL
Color VARCHAR(50) NOT NULL
Price DECIMAL(5,2) NOT NULL
BrandID INT NOT NULL
...

Brand_1
--------
ClothesID (FK/PK) int NOT NULL
ViewingUrl VARCHAR(50) NOT NULL
SomeOtherBrand1SpecificAttr VARCHAR(50) NOT NULL

Brand_2
--------
ClothesID (FK/PK) int NOT NULL
PhotoUrl VARCHAR(50) NOT NULL
SomeOtherBrand2SpecificAttr VARCHAR(50) NOT NULL

Brand_X
--------
ClothesID (FK/PK) int NOT NULL
SomeOtherBrandXSpecificAttr VARCHAR(50) NOT NULL

Problem statement

I have a clothes table which has columns like name, color, price, brandid and so on to describe the attributes for a particular item of clothing.

  Here's my problem: different brands of clothing require differing
  information. What is the best practice for dealing with a problem like
  this?

Note that for my purposes, it is necessary to find brand-specific information starting FROM a clothes entry. This is because I first display the information from a clothes entry to the user, after which I must use its brand-specific information to purchase the item. In summary, there has to be a directional relationship between clothes (from) and the brand_x tables.

Proposed/current solution

To cope with this, I have thought of the following design scheme:

The clothes table will have a brand column which may have id values ranging from 1 to x, where a particular id corresponds to a brand-specific table. For example, id value 1 will correspond to table brand_1 (which might have a url column), id 2 will correspond to brand_2 (which might have a supplier column), etc.

Thus to associate a particular clothes entry with its brand-specific information, I imagine the logic at the application-level will look something like this:

clothesId = <some value>
brand = query("SELECT brand FROM clothes WHERE id = clothesId")

if (brand == 1) {
    // get brand_1 attributes for given clothesId
} else if (brand == 2) {
    // get brand_2 attributes for given clothesId
} ... etc.

Other comments & thoughts

I'm attempting to normalize my entire database in BCNF, and although this is what I came up with, the resulting application code makes me feel very anxious. There is no way to enforce relations except at the application level, and thus the design feels very hacky and, I anticipate, very error-prone.

Research

I made sure to look through previous entries before making a post. Here's a post with a near-identical problem that I managed to find. I made this post anyway because it seems like the only answer provided does not have a SQL or design-based solution (i.e. it mentions OOP, inheritance, and interfaces).

I'm also kind of a novice when it comes to database design, and so I'd appreciate any insights.

It appears there are more helpful responses on Stack Overflow:

Here
And here
Aaaand here (key concept being: class table inheritance)

I have referred to the solutions there and suggest others finding my question do so as well.

Despite the above-provided links, I am still on the lookout for responses here and would appreciate any solutions provided!

I am using PostgreSQL.


texts:
I have this query (called from PHP, hence the $ variables):

SELECT a.name,
      St_AsText(a.geom),
      b.name AS name_b,
      b.id
FROM a
LEFT JOIN b ON b.id = a.b_id
WHERE UNACCENT(LOWER(a.name)) LIKE UNACCENT('$searched%')
ORDER BY a.geom <-> 'SRID=4326;POINT($lat $lon)'::geometry
LIMIT 2;

There is lower(a.name) BTree index on a.name, GIST index on a.geom and b.id is primary key

It runs about 40ms for $searched = "Maria%". Now, if I set $searched = "Mariasomething some%", time goes up to 2s.

Why? Can it be speed up or perform in a more stable manner?


texts: Production Database went into restoring mode

texts:
Yesterday one of my production database went into restoring mode,but fortunately i recovered it using below query

USE MASTER
EXEC SP_CONFIGURE 'Allow updates',1
RECONFIGURE WITH OVERRIDE
GO

EXEC SP_RESETSTATUS 'DatabaseNAME'
DBCC DBRECOVER('DatabaseName')
EXEC SP_CONFIGURE 'Allow updates',0
GO

RECONFIGURE WITH OVERRIDE
GO

It brought my database online, but I want to know why it went in restoring mode, I read SQL error log but I didn't get much information about it. Is the query I used to bring the database online correct? It is used to recover the database from suspect mode.


texts: Can a SELECT statement block another SELECT on SQL Server?

texts:
So the basic question is simple, can a SELECT statement alone block another SELECT statement? My understanding was you needed at least an UPDATE or INSERT for a SELECT to start blocking something because two SELECT will acquire shared locks and those won't block each other. If it is possible, what are the kinds of scenarios that could cause it.

To put this in context, I'm trying to solve some performance problems. I'm not a DBA, I'm just a software engineer having to do a DBA's job, so bear with me. We've recently found there's apparently lot of blocking happening on a database. The way we found out is there's a tool (really a set of scripts/jobs) to monitor the DB named DBWarden, and it compiles some data in a set of tables. For blocking, it uses the following query to insert blocking info into a table every 15 seconds:

SELECT
a.spid AS Blocked_SPID,
a.blocked AS Blocking_SPID,
a.loginame AS Blocked_Login,
a.hostname AS Blocked_HostName,
(CAST(a.waittime AS DECIMAL) /1000) AS Blocked_WaitTime_Seconds,
a.lastwaittype AS Blocked_LastWaitType,
a.[status] AS Blocked_Status,
a.[program_name] AS Blocked_Program,
CAST(st1.[text] AS NVARCHAR(MAX)) as Blocked_SQL_Text,
b.spid AS Offending_SPID,
b.loginame AS Offending_Login,
b.nt_username AS Offending_NTUser,
b.hostname AS Offending_HostName,
b.waittime AS Offending_WaitType,
b.lastwaittype AS Offending_LastWaitType,
b.[status] AS Offending_Status,
b.[program_name] AS Offending_Program,
CAST(st2.text AS NVARCHAR(MAX)) as Offending_SQL_Text,
(SELECT name from sys.databases WHERE [database_id] = a.[dbid]) AS [DBName]
FROM master..sysprocesses as a CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) as st1
JOIN master..sysprocesses as b CROSS APPLY sys.dm_exec_sql_text (b.sql_handle) as st2
ON a.blocked = b.spid
WHERE a.spid > 50 AND a.blocked != 0 AND ((CAST(a.waittime AS DECIMAL) /1000) > 0)

What I'm noticing is that both the blocked and offending SQL are almost always SELECT statements. Even if I try to run down a chain of blocks where something blocked something that blocked something, I'd expect to eventually hit an INSERT or UPDATE, but most times I don't, which is what led me to the original question.


texts:
I have a field called: update_count, which I need to reflect the number of times a row has been updated.
I have been looking into the SERIAL type for the column which sounds promising, but it has one problem - it is not transaction safe, which means that it may get out of sync. This may not happen.
My question is then: "How to handle an auto incrementing field on each column while supporting multiple concurrent client?"
Environment: Spark.


texts: Updating data from subpartition when the subpartition name is unknown

texts:
Thanks to JSapkota who helped me pull the subpartition name as per below query

select subobject_name from user_objects where data_object_id IN
(select dbms_rowid.rowid_object(rowid) from TABLE
WHERE DOCID = 'S00102981655537O')

Which gives me a result

SUBOBJECT_NAME
--------------
MAR16_IN_UK

Now if I update that ID using below statement it works

UPDATE TABLE SUBPARTITION (MAR16_IN_UK)
SET FLAG = 'Y'
WHERE DOCID = 'S00102981655537O';

1 rows updated

But the same query by dynamically pulling the subpartition name would throw an error

UPDATE TABLE
SUBPARTITION (select subobject_name from user_objects where
data_object_id IN
(select dbms_rowid.rowid_object(rowid) from TABLE
WHERE DOCID = 'S00102981655537O'))
SET FLAG = 'Y'
WHERE DOCID = 'S00102981655537O';

Error

Error report -
SQL Error: ORA-00971: missing SET keyword
00971. 00000 -  "missing SET keyword"
*Cause:
*Action:

Version: Oracle 11g


texts: MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEs

texts:
I am creating a module where every user often gets a record into a table for 10 to 300 seconds.

When the time expires a record gets deleted. The case is: there will be a lot of users and records will change really often - how this will affect application's performance for this table, because records will change really often and I am wondering if mysql is fine with that? Like indexes would come and go, data changes like 200 times/seconds for this particular table. Maybe I am choosing a bad solution for this kind of job. Any suggestions ?

Thank you!


texts: MySQL won't start after 5.5 security upgrade

texts:
I have the MySQL data directory running in a non-standard location on an Ubuntu 14.04 server. Last night it received a security upgrade and now will not start. The error message is

Can't find file: './mysql/plugin.frm'

More worrying is that when I go into the data directory there are now two directories call "mysql". When I cd in, all of my databases seem to be missing. I have no idea if I am in the old or new directory as they both seem to have the created on date.


texts: Is there any unique identifier for PostgreSQL? How can the master identify its slave unqiuely?

texts:
Is there any way to have more than one PostgreSQL slave servers in the same system.

If yes, how come a master detect each server as different slaves from the below query

select * from pg_stat_replication;

will give one row for each slave which are connected to it, with PID, IP-address/client_address, client_port, etc..

If two servers are running in the same machine gives the same IP-address so it is not a unique identifier.

If slave got restarted then both client_port and PID will change so these are also cant be a unique identifier.

So, what is the unique identifier used by Postgres master to identify each slave server uniquely? Or how it is finding its slave servers differently?


texts: 2ndQuadrant/Pglogical multi-master cluster installation

texts:
I'm looking for a way to make a multi-master cluster with pglogical.
When I say multi-master, it's means 3 nodes, where each of them is a provider and a subscriber.
Currently I have two nodes that working fine as a provider and a subscriber at the same time.
But when I whant to add my third node, with the two others, the conflict_detections appears, because the subscription try to insert always the same tuples, like a loop.
So what I need in architecture for the cluster is :

3 nodes provider and subscriber as the same time
2 subscriptions for each node (to send and to receive data by a replication_set)
3 replication_set (to forward, all insert, update,delete orders on each node )

like this each node could send and receive data, normally.
My questions are :
Does anyone have already installed a multimaster cluster with pglogical ?
If yes, in wich order I have to do the subscription and the replication_set ?
Specially for don't have a conflict_detection, when synchronize data.
Does anyone could share me a example of installation step by step for the subscription/replication_set part ?
thank you by advance.


texts: VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed?

texts:
For a log table, I have a custom message column.

It will never be updated.
It will be searched with LIKE conditions.
It will be capped at 1KB.

TEXT fields are slower than VARCHAR fields for lookups because they stored separately, while VARCHAR fields are stored inline.

What I don't know and couldn't find here on SO (there already are hundreds of VARCHAR vs TEXT questions) or on the MySQL docs: when inlined, do VARCHAR fields take at least the number of bytes specified, or do they vary?.

One of the goals is keeping the size on disk as small as possible. If VARCHAR does not vary, then TEXT has a significant advantage. Otherwise, VARCHAR will provide both lookup speed and small disk footprint.

EDIT: For clarification, if the field is created with ASCII collationwith and a size of 100, a string of length 10 will take 10 bytes or 100 bytes on disk (for inline VARCHAR)?


texts: MySQL Workbench - how to search all columns in every table

texts:
In MySQL Workbench how do I search for a column which can be located in any schema. Basically I'm looking for a column called "notification" and I want to search all schemas for that column.

Look at this screenshot where I know for a fact there is a column named "activated_at" yet when I search for it after highlighting all my tables (using select all command also) it finds nothing. Why?

but if I run the following query its able to find the column, so why not with GUI?

    SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    ordinal_position

FROM  INFORMATION_SCHEMA.COLUMNS

WHERE column_name = 'activated_at' ;


texts: How to Create a PostgreSQL database with different Encoding?

texts:
I want to create a New Database that will store data in German(i.e Text with accents etc). Hence I tried to create it using the following SQL:

CREATE DATABASE testdb
  WITH ENCODING='LATIN1'
       OWNER=postgres
       TEMPLATE=template0
       CONNECTION LIMIT=-1;

When I run this on my Windows Development machine, I get the following error message:

  ERROR:  encoding "LATIN1" does not match locale "English_United
  States.1252"
  DETAIL:  The chosen LC_CTYPE setting requires encoding "WIN1252".

On my Linux server, I get the following error message:

  ERROR:  encoding "LATIN1" does not match locale "en_US.UTF-8"
  DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".

What do I need to do to create a Database which can correctly store German text?


texts: Session close unexpectedly destroying temporary table

texts:
I am using Temporary table to delete multiple row at the same time in the database. Every time I try to use temporary table on a specific Sql-Server-2016 it fails.

I did try to reproduce the issue on a different instance and it is not happening.

The trace from the server showing the issue shows that just after successfully creating the Temporary Table the session is closed. The same session is then used to try to insert data in the Temp table but it failed since the Temp table only last as long as the session that created it. The Connection to the database is not pooled (and it says so in the traces too).

I compared the configuration between a fresh installation and the server showing the issue and all the configuration from sys.configurations are the same.

Here are the traces I collected. Every Audit Login have the same parameters command so I will only put put it once.

Audit Login
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

SQL:BatchStarting   CREATE TABLE ##TempA368AEA69A2F4E949CE8DC668FD7F86F (...)
SQL:BatchCompleted  CREATE TABLE ##TempA368AEA69A2F4E949CE8DC668FD7F86F (...)
Audit Logout

Audit Login
Exception   Invalid object name '##TempA368AEA69A2F4E949CE8DC668FD7F86F'
SQL:BatchStarting   INSERT INTO ##TempA368AEA69A2F4E949CE8DC668FD7F86F
VALUES(...)

Exception   Invalid object name '##TempA368AEA69A2F4E949CE8DC668FD7F86F'
SQL:BatchCompleted  INSERT INTO ##TempA368AEA69A2F4E949CE8DC668FD7F86F
VALUES(...)
Audit Logout

My question, is there any reason a SQL-Instance closes a session without the application disposing it/timeout


texts: Table optimize command crashed MySQL server

texts:
I have a large 5GB table on one of my MySQL databases that I wanted to optimize.

I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a temporary one (is that accurate?).

Anyways, I did not notice that I don't have enough space on my HD to complete this action so it failed in the middle of it:

+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| support.exported | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| support.exported | optimize | error    | The table '#sql-420a_65fb' is full                                |
| support.exported | optimize | status   | Operation failed                                                  |
+------------------+----------+----------+-------------------------------------------------------------------+

Now, my HD is 100% full since the temporary table that was created ate up all the storage I had left and I don't have any files I can delete from other locations on the HD to free the space.

I tried to restart the mysql daemon since I thought that might initiate a drop to the temporary table that was created and that way it will free some space, but what now happens is that I cannot start the server:

MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

Is there a way to drop/delete the temporary table that was created during the optimize process without having the mysql server running?


texts: Proper way to add new column in database

texts:
What is the proper approach when we add a new column into an existing table?

For example, I already have columns like, Foo1, Foo2, Bar1, Bar2. Now I want to add a new column called Foo3.

What is standard approach (if that is such thing) when I want to add a column with similar name?

I see 2 choices:

Create temp table with new structure where new column is next to columns with same name, copy data to new table from existing table and drop existing table and rename temp table. Bit complex process but makes database fields more readable.
Add new column in end. More simpler operation. But column names may not be clearly understood if they in very end.

To get some reference, we are using Database Projects to source control database changes and have a nicer GUI for app developers to make database changes. And we are using some kind of ORM to interact with database so nobody is querying database using database object names.

Update:
I have couple of indexes on some of existing columns. But columns that have similar names (including one that I want to add) are not part of any index.


texts: Data driven subscription query parameters in SSRS

texts:
I am looking at customizing my data driven subscription query in SSRS so that depending on the results of the original query it will notify the correct department.

For example the query returns:

ID Dept   Amount Name

1  Sales  $70    Shelly

Then the email only goes to the sales department and no other department such as marketing or HR

I am looking at using a data driven query and notifying users by email


texts:
I'm trying to write a query that will put values in three different columns. I have the query pulling the information I need, I just don't know how to split the values out into different columns.

The first column value should display the values where financialinst.name = National Financial Services

The second column value should display the values where financialinst.name is not equal to National Financial Services (DirectValue)

The last column value should be the total of these two columns (National Financial Services + Direct Values)

Here is what I have so far:

SELECT     Household.Name, Account.AccountNumber, Account.Registration, FinancialInst.Name AS [Financial Institution], SUM(Asset.Value) AS Value,
                      FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode,
NFSvsDirect=CASE WHEN FinancialInst.Name ='National Financial Services' THEN 'NFS'
ELSE 'Direct Business' END
FROM         PlanFirmSpecCode INNER JOIN
                      FinPlanSpecCode ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID RIGHT OUTER JOIN
                      Household INNER JOIN
                      Account ON Household.HouseholdID = Account.HouseholdID INNER JOIN
                      Asset ON Account.AccountID = Asset.AccountID INNER JOIN
                      FinancialPlanner ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID LEFT OUTER JOIN
                      FinancialInst ON Account.FinancialInstID = FinancialInst.FinancialInstID ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE     (Account.Closed <> 'Y') AND (Asset.Value > '0')
GROUP BY Household.Name, Account.AccountNumber,  FinancialInst.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode, Account.Registration,
                      Account.Description


texts: Avoiding Key Lookup when using MIN and MAX

texts:
I have a somewhat simple table that takes

 Id guid (pk)
 SoftwareId guid (FK to software table)
 OnlineUsers int
 OnlineDevices int
 SampleDate datetime
 SDHour (persisted computed field, holding just the hour)
 SDMinute (persisted computed field, holding just the minute / 5)

The goal is between two dates draw a line graph of samples every 5 minutes, sometimes there is more than one sample every 5 minutes so to deal with that we just take the max amount of OnlineUsers and OnlineDevices

SELECT
    MAX(OnlineUsers), -- Only select the most users
    MAX(OnlineDevices), -- Only select the most devices
    SDHour, -- Used to allow creating the graph split into 5 minute increments
    SDMinute, -- Used to allow creating the graph split into 5 minute increments
    SoftwareId,
    Software.FriendlyName as FriendlyName, -- Each software peice has a name
    Software.MaxUsers as MaximumSupportedUsers, -- Maximum supported users
    Software.MaxDevices as MaximumSupportedDevices, -- Maximum supported devices
    DATEDIFF(SECOND, @dtStartDate, min(SampleDate)) as OrderTime -- Used to sort
FROM SoftwareUserSamples
JOIN Software on Software.Id = SoftwareUserSamples.SoftwareId-- Get sw details
LEFT OUTER JOIN SoftwareTypes on Software.SoftwareTypesId = SoftwareTypes.Id
WHERE Software.ClientId = @gClientID -- Multi tannancy
AND (@bFilterHiddenRows = 0 OR Software.HideByDefault = 0) -- hide some rows
AND SoftwareUserSamples.SampleDate > @dtStartDate -- Don't get all rows
AND SoftwareUserSamples.SampleDate < @dtEndDate -- Don't get all rows
GROUP BY
    SDHour,
    SDMinute,
    SoftwareId,
    Software.FriendlyName,  --Need to group so we can use data in the select
    Software.MaximumSupportedUsers,
    Software.MaximumSupportedDevices,
    UserDivsor
ORDER BY
    OrderTime Asc,
    SoftwareId

This query was perfectly fine when we had a couple of hundred thousand rows. However now the data has grown to 42,000,000 rows it started to take aronud 10 seconds for the query to run for just a 20 day period.

Looking at the query plan it looks like because I'm using the MAX() on both the OnlineUsers and OnlineDevices. If I remove these then the time to execute drops to <1second if I put them back in it jumps to >10 seconds. NOTE Most of the time there is actually only 1 sample per 5 minutes

Any suggestions on how to avoid this either by modifying the query or addition of indexes. The only thing I can't do is change the output format.

The Indexes I currently have are

Clustered on
Id

Non-Clustered on
SoftwareId
SampleDate


texts: MS-SQL 2014 Log Shipping: Access permission question

texts:
Wonder if someone could provide some advice or point me in the right direction towards achieving the below objective please. Unfortunately I have not been very successful in finding a solution online.

We are at the process of implementing Log shipping between 2 SQL-2014 servers. The user access are enabled on our SQL servers via Windows Security Groups.

We would like one Windows Security Group (eg: WSG-1) of users running expensive queries be denied access only to say a single database (eg: database-A) on the primary server but be allowed access to:

1). The rest of the databases on the primary server and

2). The same database-A (standby/read-only) on the secondary server.

My initial plan was to deny SQL connect access to the WSG-1 on the primary server but grant WSG-1 all access on the secondary server. However, this did not work as it denied WSG-1 access to SQL altogether on the primary server. I used:

DENY CONNECT SQL TO [DOMAIN\WSG-1]

Wonder if a solution could be accomplished this way or if there is a different way to a solution.

The biggest challenge I find with Log-shipping is, any grant/deny applied at database level on the primary server is mirrored exactly over to the secondary server with the subject database left in non recovery mode. But we would like to vary the db level access permission between the primary and the secondary server for the subject database.

I find that the database role denied access on the primary server remain denied on secondary too for the group WSG-1. Not sure if my problem could be approached in a different way.


texts: Why do I get incorrect characters when decoding a Base64 string to NVARCHAR in SQL Server?

texts:
I've been looking at how to decode Base64 with SQL Server and after searching around many solutions online (some from here) seem to be based upon this sort of method.

SELECT CAST(CAST('Base64StringHere' as XML ).value('.','varbinary(max)') AS VARCHAR(250))

When I have ASCII text this works perfectly. However when I have the following French text it gets corrupted (presumably due to the restrictions of VARCHAR).

Où est le café le plus proche?
T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=

And provides the following output.

OÃ¹ est le cafÃ© le plus proche?

I thought the relatively simple fix would be to change the CAST to NVARCHAR but this results in corruption again.

SELECT CAST(CAST('T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=' as XML ).value('.','varbinary(max)') AS NVARCHAR(250) )

썏₹獥⁴敬挠晡꧃氠汰獵瀠潲档㽥

My search engine skills may be failing me, but I cannot seem to find someone else who has my problem.

Any thoughts?


texts: MySQL/MariaDB connection in asp code encodes wrongly charset utf8 at insert or update in data base

texts:
I can't get a simple update or insert to save special äöü characters. Instead they are being stored as Ã¤Ã¶Ã¼.

The website displays äöü characters correctly if they are already stored as äöü in the database using phpMyAdmin (or some other tool like HeidiSQL), but for some reason I can't use my code/connection to the database to store them properly. This is where I stand:

I added charset=utf8 to my connection string:

MM_Connection_STRING = "PROVIDER=MSDASQL;DRIVER={MySQL ODBC 5.1 Driver};port=3306;SERVER=localhost;UID=xxx;PWD=xxx;database=xxx;Option=3;charset=utf8"

I added accept-charset="UTF-8" to all my forms.

I am running MariaDB and IIS 10.

Server version:         10.1.13-MariaDB mariadb.org binary distribution
Protocol version:       10

The page gets proxied using Apache 2.4. I am running asp (classic) code.

Calling the MySQL server for character and collation, I get this:

MariaDB> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR
  Variable_name LIKE 'collation%';

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+

The code worked when I was using iso-8859-1 as charset.

Any help will be very much appropriated. Thank you.


texts:
This is the table I have created:

 CREATE TABLE Ticket_Type
 (
  t_type_id  NUMBER(1) PRIMARY KEY,
  CONSTRAINT check_t_type_id CHECK(t_type_id  > 0),
  t_type VARCHAR(20) NOT NULL,
  t_type_price NUMBER(4,2) NOT NULL,
  CONSTRAINT check_t_type_price CHECK(t_type_price > 0),
  t_type_start_date DATE NOT NULL,
  t_type_end_date DATE,
  CONSTRAINT check_t_type_end_date CHECK((t_type_end_date = NULL) OR (t_type_end_date >= t_type_start_date)),
  CONSTRAINT unique_t_type_t_type_start_date UNIQUE(t_type,t_type_start_date)
  );

I tried to create this table but I was getting the following error message:

Error report -
ORA-02436: date or system variable wrongly specified in CHECK constraint
02436. 00000 -  "date or system variable wrongly specified in CHECK constraint"
*Cause:    An attempt was made to use a date constant or system variable,
           such as USER, in a check constraint that was not completely
           specified in a CREATE TABLE or ALTER TABLE statement.  For
           example, a date was specified without the century.
*Action:   Completely specify the date constant or system variable.
           Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",
           which a bug permitted to be created before version 8.

I'm unable to figure out regarding where is the mistake I'm making.

I'm using Oracle version 12c SQL Developer.


texts: Find the highest graded student in each state, using Relational Algebra

texts:
Suppose I have a table of students, containing their ID, grade and state:

-------------------------
| id  |  grade  | state |
------------------------
|  1  |    83   |   CA  |
|  2  |    94   |   TX  |
|  3  |    92   |   WA  |
|  4  |    78   |   CA  |

And I want the ID of the students in each state with the highest grade (ex. 1, 2 and 3), how would I go about doing that?

I know how to find the maximum (can do the cross product (renaming as R1 and R2) and then select R1.grade < R2.grade for those who aren't the top, and subtract that from the original database). But I'm confused at how to do that for each state.


texts: use DBCC SHRINKDATABASE after rebuild / reindex

texts:
I have little free disk space.

Why shouldn't you use DBCC SHRINKDATABASE (DBName, TRUNCATEONLY) after every rebuild/reindex operation?


texts: How do I set MySQL's "character_set_server" and "collation_server" variables when creating a database?

texts:
I'm using MySQL 5.5.37.  How do I set the default "" and "" variables of my database when initially creating my db?  I'm logged in as the root user but my command is not cutting it, as you can see below

mysql> create database if not exists my_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)

mysql> use my_db
Database changed
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | utf8              |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | utf8_bin          |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

Notice that "character_set_server" is set to "latin1" and "collation_server" is set to "latin1_swedish_ci" despite what I specified in my intiail command. How do I change my initial command so that these values are utf8 and utf8_bin respectively?


texts:
I want to learn how to use Galera cluster replication (MariaDB).

Can anyone give an advice that I can start from specific point?
Is it compatible with vsphere?


texts: merge multiple data columns in one column from same table

texts:
Here are columns status , acc_date, rej_date, comp_date

*status*        *Acc_date*      *rej_date*    *comp_date*

Accepted         date value
Rejected                        date value
completed                                       date value
Accepted          date value

I want a result like this

*Status*       *date*
Accepted     date value
Rejected     date value
Completed    date value
accepted     date value

please help.


texts: how to insert multiple columns(around 40) into my sql database using java

texts:
i have a program to read the content of an excel file to my-sql db. i have 40 columns in excel and i created a table of 40 fields. and i want to import the data from excel into corresponding table filed.

My doubt is, is there any better way to write preparedStatement than the following;

con.prepareStatement("insert into insurance.bank_stmnt_ds values(?,?,?,...,?(40 times))");


texts: Trying to make sense of the InnoDB log file stats

texts:
I've been reading the high performance mysql book by Baron Schwartz and after reading about how important it is to set the log file size correctly, I thought I'd take a look at one of our servers at work.

I am trying to improve server performance by fixing the bad configuration from a former employee but I am new to this and I thought I'd ask. Server has around 37000 tables in total.

I am not sure if I should change log file settings or leave it alone. I do see write speed spikes in the server. Write speed almost never cruises in a range. It will do (as an example) 20mb at one point, 400kb the next, 40mb the next, 1mb and so on.

Any suggestions are welcome. Thank you for looking.

Show Engine INNODB STATUS

---
LOG
---
Log sequence number 18671411503063
Log flushed up to   18671411503063
Pages flushed up to 18671411503063
Last checkpoint at  18671411502723
Max checkpoint age    3478212404
Checkpoint age target 3369518267
Modified age          0
Checkpoint age        340
0 pending log writes, 0 pending chkp writes
53615 log i/o's done, 0.56 log i/o's/second

my.cnf

innodb_log_buffer_size = 8M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2


texts: Order by bug on SQL Server 2005 vs SQL Server 2012

texts:
I have a query generated by entity framework that returns wrong results on a SQL Server 2005 database (Microsoft SQL Server 2005 - 9.00.5000.00 (X64)) but good on SQL Server 2012 (Microsoft SQL Server 2012 - 11.0.2100.60).

Here is the query :

SELECT
[Project2].[tableAid] AS [id],
[Project2].[rank1] AS [rank]
FROM ( SELECT
    [Extent1].[id] AS [id],
    [Extent2].[rank] AS [rank],
    [Extent4].[rank] AS [rank1]
    FROM    [dbo].[tableA] AS [Extent1]
    LEFT OUTER JOIN [dbo].[tableB] AS [Extent2] ON ([Extent1].[tableAid] = [Extent2].[tableAid]) AND (896 = [Extent2].[tableBid])
    INNER JOIN [dbo].[tableC] AS [Extent4] ON [Extent1].[tableCid] = [Extent4].[tableCid]
)  AS [Project2]
ORDER BY [Project2].[rank] DESC

I've simplified it but the model is :

TableA
int tableAid
int tableCid

tableB
int tableBid
int tableAid
int rank

tableC
int tableCid
int rank

There's a many to many relationship between A and B and a 1 to 1 between A and C.

On SQL Server 2005, this query is sorted by the rank column of the results, not by Project2.rank. If I replace the [Project2].[rank1] AS [rank] in the SELECT by [Project2].[rank1] AS [whatever], results are correctly sorted. But since these aliases are generated by EF, I can't easily change them.
On SQL Server 2012, the query works correctly as is.

Does anyone know this bug? Is there a patch or some settings for SQL Server 2005 to avoid this problem?


texts: Get tp.address_from and tp.address_to from travel_point table

texts:
I'm building a view for get tp.address_from and tp.address_to but I can't get this works. This is the query I'm working on:

SELECT
    `tp`.`address_from` AS `address_from`,
    `tp`.`address_to` AS `address_to`,
    `bk`.`firstname` AS `firstname`
FROM
    (
        `book` `bk`
        LEFT JOIN `travel_point` `tp` ON(
            (
                (`bk`.`tp_from` = `tp`.`id`)
                AND(`bk`.`tp_to` = `tp`.`id`)
            )
        )
    )

But it's not working since I get always a set of empty results as picture below shows:

Between book and travel_point tables exists the same relation twice: tp_from is a FK on book and also tp_to is a FK and both points to travel_point.id maybe here is the problem and I don't see it yet. Anyway, I leave the SQL schema in this link because is a bit large, apologies for that but and I provide the same data I've for testing purpose.

What I'm doing wrong?

PS: You can take a look to data on this images I leave and as I said data is shared


texts: data fetch with multiple filters in a paginated way

texts:
We have a very huge table with around 10 partitions in our system. The table data has multiple references in other tables.
The database administrator has advised us to avoid performing any join query against the table. How can I efficiently fetch the data in a paginated way from the table where as the filter that I am passing  contains the references of other tables as well. The database is postgres.


texts: How does optimistic locking actually enforce re-read/update?

texts:
My understanding of optimistic locking is that it uses a timestamp on each record in a table to determine the "version" of the record, so that when the record is access by multiple processes at the same time, each has a reference to the record's version.

Then, when an update is performed, the timestamp is updated. Before an update is committed, it reads the timestamp on the record a 2nd time. If the timestamp (version) that it has is no longer the timestamp on the record (because it's been updated since the first read), then the process must re-read the entire record and apply the update on the new version of it.

So, if anything I have stated is not correct, please begin by making clarifications for me. But, assuming I'm more or less correct here...

How does this actually manifest itself in a RDBMS? Is this 2nd read/verification enforced in the application logic (the SQL itself) or is it a tuning parameter/configuration that the DBA makes?

I guess I'm wondering where the logic comes from to read the timestamp and perform a 2nd update if the timestamp is stale. So I ask: does the application developer enforce optimistic locking, or is it enforced by the DBA? Either way, how? Thanks in advance!


texts: Microsoft SQL Server 2008 R2 Developer Edition Data Tuning Advisor tool

texts:
I have Microsoft SQL Server 2008 R2 Developer Edition installed on my machine which has Win7 64 bit OS.
I am not able see Data Tuning Advisor tool in my SSMS. Before this installation my machine already had SQL server 2008 R2 express edition. I upgraded shared components as well but no success.

One thing I noticed while installing developer edition is that there was no option to choose "Management tools - Complete" it was only "Management tools - Basic". Is this an issue?

Can anybody help me getting this tool?


texts:
I have a dedicated MySQL server(8GB) with following configuration.

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
lower_case_table_names = 1
default-storage-engine=InnoDB
innodb_buffer_pool_size=5G
innodb_log_file_size=512M
query_cache_size=512M
table_cache=64
innodb_flush_method=O_DIRECT

#Replication

log-bin=mysql-bin
server-id=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1

binlog-do-db=test_db1
binlog-do-db=test_db2

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

I have 2 applications which are connecting to different databases (test_db1 and test_db2) and perform all kinds of operations to the respective dbs. Both applications are written in C# and close the MySQL connections from application side.

 if (Connection != null)
            {
                if (Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                    Connection = null;
                }
            }

The 2 applications were running and doing their jobs perfectly. We set up replication recently and (I believe after that only) now connections are sleeping for long time and application have problem. I am getting an error Two many connections after a certain time and both applications stop working.

show status like '%onn%';

Variable_name             Value
Aborted_connects                 4
Connections                    1818
Max_used_connections         99
Ssl_client_connects              0
Ssl_connect_renegotiates         0
Ssl_finished_connects        0
Threads_connected                6

I have checked different posts asked about sleeping connection issues. Majority of the posts gave the reply like client side(application) is not disconnecting connection correctly or to reduce the value of interactive time_out and connection_time-out. Both solutions are not working for me.


texts: Can partitioning help speed up inserts on a very large table?

texts:
Some background:

SQL 2012 Enterprise
OLTP table currently at 1B records
records increase 5-10M per day
SAN lun with flash storage
two indexes (one clustered identity int)

What we have noticed is a gradual slowing of INSERTs over time, which will eventually become an issue to the business if left unchanged.

The question:
We are planning to transition to use partitioning on this table soon, primarily for space management, but the main question here is whether partitioning can help improve the performance of INSERTs. (We know about performance increases for SELECTs already, please do not comment on that side of things if possible.)

I have done a fair amount of searching forums, etc, but have not found anything specific to INSERTs and partitioning. It seems reasonable to me that this would help maintain speed over time, given that we will partition on "date inserted" and that the INSERTs would always be directed to the most recent partition, but looking for confirmation.

Thanks all.

UPDATE:
To clarify: a staging table (for switching in) is not an option due to the OLTP nature and needing to read the data out immediately after inserting, both of which cannot be delayed awaiting a switch in.


texts: Autogrowth, shrink and database performance

texts:
I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it.

I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 MB to it? Now 10 MB is set.

I have so many small databases which really have data of about 50 MB. But it's physical size is about 900 MB.(So 850 is free space). I have not shrunk this database. As if I shrink it then again when data gets added autogrowth occurs.  But adding data to this database is rare. So I think it will not grow more than 200MB for next one year.
So should I shrink the database? If I keep it as it is? Should that cause any performance problem? Or having more free space(more than 90%) in database will cause any problem? I think most of the people think having large physical size will cause performance problem. So is it true or not?

SQL Server 2008 R2 Express database size limit is 10 GB. So is it physical size of both mdf and ldf file together? Because I need to consider this if I should shrink database at any point.


texts: SQL SERVER 2008 TVF OR CHARINDEX to search column with comma

texts:
ID    BlahColumn
1       2
2       NULL
3       2,3
4       4,5,6
5       1,2
6       1
7       1

Hello,  I am using the following as part of the where clause and using charindex is not working  correctly to search the above comma delimited column. i am planning to fix this by creating a separate table but for now i need a workaround. I Would really appreciate your help:

WHERE(@Param IS NULL OR
      CHARINDEX(','+@Param+',', ','+CAST(BlahColumn as VARCHAR(255))+',') > 0)))

IF @Param = '1,2' then it should return rows 1,3,5,6,7 and if it's something like 3 then it should return row 3.

my real stored procedure looks like this

 (SELECT DISTINCT
         S.ID, S.ServiceDateTime, S.ReasonForFailure, S.ActionTaken, S.VerificationPerformed, S.TSBNumber, S.ISANumber, SS.SystemFullName AS SystemName,
                      U.FullName AS TechnicianName, S.ErrorDescription AS ErrorDescription, S.ClosedDate, S.ServiceRequestClosed,
                      S.ServiceHours, S.ServiceMinutes, S.PartDescription, S.RequestAddParts AS AdditionalPartsRequested, ISNULL(S.Reconciled, 0) AS Reconciled,
                          (SELECT     FullName
                            FROM          dbo.[User] AS U1
                            WHERE      (ID = S.CreatedBy)) AS CreatedBy, S.CreatedDate,
                          (SELECT     FullName
                            FROM          dbo.[User] AS U1
                            WHERE      (ID = S.UpdatedBy)) AS UpdatedBy, S.UpdatedDate
   FROM ServiceEntry AS S INNER JOIN
    dbo.[User] AS U ON U.ID = S.TechnicianID INNER JOIN
                      dbo.System AS SS ON SS.ID = S.SystemID  LEFT OUTER JOIN
                      dbo.ServiceEntryErrorCode AS SEEC ON SEEC.ServiceEntryID = S.ID
   WHERE (S.RunLogEntryID is not null) AND (S.Reconciled=@ReconciledValue) AND (ServiceDateTime between @StartDate and @EndDate)
   and S.ID IN(SELECT SEP.ServiceEntryID FROM ServiceEntryPart SEP inner join Part ON SEP.PartID = Part.ID
   WHERE
  (@ServiceTypes is null or
   CHARINDEX(','+@ServiceTypes+',', ','+CAST(SEP.ServiceTypeIDs as VARCHAR(255))+',') > 0)
   AND
(@ActivePart is null or @ActivePart = Part.Active)

@gbn im using your solution first im trying to just run the query and then will include your solution in my above query:

The following works great,
    declare @param nvarchar(100) = null
    SET @param = '1,4'

SELECT *
FROM
  ServiceEntryPart M
  CROSS APPLY
  dbo.SplitStrings_Moden(M.ServiceTypeIDs, N',') M2
  JOIN dbo.SplitStrings_Moden(@param, N',') P ON M2.Item = P.Item

SO i  include the above query in my stored procedure and do something like this but, what im trying to do is find all service entries which have service parts, and the where clause is optional, so for instance if my @servicetypes param is NULL then I would not search against the parameter but only see if the service entries exist in the service entry parts. if the parameter is not null then i will search against the service type ids. please help, i have tried and not been able to find the correct result

S.ID IN(

SELECT DISTINCT ServiceEntryID
FROM
  ServiceEntryPart M
  CROSS APPLY
  dbo.SplitStrings_Moden(M.ServiceTypeIDs, N',') M2
  JOIN dbo.SplitStrings_Moden(@ServiceTypes, N',') P ON M2.Item = P.Item
  INNER JOIN Part ON M.PartID = Part.ID
  WHERE @ActivePart is null or @ActivePart = Part.Active)


texts:
I am currently restoring a database and I tried to restore the database three times. The third time I succeeded but unfortunately it seems that the other two attempts at restoring the database are still running but never finish.

I have these two blue windows on the pgAdmin screen which I can not remove or stop executing. I tried a lot of things, I even uninstalled PostgreSQL two times but the restoring windows appeared again.

I am using Windows 8 and PostgreSQL 9.6.


texts: Colloboration between T-SQL Table and Excel Sheet(s)

texts:
Let's say I have a T-SQL table with 20-30 columns and 2-3 millions rows (huge table). I want to allow a user to update this data directly (the solution must work for different tables). I generally have the following options:

to create simple web interface displaying only part of the data via paging
to create desktop application which can download the data (on parts) and sync it when it is changed

This will probably work, but the userwill be force to learn how to work with the interface, and the application functionalities will consume a lot of time to develop - it will be easier to use tool that is already created and the user is familiar with (for example excel).

I know I can export/import data between T-SQL table and excel file, but is there a way to build some kind of collaboration between them - for example the excel to call a stored procedure which is returning the data (it will be nice if I can send a hash of the data or the rows in order to return only the rows that have been changed). And to call a stored procedure which can upload/delete/insert data in the table.

If not, I guess I am going to work with files - download the file, edit the data in excel, then upload it, if there aren't other ways?


texts: Is Cassandra's manual node addition/removal tasks tedious and practical in a large cluster?

texts:
I am evaluating using Cassandra as a large key-value store over about 500 commodity machines.  AFAIK, Cassandra requires manual intervention during permanent machine failures (removing the deadnode, adding new machines).  I would think that the machine failure rate is high enough to overload the administrator.  So is it practically feasible to run Cassandra in such a large cluster?

Thanks,


texts:
I have a database storing user login credentials of my application in a Table. When I backed up and restored my database to another server, the restore is successful but the login is timing out (SQL Server timeout). Even though I increased the timeout, still no luck.Can anyone suggest what can be the reason for that.

Can re-indexing the database help me in the new server?


texts:
I tried to take full backup in mysql using enterprise backup by using the command

    mysqlbackup -u root -p --backup-dir=g:\full_bck backup

But I could not take backup of a single database , I tried it using --include database_name.

Please help me to sort out this.


texts: How to keep history of SQL Server stored procedure revisions

texts:
Note: I am not asking about full version control.

Is there any way automatically to keep a history of stored procedures on SQL Server.

Similar to how Google Docs automatically keeps a history of versions of documents and Wikipedia automatically keeps a history of versions of articles.

I don't want users updating stored procedures to have also to maintain a repository of stored procedures. This is too much work and people won't do it.

Hopefully this is something I can turn on in SQL Server...

(And by stored procedures really I mean functions, triggers, etc. Basically everything under Programmability.)

I posted to https://stackoverflow.com/questions/14522224/how-to-keep-history-of-sql-server-stored-procedure-revisions first cos I suspect it would get more views there.


texts:
We are working on a web application, not yet accessible to users. My boss noticed that newly created records get an ID of over 10 000, even though we only have under 100 records in the table. She assumed that the web interface for some reason creates over a 100 times more temporary records than actual ones (and deletes them) and that this can lead us to running out of range within a few months of release.

I don't think she is correct about the cause of the ID inflation (the colleague who can answer this is on vacation, so we don't know for sure), but let's assume that she is. She said that she'd hate to use a bigint column, and that she'd like us to stop autoincrementing the ID column and write server-side code which chooses the first "unused" integer and uses it as an ID.

I am a computer science grad student with little practical experience, filling a junior developer role. She has years of experience of managing all of our organisation's databases, and designing most of them. I think that she's incorrect in this case, that a bigint ID is nothing to be afraid of, and that mimicking the DBMS functionality smells of an antipattern. But I don't trust my judgment yet.

What are the arguments for and against each position? What bad things can happen if we use a bigint, and what are the dangers of reinventing the wheel autoincrementing functionality? Is there a third solution which is better than either one? What could her reasons be for wanting to avoid an inflation of ID face values? I'm interested in hearing about pragmatic reasons too - maybe bigint IDs work in theory, but cause headaches in practice?

The application is not expected to handle very large amounts of data. I doubt that it will reach 10 000 actual records within the next few years.

If it makes any difference, we are using Microsoft SQL server. The application is written in C# and uses Linq to SQL.

Update

Thank you, I found the existing answers and comments interesting. But I'm afraid you misunderstood my question, so they contain what I wanted to know.

I'm not really concerned about the real reason for the high IDs. If we can't find it on our own, I could ask a different question. What I'm interested in is to understand the decision process in this case. For this, please assume that the application will be writing 1000 records per day, then deleting 9999 of them. I'm almost sure this is not the case, but this is what my boss believed when she made her request. So, under these hypothetical circumstances, what would be the pros and cons of either using bigint or writing our own code which will assign IDs (in a way which reuses the IDs of already deleted records, to ensure there are no gaps)?

As for the actual reason, I strongly suspect that this is because we once wrote code to import data from another database, as a proof of concept that a later migration can be done to a certain extent. I think my colleague actually created several thousand records during the import and later deleted them. I have to confirm if this was actually the case, but if it is, there is not even need for action.


texts: How can I define and declare a good PRIMARY KEY for my dataset?

texts:
I have a large historical dataset that has the following attributes:

productCode: Alphabetic code used to identify a product name, e.g., 'LE' (limited edition), 'LI' (limited inclusion)
productMonth: The month of the product manufacturing (multiple products can be made per month)
productPrice: the price level for a particular product
dateSold: the day the product was sold

Note: A productCode can have multiple productMonths and multiple productPrice levels. For example, for a productCode 'LE':

Note 2: Once a product (e.g., 'LE', 'Mar 16') is sold at a certain priceLevel (e.g., '2.0') on a certain day, it is no longer available at that priceLevel for the rest of such day. It will be available at the priceLevel again the next day.

This data will be pulled for analysis and the database will also be updated daily. As a beginner I want to make sure I employ best practices. From my research, I have been told that every table should have a primary key. In my case (my dataset below), I'm not sure how to best construct one.

Sample data

In order to exemplify the pieces of information in tabular form, I have provided the following sample data:

 productCode dateSold productMonth price randomData
 ----------- -------- ------------ ----- -----------
 LI            Dec 16       Mar 16  1.65        0.05
 LE            Dec 16       Mar 16  1.65        0.05
 LE            Nov 16       Mar 16  1.65        0.04
 LE            Nov 16       Apr 16  2.00        0.03
 LE            Nov 16       Apr 16  5.00        0.01

Considerations

I believe I need a PRIMARY KEY (PK) for the case of UPDATE/INSERT into the database, I'd only want to INSERT to the database if there wasn't an existing row already (having duplicate rows would skew analysis of data). I believe a PK can help identify duplicates if I construct it based on the existing data and check as I INSERT into the database.

A unique ID can be made for every item if I combine the productCode, dateSold, productMonth and price columns. This is because once the product (productcode, productmonth, price) is sold on a particular day (dateSold), it can not be sold again for the rest of the day.

However, I'm not sure if this is the proper way of building a PK or if there is a better key for my particular dataset. Any guidance would be greatly appreciated.


texts: threshold replica lag for secondaryPrefered mongod

texts:
Example:
3 node on RS with mongos and mongoc(cluster)
On one Secondary node(on RS) we have replica lag, e.g. - 1h.
So, if my read preference is - secondaryPrefered and if I read data from RS, can it happen that I will read data from a node that has a replication lag?
Or does mongodb have some sort of protection mechanisms?


texts: MySQL, RAM disk for tmpdir - how to figure out what size it should be?

texts:
We regularly run a few large SQL queries to generate reports.
It seems that the bottleneck is the amount of time that is spent writing to the tmpdir, which is currently on disk.

We are upgrading to a larger server and I would like to setup tmpdir to use a RAM disk, but am not sure what a reasonable size would be to accommodate these reports.

Is there somewhere in MySQL that might log the most amount of space tmpdir it uses, or somewhere else I should be looking to calculate this.

Thanks in advance for your help.


texts: Are there any (functional or nuanced) differences between rebuilding an index and a full drop/create? If so, what are they?

texts:
I have been working on index fragmentation for indexes that are heavily used and causing performance decreases. Our standard job checks fragmentation levels and reorgs if >5% and <30%, and rebuilds if >30%. As far as I have found these don't seem to be functionally different but I'm being asked if a drop/create is more effective as a client tried this on their own and believes they saw greater improvement. Is this truly possible?

This is comparing a full DROP and CREATE vs ALTER INDEX REBUILD on a non-clustered index.


texts: Azure SQL Database Restore after Server Failure - Where are the Backups?

texts:
According to this article, an Azure SQL backup can be used to "Restore a database to another geographical region. This allows you to recover from a geographic disaster when you cannot access your server and database. It creates a new database in any existing server anywhere in the world."

My question is this. In the event the server my database is sitting on fails how would I be able to access the most recent backup to restore it to another server? For example, if I wanted to restore a copy of my database now, I would select my database and then the Restore option from the menu at the top of the page. However, if I've lost my server (and therefore my database), how do I get access to the backup(s) of the database(s) that sat on the server that was lost?


texts: Where clause for multiple rows and columns

texts:
I have a UDT that receive 2 parameters which is ID and Quality

CREATE TYPE [dbo].[ItemUdt] AS TABLE(
[ID] [int] NOT NULL,
[Quality] [int] NULL
)

This UDT will be use as condition to filter records from a table

@ItemList AS ItemUdt READONLY

What i trying to do is get the records that match both ID and Quality

SELECT * FROM tbl_Item
WHERE [ID] IN (SELECT ID FROM @ItemList)
AND [Quality] IN (SELECT Quality FROM @ItemList)

However, above query will match all listed ID that consists of all Quality from the UDT.
For example, if the UDT consists of (1001, 'Good'),(1002, 'Bad'), the result will display all the ID with 1001 and 1002 together with Quality of 'Good' and 'Bad'. But my expected result is as below:

SELECT * FROM tbl_Item
WHERE ([ID] = '1001' AND [Quality] = 'Good')
OR ([ID] = '1002' AND [Quality] = 'Bad')

Is there any way I can write a query that match above format?


texts: How to influence files (pre)allocation steps (max growing value) in MongoDB 3.4 with WiredTiger

texts:
We have in environment deployed some really small DBs (5GB) as directory per DB.Under dirs is LVM with XFS, where we have implemented logic for "brake" when DB space should be full-filled (all is due to some strict disk quotas - not really interesting at all I think...).
Our solution runs on MongoDB v3.4.19 on CentOS 7.5. Everything looks ok.

Some of DBs has more than 60% used disk space by single collection and in our scenario we encountered that, while restoring 2.3GB dump (all into 5GB database dir/=disk, the preallocation after 2GBs asks OS for another space in (I think) power of 2 strategy (or something very similar).

This situation hits ours triggers, and eventually should lead to lock of DB (which is still better than fall of whole cluster because of no space left).
Basically same situation we have with 10GB DB. (And in some way we can say that similar situation can occur with literally any capacity...)

My point of question is: Am I able to influence steps (maximum size lower than ~2GB) of (pre)allocation growing of Mongo/WT for restoring (only) or at all?

(Performance impact is not highest prioritized in this case.)

I've found some promising possibilities through db.adminCommand and configStrings for storage engines, but I was not able to affect or list some of possible parameters nor change something to turn things to good...
Especially file_extend parameter of WT through

db.adminCommand( { setParameter: '1', wiredTigerEngineRuntimeConfig: { <parameter> } } )

looked promising but I didn't find way to pass it to cluster...
Some of tries ended with error 22 - Invalid argument, but I wasn't able to find more about...

The other found, seemed suitable for MMAP engine only - newCollectionsUsePowerOf2Sizes=false.

This makes restoring of dumps (eventually backups) into cluster much more bothersome...

Hope that issue is clearly explained and any hint/tip would be appreciated...
If some additional info needed, let me know.

Update:
I've done screenshots where (I believe) is clearly visible what I'm talking about above... There are $ ls output (upper left), $ df output (upper right) and $ mongorestore progress (bottom left). (Better once to see than thousand times to hear (read)...)

Beginning

First alloc (to 1.4GB - used 650MB)

Second alloc (to 2.4GB - used 1.1GB)

Third alloc (to 4.4GB - used 2.1GB)

At the restore end (still allocated 4.4GB - used 2.3GB)

Back to normal (extra space deallocated)

Thank you.

Zdenek


texts: SQL Server log Size increasing while in Simple recovery mode

texts:
Is it possible to locate the active transaction, it was not listed in DBCC opentran, the log file keeps getting bigger.

Database is in Simple recovery
tried shrinking the database - no luck

Also we have restarted the SQL service, no improvement the log file size keeps increasing.


texts: Where do Database Snapshots store the information what pages have been copied to the snapshot files?

texts: Database snapshot files are sparse files. Only when a page is modified the sparse file is being written to. Reads of the snapshot must be able to know whether the sparse file contains a copied page or not. How does SQL Server know where to read from? Where is this information stored?


texts: How to simulate production workloads on a dummy database?

texts:
We are planning to enable Multi AZ on our production database which uses Amazon RDS as a managed service. Our Database is using SQL Server 2012 standard version.

Before enabling that, I wanted to simulate our production workload on a dummy database to check how the read write latency are getting affected after making the dummy database as Multi AZ.

Whats the best way to do the above ?


texts:
We document our database by storing descriptions for tables, columns, stored procedures etc. in the MS_Description extended property. It's easy enough to handle by scripting, but what I miss is some sort of a right-click command like "Document" on anything in the SSMS's object tree which would take you immediately to MS_Description (or its placeholder if it isn't yet created for that object).

You know, not "right-click -> properties -> extended properties -> type "MS_Description" -> type description text" but just "right-click -> Document -> type description text".

Anybody knows of an extension like this?
Thanks!


texts: Database Mail with SSL (like GMAIL SMTP) not working

texts:
Is anybody successful sending email via MS SQL Server Database Mail while secure connection is set ON in account settings?

I'm trying different SMTP servers with SSL to set up - like Gmail - and none of them is working. Yes, I have "unsafe access" ON in Google settings.

Seems to me like SSL in MS SQL Server is not working properly. When I try non-secure SMTP server it's working fine.

The error goes like this (don't know how / where to find more detailed description):

Please, don't ask me whether password it typed correctly - it is!

I've tryed different Gmail accounts on different servers - with the same unssuccesful result.


texts: How can I rewrite where col % 5 = 4 in SQL Server. The reason to rewrite this is because I am selecting some columns from the table based on this condition. Because of % used in where it is causing index scan instead of seek. Please help me out with this.


texts: Intentionally recursive instead of insert trigger does not want to nest

texts:
I have been struggling to get an instead of trigger to recursively call itself.

The idea is that when a row gets inserted into my table the trigger is placed on, based upon certain conditions evaluated at that point, the row data should be modified and then inserted. Also when these conditions are satisfied a second row should be inserted into to the same table also causing the trigger to fire and evaluate the newly inserted values as before and modify the data and insert a second row as before and so on and so forth until the TRIGGER_NESTLEVEL() reaches a specific value (always less than 3).

Try as I might, I cannot seem to get the trigger to execute itself again on the second insert.

Here is some sample code I wrote to attempt to illustrate my problem:

--delete Tester1
--go
--drop table Tester1
alter database Playground
set recursive_triggers on

go

create table Tester1(ID int identity, Val int, String varchar(255))

go

Create trigger [tr_Tester1]
on Tester1
instead of insert
as
Begin

    if(Select TRIGGER_NESTLEVEL()) >= 3
    Return

    --Insert the initial row (modify text based on criterion)
    insert into Tester1
    select
        inserted.Val,
        case
            when inserted.Val%2 = 0
                then 'modified ' + inserted.String --Insert modified text
            else
                inserted.String --Insert the original text
        end
    from inserted

    --Insert a second row if the criterion was satisfied
    insert into Tester1
    select
        inserted.Val * 2, --Insert a new value that should satisfy the condition
        'some more text ' + CAST(TRIGGER_NESTLEVEL() as varchar(5))
    FROM inserted
    where
        inserted.Val%2 = 0

End

insert into Tester1(Val, String)
values(2,'some text')

select * from Tester1

Results I'm seeing:

Results I was expecting to see:

I have checked SELECT DATABASEPROPERTYEX('Playground', 'IsRecursiveTriggersEnabled') and select * from sys.configurations where name like 'nested triggers' and both are enabled even though according to MSDN:

  "These actions can initiate other triggers, and so on. DML and DDL
  triggers can be nested up to 32 levels. You can control whether AFTER
  triggers can be nested through the nested triggers server
  configuration option. INSTEAD OF triggers (only DML triggers can be
  INSTEAD OF triggers) can be nested regardless of this setting."

Also, I am aware that in my example my logic would always result in recursion (Well, theoretically at least). This is however only to illustrate the problem. In the actual trigger this is not necessarily the case.

Please can somebody point what I am missing or if I am misguided in my approach?


texts: Select records in one table but not in another, when the second table is not matching on primary key

texts:
I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a vestige id column (which is actually unused in the application at the moment). The cardinal table's primary key is id. The column which should join the tables is sku. These are the tables (relevant fields only, there are other fields that I didn't paste here):

mysql> describe list;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id              | int(11)      | NO   |     | NULL    |       |
| name            | varchar(63)  | YES  |     | NULL    |       |
| sku             | bigint(20)   | NO   | PRI | 0       |       |
+-----------------+--------------+------+-----+---------+-------+

mysql> describe cardinal;
+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| id               | int(11)     | NO   | PRI | NULL    | auto_increment |
| sku              | bigint(20)  | YES  | MUL | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+

Here are the amount of records in each table:

mysql> SELECT count(*) FROM list;
+----------+
|  2677513 |
+----------+

mysql> SELECT count(*) FROM cardinal;
+----------+
|   970924 |
+----------+

Every record in the cardinal table has a valid entry for the sku column, and all of those valid entries do exist in the list table. There do exist some dupes for sku in the cardinal table.

I need all the records in the list table which do not have a corresponding entry in the cardinal table. However, all the methods that I'm trying are returning either all the records from the list table, or no records at all!

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;
+----------+
| count(*) |
+----------+
|  2677513 |
+----------+
1 row in set (0.49 sec)

mysql> SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

The first query obviously doesn't know to match on the sku column. But the second one looks like it should work based on other answers that I've seen online.

I expect about 1700000 - 2000000 rows to be returned. But certainly not 0 or the full table. How should I word the query? This is MySQL 5.5.33 running in Amazon RDS.


texts: Read READ COMMITED transaction advantage in MySQL, high performance object model

texts:
I am writing high performance object model with READ COMMITED locking.

My question is if there is any reason to starting READ ONLY transaction for read only operations vs. standard SELECT not enclosed in any transaction.

Update:

MySQL 5.6 changes InnoDB autocommit SELECT into read-only transactions, boosting its performance a bit.


texts: How to set local_listener to null upon creating database

texts:
In Oracle, I am creating a database with a silent install using DBCA and a template. The template does not define local_listener.

<initParam name="local_listener" value=""/>

After the database is create, it has a value for local_listener = LISTENER_dbname. However, I don't want this parameter to have a value when it is created. How can I ensure that this parameter is created with a null value?