texts: Where are the username and password stored in postgresql?

texts:
I'm using the PostgreSQL database. In it I create users. So I want to know

Where are the User name and Password stored?
Whether the Password is stored as plain text or encrypted data?
Whether root user is able to read the password or not?


texts: How to index for a group by with columns from different tables?

texts:
Is it possible to create an index from columns of 2 different tables?
If not, how do we optimize a query that does a group by on columns of 2 different tables?

For example:  the following statement

explain select p.name from Person p join JobStatus js
group by p.name,js.job_tickets
order by p.name,p.lastname, pm.job_tickets;

Uses indexes if I have for (p.name,p.lastname) and for job_tickets.
But I have noticed that on other queries and with more than one joining table the index is omitted.


texts:
I am supposed to be testing a piece of software that uses mssql express as the backend for data storage. When it is creating its databases, it tries to create them in my home directory that is on a network share, which does not work. I.e. it does

create database [db] on primary (name=N'db' filename=N'g:\my docs\app\db\db.mdf' ...

and a bunch more of parameters.

I tried to disconnect the computer from the network, then it tried to run

create database [db] on primary (name=N'db' filename=N'\app\db\db.mdf' ...

which also did not work.

Basically my question is then: How can I convince mssql express to use another base directory when creating new files?


texts: Find clients who bought more than 1 million units in 3 consecutive months

texts:
I want to find the client's code (cCode), who bought more than 1 million units, in three consecutive months.

Here is my data:

gDate        cCode        buy
2014-10-22  15108164    1653410
2014-12-22  15108164    1653410
2014-10-22  16100286    1255720
2014-11-22  16100286    1739850
2014-12-22  16100286    1255720
2014-09-22  12400573    1336000
2014-10-22  12400573    1336000
2014-12-22  12400573    1336000
2014-12-22  12600125    3592800
2014-10-22  12600125    1266200

where cCode is the client's code, or ID.

Result must:

------------------------------------------------------------
cCode       2014-09-*   2014-10-*   2014-11-*   2014-12-*   |
------------------------------------------------------------
15108164        -           Yes         -           Yes     |
16100286        -           Yes         Yes         Yes     |  <- OK
12400573        Yes         Yes         -           Yes     |
12600125        -           Yes         -           Yes     |
------------------------------------------------------------
Answer is(cCode) : 16100286

I update function

DELIMITER $$

CREATE FUNCTION `milad`(D date,I int ,mMonth int ,minBuy int) RETURNS tinyint(1)
BEGIN
DECLARE result TINYINT Default 1 ;
DECLARE m_counter INT Default 0 ;

myloop: WHILE m_counter < mMonth DO
 if((SELECT count(cCode) as a FROM milad WHERE
    year(gDate) = year(DATE_ADD(D,INTERVAL m_counter month))
   and month(gDate) = month(DATE_ADD(D,INTERVAL m_counter month))
   and buy>minBuy
   and cCode=I)=0) THEN SET result = 0;
 END IF;
 SET m_counter = m_counter+1;
END WHILE myloop;

IF(result=1)
 THEN RETURN true;
 ELSE RETURN false;
end if;
end


texts: media family on device is incorrectly formed. SQL Server cannot process this media family

texts:
I am trying to restore a .BAK in SQL server but get the following error:

  Msg 3241, Level 16, State 7, Line 1 The media family on device 'c:\glyn\JA.bak' is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

I have tried restoring using 2012, 2008 and even 2005 but nothing works, I have used the following query and through the Tasks >> Back up but nothing works, here's my SQL:

RESTORE DATABASE JA FROM DISK='c:\glyn\JA.bak'

Does anyone have any suggestions? I've read that the bak could be corrupt, it was sent to my by another developer on DVD who is working on the project.


texts: Delete query does not appear to open space on the SQL Server

texts:
Our SQL Server (version 2012) is housed on a cloud server, for which we pay whenever we need additional space. We determined that we could delete some of the data to prevent the need for continually buying more storage.

The delete queries did not appear to open space - the size of the drive and available space doesn't change.

It is my understanding that even though available space has not changed, available space is created by the delete query. So new records imported can populate places where data was deleted. Is this correct?

I understand the Shrink Database is not a good choice.

Should I run -  sp_clean_db_file_free_space based on the following advice?

http://www.experts-exchange.com/questions/27905050/Freeing-up-disk-space-by-deleting-records-in-a-SQL-Server-2008-table.html


texts:
When we write the following:

allocate channel ch1_tape type sbt;
set controlfile autobackup format for device type sbt to '%F';
restore controlfile  from autobackup;
RELEASE CHANNEL ch1_tape;

By what variables does RMAN search the backups? I think just by DBID, am I wrong?

Thank you very much!


texts:
I have 1 primary, 1 secondary and 1 arbiter instance in my mongo cluster and no sharding. The priority of secondary is lower than priority of primary to keep the same machine as primary.

During a very high load today, I saw 1176 queries in db.currentOp(). All of these queries except 1 are waiting for some lock. And these queries are on various collections (not just on 1 collection). One such query waiting for the lock is

        {
        "desc" : "conn802243",
        "threadId" : "0x2055e1a0",
        "connectionId" : 802243,
        "opid" : 946990251,
        "active" : false,
        "op" : "query",
        "ns" : "db.collection",
        "query" : {
            "foo" : "bar",
        },
        "client" : "10.240.0.14:49355",
        "numYields" : 0,
        "locks" : {
            "Global" : "r",
            "MMAPV1Journal" : "r"
        },
        "waitingForLock" : true,
        "lockStats" : {
            "Global" : {
                "acquireCount" : {
                    "r" : NumberLong(2)
                }
            },
            "MMAPV1Journal" : {
                "acquireCount" : {
                    "r" : NumberLong(1)
                },
                "acquireWaitCount" : {
                    "r" : NumberLong(1)
                }
            }
        }
    }

The only query which is not waiting for the lock is a "dbstat" query which was fired by the arbiter("client" is the arbiter's ip in the below sample) and is running for more than 5 seconds.

{
        "desc" : "conn909",
        "threadId" : "0x7f53dc0",
        "connectionId" : 909,
        "opid" : 946985942,
        "active" : true,
        "secs_running" : 5,
        "microsecs_running" : NumberLong(5239773),
        "op" : "query",
        "ns" : "tb_dev",
        "query" : {
            "dbstats" : 1
        },
        "client" : "10.240.0.3:44403",
        "numYields" : 0,
        "locks" : {
            "Global" : "r",
            "MMAPV1Journal" : "r",
            "Database" : "R"
        },
        "waitingForLock" : false,
        "lockStats" : {
            "Global" : {
                "acquireCount" : {
                    "r" : NumberLong(2)
                }
            },
            "MMAPV1Journal" : {
                "acquireCount" : {
                    "r" : NumberLong(1)
                }
            },
            "Database" : {
                "acquireCount" : {
                    "R" : NumberLong(1)
                }
            }
        }
    }

Also, this looks to be a db level lock even though I am using 3.0(with mmapv1 as the storage engine).What is causing this db level lock to be held for so long?


texts: How to support custom fields for an entity and list them as columns?

texts:
I am currently working on a system which supports "custom fields" for users.

I will illustrate this with a simplified example of this database schema:

users
--------
user_id
name
age

custom_fields
----------------
custom_field_id
custom_field_name

user_has_custom_field_value
------------------------
user_id (FK to users table)
custom_field_id (FK to custom_fields table)
custom_field_value (column that contains value of custom field)

This schema supports creation of custom fields to store information as per what the customer wishes.

However, I want to list all users along with their custom fields in the query output (or by using some PHP logic)

|  user_id    |    name     |    age    |    fav_sport    |    payment_mode   |    bank
----------------------------------------------------------------------------------
     3              John         28          Football           SWIFT             Chase
     6              Jane         24          Swimming           Cash               BOA

fav_sport, payment_mode and bank are custom fields created which are represented as 3 rows in the custom_fields table.

So, the custom_fields table looks like this :

custom_field_id     |      custom_field_name
-----------------------------------------------
       1                     fav_sport
       2                     payment_mode
       3                     bank

and the user_has_custom_field value looks like this :

user_id    |        custom_field_id     |       custom_field_value
----------------------------------------------------------------------
   1                       1                          Football
   1                       2                           SWIFT
   1                       3                           Chase
   2                       1                          Swimming
   2                       2                           Cash
   2                       3                           BOA

What is the best way to achieve this with minimum queries and without taxing the database ?

The only solution I can come up with is by using a query inside a for loop in PHP to fetch the custom fields (which will definitely be heavy). What are more efficient solutions to do this ?

Suggestions welcome for MySQL based, PHP based, Stored Procedures etc.


texts:
I am trying to move close 88101840 rows from one table to another. To handle log growth on the server looking at doing 10000 rows at time. Please have a look at the query below. Just want to find if this way is good or would there be a better way to do it.

DECLARE @Archive_Rows INT;

SET @Archive_Rows = 1;

WHILE (@Archive_Rows > 0)
  BEGIN
    DELETE TOP (10000) Audit
    OUTPUT deleted.*
    INTO AuditArchive
    WHERE EVENTYEAR = '2016';

    SET @Archive_Rows = @@ROWCOUNT;
  END


texts: Availability Groups - Can async replica delay failover of sync replica?

texts:
Availability Groups SQL Server 2012.

Node 1 and Node 2 in primary data centre - sync mode for HA
Node 3 in dr data centre - async mode for DR

If a manual failover is performed between node 1 and node 2 can the failover be delayed if there is high "log_send_queue_size" value to node 3?

I would hope not but also don't understand how SQL Server keeps track of the LSNs which still need to be sent when failing over.


texts:
I am connecting to an Oracle DB and return data with Excel 2010. I have setup the connection in my workbook and my connection string looks as follows,

Provider=OraOLEDB.Oracle.1;Password=jasonpc;Persist Security Info=True;User ID=jasonpc;Data Source="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))";Extended Properties=True

I am pulling data from a table called TBLPRICES and the connection properties form looks like this,

As you can see in the picture, the password is saved but everytime i refresh the data i get the following popup, asking for a password.

But when this popup appears, the password is already in the password text box and when i press ok, then the data refresh. But as far as i understand when you save the password in the connection properties then the OraOLEDB Logon box should not appear. What can i do to make the logon box not to appear when i refresh the data. Is there certain settings that i need to tweak or check?


texts: SQL Server '12 Using RAM equal to 2x size of the only user database

texts:
Only user database on instance is ~8.5GBs.
MAX Mem set to 16GBs, which is ALL being consumed.
Virtual server has 24GBs RAM and 2 vcpus
Insert Poltergeist meme "What's happening?!"

***Side notes****
2 vcpus have low avg utilization(<50%).
Server hosts app (using ~500MBs of RAM).
Indexes are rebuilt based on size(page count >= 1000) and avg_fragmentation(>=50%).
Thought Tempdb may be issue but not seeing wait stats to indicate.
Top 10 expensive queries aren't using much CPU, RAM(logical reads), or being executed over and over.
Wait stats show ASYNC_IO almost with 1-to-1 wait count per second, but these are coupled with high wiats for backup related waits...


texts:
I am trying to solve this problem from hackerrank
this far I've written this solution

select Start_Date, min(End_Date)
from
   (select Start_Date from projects where Start_Date not in (select End_Date from projects)),
   (select End_Date from projects where End_Date not in (select Start_Date from projects))
where Start_Date < End_Date
group by Start_Date
order by datediff(day, min(End_Date), Start_Date) desc

But it shows that this answer is wrong though when I compare I get the results I planned to get. I'd much appreciate a little help to find out where I am wrong.


texts: Should I created a different table to hold a big text / varchar field in MySQL 5.5.61 with Antelope file format?

texts:
I read that newer version of MySQL save the big text fields apart from the rest of the row, so we don't have to bother about it.

However, how about an older version, specifically MySQL 5.5? Is it good for speed and performance purposes to save my text field in a different table?


texts: Reporting Services 2016 Multiple Email Accounts

texts: Is there a way to configure SSRS 2016 to be able to send email from more than one email account?  I am looking to setup something that is department specific for our organization.  Right now its defaulting to one department and it doesn't really fit our growth plans.


texts: Strategies for long term data growth optimization for a query with an index on a joined table

texts:
Given two simple, contrived example tables
create schema test;

create table test.exchange (
  id serial primary key,
  created_at date not null
);
create index on test.exchange(created_at);

create table test.exchange_content (
  id serial primary key,
  content text not null,
  exchange integer not null references "exchange"(id)
);
create index on test.exchange_content(exchange);

with a lot fake data
INSERT INTO test.exchange(id,created_at)
SELECT id, NOW() + (random() * (NOW()+'360 days' - NOW())) + '30 days'
FROM generate_series(1,10000000) id;

INSERT INTO test.exchange_content(id,content,exchange)
SELECT id, md5(random()::text), id % 10000000 + 1
FROM generate_series(0,30000000) id;

Attempting this query...
select count(*) from exchange
left join exchange_content ec on exchange.id = ec.exchange
where created_at > '2021-10-14';

runs a sequential scan on exchange_content (for Postgres 9.6)
With these tables and large queries frequently being executed on them together, what are the strategies for ensuring that the queries will scale well, as the tables grow?
(Table partitions seem like the best option so far, is there any other options that I'm missing?)
Update: Based on comments it looks like upgrading to Postgres 12 would be best, but I'm still wondering if there are idiomatic strategies for designing tables with this structure and large amounts of data.

What have I considered:

Composite indexes but they don't work across multiple tables
Duplicating the created_at field over to exchange_content and giving it its own index
Using a materialized view with the created_at field and the data I need from exchange_content
It may be using Seq Scan because Seq Scan may be faster than using the index
Partial indexes, but I don't think they apply given my requirements
Using a table partition for date ranges


texts: MySQL: Execute CREATE TEMPORARY TABLE SELECT just one time?

texts:
I want to create a temporary table with some select-data.
My (strange) problem is that I have to do the same query more than one time.
CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT NULL, INDEX (id))
SELECT id FROM table WHERE xyz;

CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT NULL, INDEX (id))
SELECT id FROM table WHERE xyz;

Unfortunately, MySQL executes the second query although the temp-table already exists.
QUESTIONS

Is there a way to tell MySQL that the creation of the temporary table and the SELECT query are just executed if the table does not exist?
Maybe with an if-condition?
Or can I check to some information schema if a temp-table exists?
Thanks for some help!

Best regards,
Timo


texts:
I am currently building an online game where data is stored in MySQL.
I have three tables:

Characters
Items
Inventory

They are built in this manner:

Characters

| ID(key)   | Name  | Other...  |
| 1         | Adam  | content   |
| 2         | Ben   | content   |
| 3         | Con   | content   |
| 4         | Dun   | content   |
| ...       | Eab   | content   |

Items

| ID(key)   | Name      | Other...  |
| 1         | Potion    | content   |
| 2         | Sword     | content   |
| 3         | Shield    | content   |
| 4         | Card      | content   |
| ...       | Food      | content   |

Inventory

| ID(key)   | CharacterID   | ItemID    |
| 1         | 1             | 2         |
| 2         | 4             | 2         |
| 3         | 4             | 1         |
| 4         | 2             | 3         |
| ...       | 4             | 3         |

What I am looking for is if this is efficient and an OK method to use for storing this type of data? Instead of having to store the name of the item over and over I just reference it's ID from the items table.

The PHP code creates a JOIN to find the character ID and the item ID then searches for it on the inventory table. Then I can pull in any data I want.

If this is not efficient or if I have created a problem, please point me in the right direction so i can resolve this before it becomes a large issue at launch.


texts: Postgres to Postgres DBLink with AWS/RDS

texts:
I am trying to use dblink functionality for Postgres 9.5.2 DB to Postgres 9.52 on AWS/RDS.

On the server:

CREATE SERVER myremotedb FOREIGN DATA WRAPPER
    dblink_fdw OPTIONS (host 'myremotedb.123456.eu-west-1.rds.amazonaws.com', port '5432', dbname 'my_db');

CREATE USER MAPPING FOR foouser SERVER myremotedb OPTIONS (user 'foouser', password '1234');

GRANT USAGE ON FOREIGN SERVER myremotedb TO foouser;

On the client:

SELECT dblink_connect('myremotedb', 'host=myremotedb.123456.eu-west-1.rds.amazonaws.com , port=5432 , user=foouser , password=1234 , dbname=my_db');

select * from dblink('myremotedb', 'SELECT * FROM my_data') AS T(id integer, name text);

but what I get is:

ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the connection string.

Any idea how can I start a connection?


texts: Getting a "type" column from each table in query

texts:
Let's assume I have 3 tables: videos, books, albums and I would like a query that gives me the 3 latest entries in each table, along with a column "media_type" valued Video, Book, or Album, accordingly.

I know how to get the 3 latest entries from each table, but how do I create the temporary column?


texts:
There is a user as HRS. I am creating a view in HRS as

CREATE VIEW HRS.RNS.....

I am getting

  ORA-01031: insufficient privileges

Edit

As i remember, i only execute following commands for the user and then my user can create tables

GRANT UNLIMITED TABLESPACE TO THE_USER;
GRANT CONNECT, RESOURCE TO THE_USER;
GRANT CREATE SESSION TO THE_USER;

How does my user automatically get rights to create table but not view?


texts: How can I use LEAD LAG-like functions on multiple columns simultaneously

texts:
Suppose I have the following table:

start_data | end_date
----------------------
t1         | Null
Null       | t2
t3         | Null
Null       | t4
t5         | Null

The output should be like this:

start_data | end_date
----------------------
t1         | t2
t3         | t4
t5         | Null

notes:

the type of ti is date
ti < ti+1 holds true for all values.

pseudo code:
 for every record i
    xi <--- start_date
    yi <--- end_date
    zi <---- min(yi) && xi>yi
    (xi,zi) should be the output for this record i

Is it solvable using native SQL?


texts:
Migrating from DB2 found this error message:

Error message: Incorrect string value: '\xF2\xAC\x9F\xB0or...' for column 'column_name'

This is when trying to import an XML to MySQL. I should have a longtext column with UTF-8 encoding:

  MySQL does not have this option, so you'll have to use the TEXT datatype using a UTF-8 character set.
  View Full Question

I tried to change the character set of the column to 'utf8mb4' which would support 4 byte length characters. And also set the character_set_server to utf8mb4. But it doesn't work.

  If you have MySQL 5.5 or later you can change the column encoding from utf8 to utf8mb4. This encoding allows storage of characters that occupy 4 bytes in UTF-8.
  You may also have to set the server property character_encoding_server to utf8mb4 in the MySQL configuration file. View full question

Is there anything else i could try?

MySQL Version: 5.6.13
Connection via: JDBC
OS: Microsoft Windows 7


texts:
I am wondering if PostgreSQL has an update query somewhat like their insert values syntax.

I have an updated set of data in this form currently:

INSERT INTO bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID)
VALUES(109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null);

and I want to update the rows that are there already to the new data, I'm looking for something like the following where I can update all rows without repeating myself:

UPDATE ON ID
bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID)
VALUES(
        (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null),
        (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
);

The above would check for matching ID values, and update the matches with the new data.

Note: I am really looking to avoid manually mapping all the column names. PostgreSQL already knows my columns, and I've already mapped them on the data. Why do that again in a longer format?


texts:
A lot of stuff stopped working on my server because the sda3 partition became full. After fixing that issue, I cannot start the mysql service:

root@abcd:~# sudo service mysql start
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
root@abcd:~# systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Fri 2018-09-28 22:44:46 UTC; 4s ago
  Process: 9276 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=1/FAILURE)
  Process: 9255 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

Sep 28 22:44:46 abcd systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Sep 28 22:44:46 abcd systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Sep 28 22:44:46 abcd systemd[1]: Stopped MySQL Community Server.
Sep 28 22:44:46 abcd systemd[1]: mysql.service: Start request repeated too quickly.
Sep 28 22:44:46 abcd systemd[1]: mysql.service: Failed with result 'exit-code'.
Sep 28 22:44:46 abcd systemd[1]: Failed to start MySQL Community Server.

If anybody could help me with this problem, I would be greatly appreciative. Thanks!


texts: Does MS provide a way to query a SSDT project

texts: Is there a way to query a SSDT project to get a list of tables or foreign keys?  Similar to what you can do with the actual database?


texts:
I have a database with 2 tables : products, and synonyms.
When a user type one or several words to search in the products.description field, I would like to search for all the synonyms/substitutions as well.
The synonyms table is basic, and have to columns : word1 and word2.
Example of its content :

chiptune <-> 8bit
lofi <-> lo-fi
lo-fi <-> 8bit

In SQL, How can I present to the user results feturing the word "lofi" when he type "chiptune"?
What I am trying to achieve is a "loop between synonyms", in both ways.
Thank you if you have some thoughts.


texts: Copy & rename db so I can attach both copies

texts:
I have a db called 'mysite_db' on a server and I have created a copy on my local machine which is also 'mysite_db'.  I would like to attach the db (from my local machine) to the server which already has this db name. Meaning I have to change my local db's name (I guess I also need to change the physical file).  I'm lost with how to do that. Can you please guide me?

My goal is to load the 2 databases on the server for example: 'mysite_db' and 'myNewSite_db'


texts: Replication with fail over cluster in SQL Server 2014

texts: Presently I am Using Replication Feature in SQL Server 2012 .I want to migrate to SQL server 2014 .I want to use fail over cluster feature .Does it effect the replication feature which we are using for our project


texts: Managing Sql Server 2012 Standard Edition

texts:
I have some questions about Sql Server 2012 Standard Edition.

Does anyone know of 3rd party software to manage Sql Server 2012 Standard Edition with?
Is it advisable to use SSMS 2005/2008 Express to manage a SQL Server 2012?

The reason I ask is that we only have XP workstations and SSMS 2012 requires Vista/Windows 7 or above.

Thank you, Pieter


texts:
I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.)

I am having some issues with using Bulk Insert when the data file is on a network share when using Windows Authentication. What is known is that the SQL Server service account has access to the network resource, which is shown by logging into SQL Server with a SQL account and doing the Bulk Insert. I also have rights to the files on the share, as shown by the fact that I put the files there.

Now, when connecting to SQL Server with Windows Authentication and running the Bulk Insert I get the following error (emphasis mine):

  Msg 4861, Level 16, State 1, Line 2 Cannot bulk load because the file
  "\\[server]\[share]\[filename]" could not be opened. Operating system error code 5(Access is denied.).

I found this snip at BULK INSERT (Transact-SQL)\Security Account Delegation (Impersonation), which says, in part:

  To resolve this error [4861], use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

Finally, after much searching, I found this TechNet article, How to Configure the Server to be Trusted for Delegation, and we tried the unconstrained delegation and I rebooted the SQL server, but it still does not work.

What the heck am I missing???


texts:
In SQL Server, What does the 'key' part of the phrase 'index key' mean? Is the key a number? what is it?

If I have the table of data containing a column called LastNames, and I have, say, a clustered index on it, is the 'key' for each row simply the last name? Would an example of a 'key value' be something like 'Cooper'?

I am asking because in non-clustered indexes on clustered tables, the leaf page of the non-clustered index contains the clustered index key. But what does it mean that it contains the index 'key'? Does this mean the actual column data stored on the leaf page of the clustered index (i.e. the data page) for the column that has a clustered index on it?


texts: mysql 5.7 json filter and features products

texts:
sorry for my english!
I need use this column(feature) for show features, filters for products and in some language(It depends on the country).
I have products table(innodb) and a column(json) features, the column feature is like this:

{feature:{
        [{"en": "System Operation",
        "it": "Sistema operativo",
        "value": "IOS 8"
    },
        {
        "en": "Memory Ram",
        "it": "Memoria Ram",
        "value": "16 Gb"
    },
        {
        "en": "display",
        "it": "schermo",
        "value": "5.5\""
    }]}

Here have just 2 language but can have 30 or 100 language.

For show feature i use foraech in php I should optimize... maybe in mysql if is possible.

$language_detect = substr($_SERVER['HTTP_ACCEPT_LANGUAGE'], 0, 2);
if($language_detect == "it") {$lang_det = "it";}
elseif($language_detect == "en") {$lang_det = "en";}
elseif..
elseif...

foreach($feature->feature as $row) {
      foreach($row as $key => $val){
        if($key == '$lang_det'){
              echo $key->$lang_det .'  ' . $val . ' : ' ; }
        if($key == 'value'){
                echo $val . '<br>' ; }
        }
    }

The really first problem is: how I can make the query like this result?:

        [{
        "en": "System Operation",
        "value": "IOS 8"
    },
        {
        "en": "Memory Ram",
        "value": "16 Gb"
    },
        {
        "en": "display",
        "value": "5.5\""
    }]

and second problem is: how can I search product/s with the feature/s selected?
In mysql sure :)

Thank you!!


texts: Compare timestamp type columns in SQL Server

texts:
I am trying to get data since last timestamp. In my legacy database, all tables have a column named as recordStamp type timestamp. I want to rely on this to select records modified after the max(recordStamp) from previous select.

I tried with T-SQL; but this tells only equality. What are my options..

CREATE TABLE Cars (
    id int identity,
    model varchar(20),
    brand varchar(20),
    recordstamp timestamp)

INSERT Cars (model)
VALUES
('accord'),
('camry'),
('corolla'),
('civic'),
('prius')

--latest recordstamp: 0x0000000001DD8AAF
select * from cars order by recordstamp desc --latest recordstamp: 0x0000000001DD8AAF

--Update a record
Update Cars set brand = 'honda' where model = 'accord'

--I want only the accord in result.
select * from cars order by recordstamp desc


texts: Merge Replication Business Logic Handler Incorrect Encoding when connecting to SQL Server

texts:
We have a SQL Server Merge Replication configuration that uses a business logic handler to do conflict resolution on a datetimeoffset column.

All we're doing is a copy of the Subscriber or Publisher dataset to the CustomDataset in the UpdateConflictsHandler function and logging the win. When profiling we see the below, where it appears that all non-datetime date and time types (datetime2, datetimeoffset, date, time) are being mangled by adding spaces between each character.

We are surmising that somehow after our function returns the column is being encoded as UTF-16 and decoded by SQL Server as UTF-8. Is there any way to fix this? It seems to be happening wholly outside of our code. (SQL Server 2008R2, with Merge Replication through IIS over web)

exec [MSmerge_cft_sp_7BF3FB7C8F8D4A33D64FC13EF2DE42B6] '4BCCFA1D-891F-4E7D-9A0B-000409743B2B'
,1000001001,'','JOHN','TestCorp2322'
,'Smith',NULL,NULL
,'2 0 1 3 - 0 4 - 2 0   1 7 : 0 1 : 3 1 . 0 0 0 0 0 0 0   - 0 7 : 0 0'
,NULL,NULL,NULL,1,0,0,N'',NULL,NULL,1,0
,'2 0 1 8 - 0 4 - 1 8   1 1 : 0 8 : 4 0 . 4 7 2 5 2 7 2   - 0 7 : 0 0 '
,1,NULL,1,1,1,NULL,'4BC6FA1D-891F-4E7D-9C0B-000409793B2B',N'PUBLISHER-NAME',1,1,N'The same row was updated at both ''SUBSCRIBER.RepTest11'' and ''PUBLISHER.RepTestCorp''. The resolver chose the update from ''SUBSCRIBER.RepTest11'' as the winner.','D64FC13E-F2DE-42B6-A40E-05DF4159644F',0x00,46258027,'D64FC13E-F2DE-42B6-A40E-05DF4159644F',0


texts: How to identify where a package execution has spent most of the time?

texts:
Team BI has a package deployed as a project on SQL Server 2012.

How can I find how long a package takes to run on a daily basis, and where it has spent most of the time?

I know that the catalog.executions view can be queried, anything else?

I am getting this request from developers:

Today’s BI Incremental ran without error but took far longer than usual.  The Sales Incremental took 1hour 12mins.  Yesterday it took 15 mins.  There has been no release/code changes.

The Sales ETL hits databasename on servername.  Was there something going on at the time of the load (5am)?  Anything logged?


texts: Patching SQL Server 2014 Versus SQL Server 2014 Express

texts:
Need to apply a Service Pack to the following SQL Express install:

Installed version : 12.0.2000.8 (2014) Express Edition

Not sure if I can apply the latest SQL 2014 Service Pack SP4 or stick to the latest Express Service Pack I have found SP3.

Are service packs for SQL 2014 different than the service packs for SQL 2014 Express?


texts: Oracle INSERT with If statement for values

texts:
I'm trying to find a way to put together an INSERT which will insert a column value based on the value of another column, so for exampke

INSERT INTO table1
(col1a,col1b)
SELECT
  (dependant of the value of col2b),
  col2b
FROM table2;

so:

if col2b is 1 then col2a is A
if col2b is 2 then col2a is B
else col2a is C

Thanks in advance

KS


texts: "Main" folder is not found at "/var/lib/postgresql/9.3/"

texts:
I am new to Ubuntu and PostgreSQL. I am trying to find a "data directory" for PostgreSQL on my Ubuntu.
When I Googled, it appears that it should be /var/lib/postgresql/9.3/main. But on my Ubuntu, I do not see that folder.
 I only see "bin" and "lib" folders. Can anyone guide where I could locate a data directory for PostgreSQL?

I was informed that the variable is normally called datadir.


texts: How to immediately terminate a SPID with a very long rollback time estimated?

texts:
I have a script to restore a database from a backup. Prior to the restore command, it has:

set single_user with rollback immediate

The script seemed to be hanging here. I used sp_who2 to find out a SPID number 70 was in ROLLBACK state. I then used kill 70 with statusonly to obtain estimated time remaining - and it translates to more than 20,000 hours.

How can I arrest this rollback, even if it means the DB ends up in a transactionally inconsistent state? (I don't really care, as I just want to restore it from a backup.) Is my only option to shut down the server (and impact other DBs on that server)?


texts: Should a MySQL replication slave be set to read only?

texts:
I've got replication running on Percona Server 5.5 by following this guide and wondered if I should add read-only=1 to my slave's my.cnf to make it read only?

The guide sets up replication for the mysql table so users are replicated but I am primarily using the slave to take mysqldumps, an in an emergency reconfiguring it to be master, so I don't believe we need (or should have) write enabled on it constantly?


texts: Retrieve user id for users that have 5 specific rows

texts:
I have 2 tables: Titles and Data
They have a One-to-One relationship
The entry_id in Titles t = entry_id in Data d

I need to retrieve the t.author_id for those users who have 5 specific rows in the Data table.

e.g

SELECT t.author_id
FROM titles t, data d
WHERE d.skills = "Skill A"
AND d.skills = "SKILL B"
AND d.skills = "Skill C"
AND d.skills = "Skill D"
AND d.skills = "Skill E"
GROUP BY author_id

I think i have too many joins happening as the query won't finish executing.

Any ideas?

Thanks.


texts:
Postgres version: 11

I've got 2 roles - one used for read and one for write on the same database.

CREATE ROLE reader WITH login;
CREATE ROLE writer WITH login;

CREATE DATABASE thedatabase;

GRANT SELECT ON all tables in schema public TO reader;
GRANT INSERT ON all tables in schema public TO writer;

Everything is fine for all the existing objects in the database, but as soon as another object is created both users will need a GRANT SELECT/INSERT to be able to perform their duties.

Is there a config to overcome this?


texts: Including clustered index key in non-clustered index

texts:
We're running SQL Server 2008.  In looking over the results of a query I ran on sys.dm_db_missing_index_* views, I see a curious suggestion for an included column.  I have the following two tables:

create table foo (
    id int not null identity(1,1) primary key
    /* many more fields, not relevant to question */
)

create table bar (
    id int not null identity(1,1) primary key
    foo_id int not null,
    param_name nvarchar(50),
    param_value nvarchar(255)
)

The table foo has a 1:M relationship with bar; bar is used to store miscellany associated with some of the records in foo.

Back to the sys.dm_db_missing_index_* query.  Several times a day, the optimizer is looking for an index on bar with foo_id in equality_columns and id in the included_columns.  My question is, what is the point of including the table's clustered index key in a non-clustered index?  Since the leaf level of the non-clustered index includes the clustered index's key value anyway, isn't it redundant to include the clustered index key in a non-clustered index?

EDIT:  The only existing index on bar is the clustered index with the PK as the key.

Thanks in advance.


texts: Broken Slave MySQL : How To Fix MySQL Replication (BINLOG) error in the Slave?

texts:
Before we start I can give some info about the versions,
Ubuntu - 14.04, MySQL - 5.5
I have successfully configured the Master-Slave replication with two EC2 Ubuntu instances. My master is in Ubuntu 14 and slave is in Ubuntu 18. The replication was successful and the slave was replicating data for the last 3-4 days. But, suddenly my slave(Ubuntu 18) stopped replication with a query error.
Error Message : Last_SQL_Error: Error 'Duplicate entry '11379183' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO testTable (id,value1,value2,value3,value4,value5,value6) VALUES(22,"5","429438","1592499300","1","8","1")'

Slave_IO_Running: Yes
Slave_SQL_Running: No

I have tried,

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

It has helped me for some time. but the replication is broken again.
How can we resolve this kind of replication errors? Is there any automated method for fixing the broken slave?
Is GTID based replication possible with MySQL 5.5? I think GTID is available from MySQL version 5.6?


texts: Reduce number of rows with same id repeated based on status

texts:
I have the following table:

-- id, location_id, status, posted_year, posted_quarter
CREATE TABLE foo AS
SELECT * FROM ( VALUES
(1 ,12,'active'  ,2014,3),
(2 ,12,'inactive',2014,3),
(3 ,12,'active'  ,2014,3),
(4 ,12,'active'  ,2014,4),
(5 ,12,'inactive',2014,4),
(6 ,13,'active'  ,2015,1),
(7 ,13,'active'  ,2015,1),
(8 ,13,'inactive',2015,1),
(9 ,13,'active'  ,2015,2),
(10,13,'active'  ,2015,2),
(11,13,'inactive',2015,3),
(12,13,'active'  ,2015,4),
(13,13,'active'  ,2015,4),
(14,13,'inactive',2015,4),
(15,12,'active'  ,2015,1),
(16,13,'active'  ,2015,1),
(17,12,'inactive',2015,1),
(18,12,'active'  ,2015,2)
) AS t(id,location_id,status,posted_year,posted_quarter);

I want to recreate this table but have only one quarter per year from each location.

We might have more than one record in a year for a quarter, and in this case we need to check the status based on:

If one status is active for a year in selected quarter, the status will be active, otherwise the status will be inactive.

Examples:

location_id 12 for year 2014 and quarter 3 will have one record in the new table with status active.
location_id 12 for year 2015 and quarter 1 will have one record in the new table with status inactive.

How to write this query?


texts: Convert sql_variant to varchar to select left 2

texts:
I am trying to populate a column (versionstatus) with if the service pack needs updating. I have created a lookup table which lists the SQL Version, and the latest product version, e.g.
SQLVersion           LatestSP
SQL Server 2016      13.0.5026.0

I then have a second table which contains product versions, and want to compare this value with the LatestSP value, if the value in the first table is less than the second, then SP is not up to date.
I need to specify that where the product version in both tables begins with X (i.e. begins with 12 for SQL 2014, 13 for SQL 2016) then compare the numbers (so that I only compare the correct versions to each other) and if the value in Servers is less than the value in SPLookup, then populate the versionstatus column with 'NEEDS UPDATING'.
 However I am having issues converting sql_variant to varchar to be able to select the first 2 digits.

Here is what I have so far, and would extrapolate this to the other versions.
productversion in Servers table is sql_variant, LatestSP in SPLookup is varchar.

 INSERT INTO Servers
 (versionstatus)
 SELECT CASE WHEN LEFT(X.productversion, 2) = '12' AND LEFT(Y.LatestSP, 2) = '12' AND X.productversion < Y.LatestSP THEN 'UPDATE NEEDED'
            WHEN LEFT(X.productversion, 2) = '12' AND LEFT(Y.LatestSP, 2) = '12' AND X.productversion >= Y.LatestSP THEN 'UP TO DATE'
        END AS versionstatus
        FROM Servers X INNER JOIN SPLookup Y ON X.productversion = Y.LatestSP


texts:
While altering a DB to open getting the below error.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[344], [73], [125], [], [], [], [], [], [], []


texts:
I have a table with a column named price typed as varchar. I want the maximum value of this column. I am using this query:

SELECT MAX(price) FROM product_management.`item_list`

but the result is wrong. Please solve this problem.

I am using [SB] SQLyog Ultimate Ver. 9.0.2.0.


texts:
I'm trying to pull data from an SQL Server database into Excel 2016 via ODBC / Microsoft Query (built in to Excel).

I'd like to have a dropdown of companies, where after a company is selected from the dropdown, all contacts for that company (from a separate contacts table) are displayed.

From what I've learned so far I need to use :field or @field to indicate that the user chooses - something like:

SELECT Company.CompanyName,
       CompanyContacts.Forename,
       CompanyContacts.Surname,
       CompanyContacts.Email
FROM Commercial.CompanyContacts CompanyContacts
     INNER JOIN Commercial.Company Company
        ON (CompanyContacts.CompanyID = Company.CompanyID)
WHERE (UPPER (Company.CompanyName) = UPPER (@CompanyName))

But Excel / MS Query returns:

  "Must declare the scalar variable "@CompanyName". Statement(s) could not be prepared."

Can anyone advise on how I declare the scalar variable? Assuming that's even what I'm actually needing to do here. Many thanks.


texts: How to sum (aggregate) all the values in a key/value JSONB?

texts:
in Postgres 10 I have the following table:

CREATE TABLE testtable (
    id  int   PRIMARY KEY,
    qty jsonb
);
INSERT INTO testtable (id,qty)
VALUES
  ( 1, '{"2018-08-01": 10, "2018-08-11": 20, "2018-10-23": 30}' ),
  ( 2, '{"2018-08-17": 100, "2018-11-01": 200}' ),
  ( 3, '{"2018-09-03": 1, "2018-09-01": 2, "2018-10-01": 3}' );

Is there a quick way, using SQL to return the sum of each JSONB field so that the results would be:

ID Total
1   60
2   300
3   6

I have seen more complicated possibilities using UNNEST and/or replace (https://stackoverflow.com/questions/26699601/sum-of-values-of-json-array-in-postgresql).

However, I was hoping for something more elegant.


texts:
So I've got two tables.

Accounts has three columns:

id, Account_ID, Account_Name

Tickets also has three columns:

id, Account_ID, Description

I can use the following query to get the most commonly occurring account IDs from within the ticket table - The accounts which are the most active in submitting tickets

select account_id, count(*) as 'occurences' from tickets group by account_id order by occurences desc

which is great, but i'd like to group that on the accounts table so the output reads

Accounts.Account_Name, Count of occurences from previous query

I tried this codeplex example to no avail, I get the error

The multi-part identifier "ticket.Account_ID" could not be bound.

using this query

Select accounts.Account_Name, Accounts.Account_ID, TotalTickets
from Accounts
inner join
(Select tickets.Account_ID,count(*) as TotalTickets
from  Tickets group by ticket.Account_ID) acctTickets
on acctTickets.Account_ID= accounts.Account_ID

Thanks!


texts:
our vendor has recommended us to use your tool to collect data for him. After the installation (version 8.03) we received following errors (warnings):
*The module 'sp_AllNightLog' depends on the missing object 'master.dbo.DatabaseBackup'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'sp_AllNightLog' depends on the missing object 'master.dbo.DatabaseBackup'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'sp_AllNightLog' depends on the missing object 'master.dbo.sp_DatabaseRestore'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'sp_AllNightLog' depends on the missing object 'master.dbo.sp_DatabaseRestore'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'sp_DatabaseRestore' depends on the missing object 'dbo.CommandExecute'. The module will still be created; however, it cannot run successfully until the object exists.*
I verified the master database and I did not find the object mentioned above. Is it OK?
P.S. We are running Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)   Mar 14 2020 16:10:35   Copyright (C) 2019 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: )
Regards,
Ondrej


texts: How to ensure only a single row with a certain value

texts:
Given this table:

CREATE TABLE rollouts
(
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id    TEXT        NOT NULL,
    is_active    BOOLEAN     NOT NULL DEFAULT FALSE
);

How can I ensure that for a given device_id, there is only ever one active rollout? Multiple inactive ones are fine, so a simple unique index doesn't cut it.


texts: Refactoring/normalization - but almost empty table

texts:
I normalized a legacy DB into this structure:

But I'm not sure if it is correctly normalized. I don't feel very comfortable with the almost empty filters table.

The requirements

A group contains several items.
A filter organizes how a item is "displayed".
Multiple terms are defining a filter.
Different groups may contain the same item, but different filter.
A filter cam have no terms.

Not really important requirements

A filter might be used several times.

Btw. the diagram was created using http://cakeapp.com.


texts: Phantom comparison data in SQL Server Reporting Services - How do I remove it?

texts:
I'm trying to write a query that will allow me to include or exclude a filter in my where statement based on whether a parameter passed in is set to true or false.  So far my statement is

IF @IncludeAttended = 'true'
begin
    SELECT        id, fullName, Address, PhoneNumber, NeverAttended
    FROM            Guests_View
    WHERE        (GroupID = @groupid)
end else begin
    SELECT        id, fullName, Address, PhoneNumber, NeverAttended
    FROM            Guests_View
    WHERE        (GroupID = @groupid)
    AND NeverAttended = 'false'
end

So I'm trying to filter based on whether or not the guest has ever attended an event.  Running the above SQL in Management Studio will get me appropriate results with no issues, but when I try to run this in SSRS, I get an error message Conversion failed when converting the varchar value 'AM13425' to data type int.

I have tried several different statements and methods to find where this string might be, but I can't find it anywhere in the database, nor can I find it anywhere in the project itself, so as far as I can tell, the data doesn't exist.  Wiping the table and repopulating it with fresh test data doesn't help either, the message just resurfaces.  Also, if I take out the @IncludeAttended parameter, and just run only one half of the statement above (among various iterations), I can get the results back, but I need this report to allow this filtering.  What else can I try, and where can I look for this phantom data that's causing me the problem?


texts: SQL Server : create dynamically queries to select all related data in DB based on entry table and ID

texts:
I'm tasked to create a SQL statement which will create (and run?) multiple other queries which will select all related data outgoing from an given ID (uniqueidentifier) and a given table name.

It doesn't have to be that fast, but it should be able to use against every db, given only 1 ID and the table name.

In the DB, there are some 1:n, n:m etc. relations and not every table has a column named Id, some only have foreign-keys and it has to be fully dynamically and generic.

I tried to realize it by myself, but I'm not that good in SQL, I also tried some other scripts which will return the primary-foreign-key-relations but I'm stuck there so I'd thought you guys might help me :)

To be returned, I need (would be best) generated queries which I can run against the db and get all information returned
(Are there other good return possibilities?)

Alternatively I could create this project in C#, but I'm stuck in attempt as well.

I'll appreciate any kind of help :)


texts:
I can understand that this is a basic question, but it's not about knowledge (I think) it's about practical experience.
I came from another field so, be kind, please.
I have my ORDER
Every ORDER has ITEMS
Every ITEM has his VARIANTS
Every VARIANT has his QUANTITY
When I'm required to store the content of an order, my first instinct is to create an array with items, variants, and quantities and then convert it to a string and finally store it as a field of the ORDERS table.
I can smell there is something wrong.
The ITEMS in the order can be a table, and so can VARIANTS and QUANTITY. And this seems to me as a tidier way to solve the problem.

However, when I have to rebuild an ORDER with the first approach it's very easy and I just have to convert the string to the original 3D array, while with the second I've to look at 3 different tables, and this is not something I feel comfortable with, mainly because it seems like a lot of work to do, and if there are only a few requests, it's easy, but if I have 1000 users at the time I fear that the DBMS can't cope with the workload.
I've tried some basic queries with MySQL and all my fears seem excessive, but I don't know how things work in a real work application.
I'd be glad to know how a pro would solve this problem. In production, would you use strange concatenations or separate tables (and the DB remains always stable and responsive)?


texts: How to do incremental backup with limited privilege in mysql?

texts:
Env：MySql Community v5.7 @AWS RDS
User Privs: Select_Priv, Create_View, Show_View, Show_Db, Execute, Create_tmp_table
binary_log: ON

Currently I can do full backup using mysqldump --single-transaction without locking tables. Is that possible to do incremental backups in this case? And how?
I've tried FLUSH LOGS but it requires RELOAD privilege which i don't have. Also, some of the 3rd-party backup tools requires higher access levels.
Thanks in advance!


texts:
I am trying to identify the cause of internal memory pressure. From what I have learned, Resource Monitor Ring Buffer returns an indicator for internal pressure. For example by using this query,

SELECT Top (1) *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

The XML results (within the resource monitor tag):

<ResourceMonitor>
  <Notification>RESOURCE_MEMPHYSICAL_LOW</Notification>
  <IndicatorsProcess>2</IndicatorsProcess>
  <IndicatorsSystem>0</IndicatorsSystem>
  <NodeId>0</NodeId>
  <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>
  <Effect type="APPLY_HIGHPM" state="EFFECT_IGNORE" reversed="0">100789</Effect>
  <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>
</ResourceMonitor>

In this article by Bob Dorr, shrinking signal from Memory Broker could create internal memory pressure and reflect in resource monitor notification with IndicatorsProcess = 2 (IDX_MEMPHYSICAL_LOW) as shown above.

This article by Slava Oks shows another way of checking internal memory pressure with RING_BUFFER_SINGLE_PAGE_ALLOCATOR. I don't know much about this particular ring buffer because I never seen it in my environment.

SELECT Top (1) *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SINGLE_PAGE_ALLOCATOR'

The XML result this time:

<Pressure status="0"><AllocatedPages>477</AllocatedPages>
  <AllAllocatedPages>477</AllAllocatedPages>
  <TargetPages>31553</TargetPages>
  <AjustedTargetPages>31553</AjustedTargetPages>
  ...
</Pressure>

My question is, which internal processes may be contributing to internal physical memory pressure that will turn on this indicator = 2 (IDX_MEMPHYSICAL_LOW)?  What I see from resource monitor indication of internal physical memory pressure, these are among them:

Memory broker shrink operation (e.g. cache cleanup) which can be identified through memory broker ring buffer,
Max Server Memory setting change and,
Buffer pool stolen page > 75% (as I am not seeing it reflected from single page allocator ring buffer)

But are there others, too? And is there any record/log to identify them through DMV or other mean?


texts: Why does STRAIGHT_JOIN consume more CPU?

texts:
Why does STRAIGHT_JOIN consume more CPU than a regular JOIN? Do you have any idea?

When I use STRAIGHT_JOIN on one of my queries, it speeds up the query like from 12 seconds to 3 seconds. However, it consumes so much CPU. Might this be about server configuration or something else?

You might want to check the code after this comment / Topic Ids are OK, Getting Devices... /
Before this line there are some code about filling topic_ids to a temp table.

Here is the query:

CREATE PROCEDURE `DevicesByTopic`(IN platform TINYINT, IN application TINYINT, IN topicList TEXT, IN page_no MEDIUMINT UNSIGNED)
BEGIN

    DECLARE m_index INT DEFAULT 0;
    DECLARE m_topic VARCHAR(255);
    DECLARE m_topic_id BIGINT UNSIGNED DEFAULT NULL;
    DECLARE m_session_id VARCHAR(40) CHARSET utf8 COLLATE utf8_turkish_ci;

    -- Session Id
    SET m_session_id = replace(uuid(), '-', '');

    -- Temp table
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_topics(
        topic_slug VARCHAR(100) COLLATE utf8_turkish_ci
        ,topic_id BIGINT UNSIGNED
        ,session_id VARCHAR(40) COLLATE utf8_turkish_ci
        ,INDEX idx_tmp_topic_session_id (session_id)
        ,INDEX idx_tmp_topic_id (topic_id)
    ) CHARSET=utf8 COLLATE=utf8_turkish_ci;

    -- Filling topics in a loop
    loop_topics: LOOP
        SET m_index = m_index + 1;

        SET m_topic_id = NULL;
        SET m_topic= SPLIT_STR(topicList,',', m_index);

        IF m_topic = '' THEN
            LEAVE loop_topics;
        END IF;

        SELECT t.topic_id INTO m_topic_id FROM topic AS t WHERE t.application = application AND (t.slug_hashed = UNHEX(MD5(m_topic)) AND t.slug = m_topic) LIMIT 1;

        -- Fill temp table
        IF m_topic_id IS NOT NULL AND m_topic_id > 0 THEN
            INSERT INTO tmp_topics
                (topic_slug, topic_id, session_id)
            VALUES
                (m_topic, m_topic_id, m_session_id);
        END IF;

    END LOOP loop_topics;

    /* Topic Ids are OK, Getting Devices... */

  SELECT
    dr.device_id, dr.platform, dr.application, dr.unique_device_id, dr.amazon_arn
  FROM
    device AS dr
  INNER JOIN (
            SELECT STRAIGHT_JOIN
            DISTINCT
                d.device_id
            FROM
                device AS d
            INNER JOIN
                device_user AS du ON du.device_id = d.device_id
            INNER JOIN
                topic_device_user AS tdu ON tdu.device_user_id = du.device_user_id
            INNER JOIN
                tmp_topics AS tmp_t ON tmp_t.topic_id = tdu.topic_id
            WHERE
                ((platform IS NULL OR d.platform = platform) AND d.application = application)
                AND d.page_no = page_no
        AND d.status = 1
                AND du.status = 1
                AND tmp_t.session_id = m_session_id COLLATE utf8_turkish_ci
  ) dFiltered ON dFiltered.device_id = dr.device_id
  WHERE
        ((platform IS NULL OR dr.platform = platform) AND dr.application = application)
        AND dr.page_no = page_no
        AND dr.status = 1;

    -- Delete rows fFill temp table
    DELETE FROM tmp_topics WHERE session_id = m_session_id;

END;

With the STRAIGHT_JOIN this query takes about 3 seconds but consumes so much CPU like 90%, but if i remove the keyword STRAIGHT_JOIN, it takes 12 seconds but consume 12% CPU.

MySQL 5.6.19a - innodb

What might be the reason?

Best regards.


texts:
I recently had a problem with our server. It required us to install a new hard drive, cpanel, and setup the old hard drive as a slave. How can I migrate the mysql database from the slave drive to the master drive? The slave contains the tables with MYD, MYI, and frm files. The database is probably about 20G.

I'm assuming SSH is the best way to do this. I am not very familiar with SSH commands and cannot find out how to do this. I've already setup the new database (on the fresh install on the master drive) with the same name, users, and passwords.

System: Linux, Centos 6.4


texts: View information on historical deadlocks

texts:
I've been tasked with investigating an issue where an application's job has failed due to deadlocks. I'm aware of using traces to capture deadlocks, however as this is something that occurred 16 hours ago I wanted to know if it was possible to see what caused the deadlock?

Thanks


texts:
Trying to setup replication through the wal shipping using Postgresql 9.3 x64.

Used this instruction from youtube.

The problem is - the slave postgresql service won't run.
This is what in logs

IST FATAL:  ShmemIndex entry size is wrong for data
structure "Wal Sender Ctl": expected 144, actual 56


texts: Should I add it as an additional column or a seperate table?

texts:
I have a table in my SQL Server that's called "Regions", it has information about 9 regions and the 10th row holds the info about our Division.
Now that we will be merging another 2 divisions with its regions, Should I add them all in the same table like I have currently or start separating them by having a "Division" table and "Regions" table?

Which one is the best approach to pull reports from, like pulling documents implementing in specific regions from a specific division? how can I reference that, please advice.
Thanks in advance.


texts: What is the safest method for order by cast()

texts:
My query looks a bit like this:

Select top (@n) *
From MyTable
Where IsNumeric(MyColumn) = 1
Order By Cast(MyColumn as int) desc

Occasionally Sql will produce a query plan that places the Sort operator ahead of the filter operator. This is problematic since the table can contain values which are non-numeric.

Is there a query hint I can apply to force the filter to always come first?

Or would it be better to write my order-by clause to safely handle non-numeric values? I've found two options for this, but I'm not sure which one is better:

Order By Cast(Stuff(MyColumn, 1, Patindex('%[0-9]%', 1), '') as int) desc

Or

Order By Case IsNumeric(MyColumn) When 1 Then Cast(MyColumn as int) else 0 end desc


texts: OPENROWSET Bulk insert Text File NULL Columns

texts:
I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the log files is not consistent.
The table has 10 columns 9 of which are populated from the log file, last 3 columns are all descriptions but the log file may only have a single description column populated (the max is always 3).
In the image below the highlighted rows work because they have the valid 3 column worth of data separated by a comma. Is there a way to force BCP to always populate the 3 columns or mark them as NULL then move onto the next row?


texts: "Approximately equal" between Sync and Async operations for PageCleaners

texts:
The documentation of num_iocleaners says:

  The parameter can be reduced if both of the following conditions are true:

  pool_data_writes is approximately equal to pool_async_data_writes
  pool_index_writes is approximately equal to pool_async_index_writes.

  The parameter should be increased if either of the following conditions are true:

  pool_data_writes is much greater than pool_async_data_writes
  pool_index_writes is much greater than pool_async_index_writes.

What is approximately? What is "much greater"?  What ratio?  What percentage of difference?

Does anyone have an example of values that are "approximately equal" and where the Page Cleaners value should be increased? The same info for the "much geater" values would also be appreciated.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0000332.html?cp=SSEPGG_10.5.0%2F2-4-4-8-108&lang=en


texts: SSMA cannot read meta data (missing runtime components)

texts:
What I want to do should be rather easy: Use SSMA to transfer internal Access tables and queries to SQL-Server.

On my Notebook I'm running SQL-Server 2017 and Office 365 (32 bit)

As I have 32 bit Office (company's choice ...), I installed the "SSMAforAccess_8.4.0_x86.msi" from the official download site and get a warning during setup, that there are components missing with a download link where I can find "accessruntime_4288-1001_x86_en-us.exe" (same with x64).

I tried first the x86, but it fails, because there is a 64bit "Office 16 Click-to-Run Extensibility Component 64-bit Registration" on my system. And trying to install the x64 fails too, because there is a 32bit "Office 16 Click-to-Run Extensibility Component" on my system.

I can run SSMA without any problems, load the .accdb, connect to SQL-Server, but it fails to read the accdb's meta data:

  Access Object Collector error: Database
       Die COM-Klassenfactory für die Komponente mit CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} konnte aufgrund des folgenden
  Fehlers nicht abgerufen werden: 80040154 Klasse nicht registriert
  (Ausnahme von HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). Please make
  sure that you are using the correct version of SQL Server Migration
  Assistant. If you have 32-bit Microsoft Access installed, then you
  will need to use 32-bit version of the Migration Assistant. In order
  to use 64-bit version of Migration Assistant, you will need to install
  64-bit Microsoft Access components available at
  https://go.microsoft.com/fwlink/?linkid=834402.
       An error occurred while loading database information.

The german part translates to: The COM-class could not be executed due to the given error (class not registered).

A search for the given CLSID brings me back to the access runtime components.

Question:

How can I use SSMA, if I cannot install the needed runtime?
Or: How can I install missing components, if the installation routine does not work (e.g. shift the DLL to GAC with gacutil manually)

EDIT:

In the meanwhile I tried to use SSMA x64 to no avail...

UPDATE

In the meanwhile I found this (german) instruction how to find and remove the "Click-to-Run" services.

In short: Go to %windir%\installer, make sure that explorer is showing you the column "Betreff" (I don't know, how this is called in English, might be "regarding"). You can find the MSI-packages. Copy them somewhere and perform a right-click. Now you can choose "uninstall".

After removing these "click-to-run" stuff from my notebook I can proceed but run into the next error:

  [...] because you have these Click-to-run installer based Office
  programs installed on your computer 1) Microsoft Office 365 ProPlus -
  en-us and 2) Microsoft Office 365 ProPlus - de-de". Windows Installer
  and Click-to-Run editions of Office programs don't get along for this
  version, so you can only have one type installed at a time. [...]

So my new question is:

Can we use SSMA together with MS-Access 365 and - if not - is there an alternative?

Well, I might set up a pc just for this one-time-issue...

Thank you for any hint...


texts: Execution time is different between normal query and query inside a stored procedure

texts:
I have following simplified query

WITH Report1 AS (
   SELECT SUM(t.X), SUM(t.Y)
   FROM Table1 t
   WHERE t.Date1 > :dateStart  and t.Date1 < :dateFinish
)
SELECT * FROM Report1

This query executes in 6-7 seconds.

When I put this query to stored procedure

create or replace PROCEDURE       SPReport1
(
 pCURSOR OUT Geneltipler.REFCURSOR,
 pDateStart Table1.Date1%TYPE,
 pDateFinish Table1.Date1%TYPE,
 ) AS
BEGIN

  OPEN pCURSOR FOR
    WITH Report1 AS (
       SELECT SUM(t.X), SUM(t.Y)
       FROM Table1 t
       WHERE t.Date1 > :dateStart  and t.Date1 < :dateFinish
    )
    SELECT * FROM Report1
 ;
END;

and execute in the following fashion in Toad and Sql Developer:

DECLARE
  PCURSOR Geneltipler.REFCURSOR;
  pDateStart DATE;
  pDateFinish DATE;
BEGIN

  pDateStart := TO_DATE('01/01/2014', 'DD/MM/RRRR');
  pDateFinish := TO_DATE('01/03/2014', 'DD/MM/RRRR');

  SPReport1(
    PCURSOR => PCURSOR,
    pDateStart => pDateStart,
    pDateFinish => pDateFinish,
  );
  /* Legacy output:
DBMS_OUTPUT.PUT_LINE('PCURSOR = ' || PCURSOR);
*/
  :PCURSOR := PCURSOR; --<-- Cursor
--rollback;
END;

This execution takes 4 minutes, that is 240 seconds. This is almost 40 times normal. How can I find what is the problem? I know that stored procedures does not have execution plans? How can I investigate what is the problem?

I am using Oracle 10gR2 RAC.


texts:
Is is possible to call a function, like now() when inserting/updating a JSONB column in Postgres?

I was thinking something like the following where time would be set to the return value of now():
SELECT '{"value": 5, "time": now()}'::JSONB;


texts: How to create a read-only server role on SQL Server 2012?

texts:
I am granting the "View any database" permission when creating a new server role, but realized this permission only allows the user to view the system databases.

I am trying to create a server role that is read-only and can read any database.

Is there a way to create a user-defined server role to read user databases? Or do I have to do this through user mapping per user?


texts:
I'm currently working on project to clean database services on oracle database.
Analyze first whats not needed and then drop.

I need to prepare backup of services data for rollback purpose.
(toad can export many types of object metadata, but there isn't option to export services metadata)

Someone know way to achieve that?


texts:
this is probably very basic but I can't figure it out.
I'm trying to total two records to give the output: £2,100 in the Total field but they keep coming out separate.

Any help? Thanks.


texts: Is it a bad practice to associate database records to relevant object storage by ID?

texts:
I'm using Google Cloud Platform to host a web app. The app has a typical database structure, with a schema defined by Django migrations. For one of the models, Song, each record should be associated with an object stored in Cloud Storage which contains the audio of that song. Since this audio is created and stored immediately after the database record is created, what I've done historically is to simply title the object with the ID of the database record, so that each object would be stored as song/[ID]/audio.mp3

Is there a better approach to this? As long as each database record has a static ID, I can't see any critical issues with this one (and indeed, I've used it successfully in past projects of mine). But I have a hunch that many people would consider this a weak coupling and could open up the possibility for bugs.


texts:
I have written a custom PostgreSQL client and I want to add a function that would automatically capture query execution time.

The current executionTime value in Slonik results records the total query execution time, including the roundtrip and parsing. In order to capture the actual query execution time, I plan to prefix every query with EXPLAIN ANALYZE, parse the explanation and capture the results of the query. However, I cannot find a way to run EXPLAIN ANALYZE and capture the actual query result (in addition to explain output).

How to run EXPLAIN ANALYZE and get results of the query?


texts: Reduce the number of queries to the same table

texts:
I need to build some reports using two tables:

AuthorTitles: id(pk), author_id, title_id
AuthorReports: id(pk), author_id, title_id, items_sold, date

My report needs to output the fallowing: author_id, max_items_sold_wavg, items_sold_wavg, date

The below code might have some errors, please check the fiddle.

max_items_sold_wavg is the weighted average of the fallowing query:

WITH maxitems AS (
 SELECT
   at.author_id,
   at.title_id,
   max(ar.items_sold) as sold,
   ar.date::date as date
 FROM author_titles at
 LEFT JOIN author_reports ar ON ar.author_id = at.author_id AND at.title_id = ar.title_id
 GROUP BY 1, 2, 4
), totitems AS (
    SELECT
      mi.author_id
      count(ti.title_id) as total,
      count(CASE WHEN (mi.sold = 1) THEN mi.title_id END) AS sold1,
      count(CASE WHEN (mi.sold = 2) THEN mi.title_id END) AS sold2,
      count(CASE WHEN (mi.sold = 3) THEN mi.title_id END) AS sold3,
      mi.date,
    FROM maxitems mi
    GROUP BY mi.date
), itemsold AS (
   SELECT
      at.author_id
      count(at.title_id) as total,
      count(CASE WHEN (ar.items_sold = 1) THEN ar.title_id END) AS sold1,
      count(CASE WHEN (ar.items_sold = 2) THEN ar.title_id END) AS sold2,
      count(CASE WHEN (ar.items_sold = 3) THEN ar.title_id END) AS sold3,
      ar.date::date as date,
    FROM author_titles at
    LEFT JOIN author_reports ar ON ar.author_id = at.author_id AND at.title_id = ar.title_id
    GROUP BY at.author_id, ar.date::date
)
SELECT
  ti.author_id,
  ((ti.sold1 * 30 + ti.sold2 * 60 + ti.sold3 * 100) / at.title_id),
  ((is.sold1 * 30 + is.sold2 * 60 + is.sold3 * 100) / at.title_id),
  ti.date
FROM totitems ti, itemsold is
WHERE ti.author_id = is.author_id AND ti.date = is.date

You can notice that in maxitems I extract only the max sold for every title and in itemsold I extract all the sells for all the titles, because an author can sell many titles in one day.

Example report data:

http://sqlfiddle.com/#!15/d2efc/22

How can I improve this query, is there a better way of doing it, because you can see that I am accessing the same data multiple times.


texts: I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created?

texts:
I have powerful machine with 70 GB RAM. I created one Oracle instance with 20 GB as sga_target. I am not able to create another Oracle instance with sga_target > 10G even when I keep the first database down. If I set sga_target >=10G it gives below error on startup:

  ORA-27104: system-defined limits for shared memory was misconfigured

While free -m shows that there is enough memeory available though:

             total       used       free     shared    buffers     cached
Mem:         72419      34490      37928          0        618      28159
-/+ buffers/cache:       5711      66707
Swap:         2047          0       2047

Do I need to increase swap space? Any pointer in this regard is highly appreciated.

Also for 70 GB memory, for creating many instances, what would be best value for swap space - is there some way to caluculate this? My objective is to have at least two instances each with sga_target=20G and will keep only one instance up at a time. If I am missing any concept here?

Output of ipcs -im is as below

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1


texts:
I'm sorry if this question breaks the rules of DBA StackExchange. I'm not sure if it is allowed in here.

I am planning to lead a basic SQL Server training for my coworkers. The course should include some basic information about relational databases and the SQL querying. This is why I'm looking for some training materials.

Obciously I could prepare PowerPoint presentations, SQL tasks and all the other stuff myself but I think since it is a well researched topic I should not reinvent the wheel.

Do you know of any training materials available that I could use?
I have the training materials for the MS Exam 70-461 - Querying SQL Server but I'm not sure if I would be allowed to use it and also they are pretty big.

Can you recommend something?


texts: Postgresql: How to get row type of a table as text?

texts:
I want to do this:
json_populate_record(null::schematable, json)

But schematable is a text variable contaning schema.table
I have tried this:
json_populate_record(pg_typeof(schematable), json)

but pg_typeof() returns regtype not rowtype.
How do I get a rowtype of a dynamic text argument?


texts: Importing 40 gb mysql file via phpmyadmin

texts: I have a 40 GB MySQL dump file. Can anyone give me the idea how to import the file via phpmyadmin?


texts: How to allow user access to non-owned objects in Postgres?

texts:
I am in the process of creating a pre-prod Postgres database, and want to ensure the accessing user has limited access.

I want to setup tables in such a way that the accessing user can't CREATE, DROP or ALTER objects. Currently the only way I can see it to change the ownership to another user, such as postgres and then somehow grant only the permissions needed:

 ALTER TABLE mytable SET OWNER TO postgres;
 REVOKE connect ON DATABASE mydatabase FROM PUBLIC;
 GRANT USAGE, SELECT, UPDATE ON ALL TABLES IN SCHEMA public TO dbuser;
 GRANT connect ON DATABASE mydatabase TO dbuser;

The issue is when I do the above I am unable to do a simple SELECT when I connect with the user the grant was applied to. For example:

 select * from mytable;

Yields:

ERROR:  permission denied for relation mytable

How to get this right?
Additionally, should I use a named schema other than public?


texts: filling a table by triggering multiple tables on insert

texts:
I have a table m contains the following fields:

id int(11) not null auto_increment, //PK
name varchar(100) null,
something_else varchar(100) default 'not important'

I need to fill up table m by using this trigger

CREATE TRIGGER some_name AFTER INSERT ON `x` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);

which I got from this dba question

but the problem is that each row on m should be filled using after insert on multiple different tables like x,y. So, how can this be done.

I hope that my question is clear enough as it is in my mind.

Update 1:
table m has the following row:

id - name - something_else
1  - null - not important

on after insert for table x, table m row should become like:

id - name     - something_else
1  - newthing - not important

then, on after insert for table y, table m row should become like:

id - name     - something_else
1  - newthing - new value

Hence, the row on table m had been filled by multiple inserts on different tables and updated accordingly.


texts:
With this query:

SELECT SUM(qty) AS sumQty,
       cnetprodid
FROM   quoteitem
WHERE  ordered = 1
       AND sageSOPOrderReturnLineID IS NOT NULL
       AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
       AND cnetprodid LIKE 'S%'
       AND price > 10
       AND created > DATEADD(MONTH, -3, GETDATE())
GROUP  BY cnetprodid
ORDER  BY SUM(qty) DESC

I get one row back per product, the top row has 294 as sumQty and 'S10381902' as the cnetprodid

Enter this query:

SELECT SUM(qty) AS sumQty,
       p.ProdID,
       p.CatID
FROM   quoteitem
       INNER JOIN cds_prod AS p
         ON p.ProdID COLLATE Latin1_General_CI_AS = cnetprodid
       INNER JOIN cds_atr AS a
         ON a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID
       INNER JOIN lan_cat_sections AS c
         ON c.attID = a.atrID
             OR ( LEN(ISNULL(c.attID, '')) = 0
                  AND c.cat_id LIKE p.CatID + '%' )
                AND ( ( c.[include] = 0
                        AND a.valID NOT IN (SELECT Value
                                            FROM   Split(c.valID, ',')) )
                       OR ( c.[include] = 1
                            AND a.valID IN (SELECT Value
                                            FROM   Split(c.valID, ',')) ) )
WHERE  ordered = 1
       AND sageSOPOrderReturnLineID IS NOT NULL
       AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
       AND cnetprodid LIKE 'S%'
       AND price > 10
       AND created > DATEADD(MONTH, -3, GETDATE())
GROUP  BY p.ProdID,
          p.CatID,
          a.prodID
HAVING SUM(qty) > 10
ORDER  BY SUM(qty) DESC

I get one row per product back but now the row with 'S10381902' as the ProdID column has a figure of 8820 for sumQty.

All I want is a number for how many products have been sold in the last quarter, not interested in products that have sold less than 10 units, hence the HAVING part.

The COLLATE is there because the tables come from different databases; one is in SQL_Latin1_General_CP1_CI_AS and another database is in Latin1_General_CI_AS.

Fiddle: http://sqlfiddle.com/#!3/ce1ed/4


texts: how to remove 6 months old data automatically from MYSQL innodb table

texts: I have a very big table in which I am fetching data everyday and it's size is getting bigger everyday but for my software, only 6 months data is useful. So, I am planning to remove data which is older than 6 months. I know I can do it using CRON jobs but I want to use MYSQL way to delete older data automatically. I read about PARTITION also and My question is Can I do it using PARTITION or I will have to find some alternative way ?


texts: Introduce delay between master slave setup in MariaDB

texts:
We're using a master-slave solution using MariaDB 5.5.44. Is there a way to manually introduce an artificial delay between the master and slave? I'm thinking of something like 1 minute or so. Is there a mysqld option that does this?

I see that MySQL 5.6 has the MASTER_DELAY parameter for the CHANGE MASTER TO command, but I suppose it doesn't exist in 5.5 (or MariaDB 5.5). Is there another way to cause a delay?


texts: Changes to the database schema are called migrations, what are data changes called?

texts:
Not sure where to ask this but here it is anyway.

I understand that changes to the database schema are called migrations from a certain version to another.

What do DBAs call changes to the underlying data, ie. the sequence of INSERT, UPDATE, DELETE? Think of the MySQL binlog for instance. Is there a specific term for this?


texts:
I am trying to get to the root of a problem and need to see if and when a specific Delete Query was run on my MS SQL Server.
The command would have come from my .NET website.
My Server is Windows 2012 R2 running SQL 2014 Express.
The query would have run at some point in the last 3 weeks or so.


texts:
I was sent this as a mysql dump, the file is labeled .sql, but it's clearly not a standard export.  Is this something that can be used to reproduce a database and if so how?

Here is a small sample

USE [Sunwave]
GO

/****** Object:  Table [dbo].[SunwaveZipCodes]    Script Date: 6/15/2014 8:10:57 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SunwaveZipCodes](
    [POSTAL_CODE] [varchar](50) NULL,
    [UTILITY_NAME] [varchar](50) NULL,
    [CITY_NAME] [varchar](50) NULL,
    [COUNTY_NAME] [varchar](50) NULL,
    [STATE_CODE] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


texts:
Couple of days back I had a table with 7GB of data and it was showing 63MB of overhead in phpMyAdmin and I mistakenly started its optimization and after working of more than 40 mins it corrupted the whole table.

What are the safe practices to avoid table crash?


texts:
Getting this apparently nonsensical error
Error code: 1054 Unknown column in having Clause

while running this query
Select date(START_TS), resource_key, count(INTERACTION_RESOURCE_ID)
From tableName
Group By date(START_TS), resource_key
Having date(START_TS) > '2020-08-27' and resource_key in(772122, 772134);

Although the objective has been achieved by using Where Clause before Group By, but this error is still bothering. Any help would be appreciated.


texts: Extra tables are added when I create a new Database

texts:
This is a really weird problem. When I create a new DB from a script I'm getting extra tables added. These aren't system type tables but regular tables that look like they may have come from a past project. There are 3 of them, each with 2 columns, either ints or nchar(10) with no keys, constraints, triggers, or indices.

I have no idea where this is coming from or how I can fix it. Right now I just delete the tables but it's a bit annoying and I'd like to fix the issue.

Here is a script I wrote that reproduces the error:

CREATE DATABASE X;
GO

USE X;
GO

CREATE TABLE Y(
  YId INT IDENTITY CONSTRAINT pk_yid PRIMARY KEY(YId));
GO

CREATE TABLE Z(
  ZId INT IDENTITY CONSTRAINT pk_zid PRIMARY KEY(ZId));
GO

After I run this script my DB is created, but it has 5 tables instead of 2.

Running SELECT * FROM INFORMATION_SCHEMA.TABLES just lists my extra tables as BASE TABLE, exactly the same as the ones I defined (all other columns are the same as well).

This is happening on SQL Server 2008 R2
(Microsoft SQL Server Management Studio 10.50.2500.0)


texts: How do you extract discount data from SAP?

texts:
I'm not a SAP developer. We already have a big SSIS package that pulls data from the SQL Server instance that our SAP installation uses and I need to add discount data to it. I've usually had pretty good luck looking through this package and the SAP UI and then using Google & SAP Data Sheets to figure out how the tables are laid out, but this time I'm totally lost.

In the SAP UI, the data I'm searching for is listed under the order document conditions. The Googling I have done makes it pretty clear that I'm supposed to use VBAK.KNUMV and JOIN to KONV.KNUMV. Here's the problem: our server doesn't have KONV. I found a forum post that says that I can JOIN to KONP.KNUMH instead, but for the sample document I'm working on, there's no corresponding record in KONH.

I can't find an explanation for why KONV doesn't exist, and I don't know why JOINing VBAK.KNUMV to `KONH.KNUMH' doesn't work. If anyone could guide me down the right path, I would appreciate it.


texts:
I have 2 tables in Microsoft Access.
Table 1:
ID    Val1    Val2    Val3
 1      a       b       c
 2      e       f       g
 3      h       i       j

Table 2:
ID    Val Chosen
 1        a
 2        f
 3        j

In the Val Chosen field, I want Access to create a listbox in Val Chosen that contains the values in Val1, Val2, Val3 for the respective id.  For example, when the user clicks the Val Chosen listbox for the record with ID 1, it should contain "a", "b", and "c".  The Val Chosen listbox for the record with ID 2 should contain "e", "f", and "g".
In design mode for "Table 2", the display control is set to list box. The following query is used as a row source and incorrectly returns all possible values.
SELECT Val1
FROM Table1
UNION
SELECT Val2
FROM Table1
UNION
SELECT Val3
FROM Table1

The actual database has many records so I would prefer to preserve the structure of the database if possible.


texts: SQL Server: Get location of transaction log from MDF file

texts: Our SQL Server 2012 Express server crashed and lost some data. We managed to rescue the main MDF, but can't find the LDF. Is there a way to get this information from the database MDF file or msdb, or temp mdfs that were found?


texts: In MySQL replication, why aren't queries run on the slave when there was no USE statement on master?

texts:
We have replication running from our master server to a slave. binlog_format is the default; mixed.

We have noticed something weird and are not sure whether it's a bug or intended behaviour. Certain statements are being ignored.

Ignored

DELETE FROM schema_name.table_name WHERE id = 123;

Works

USE schema_name;
DELETE FROM schema_name.table_name WHERE id = 123;

Also works!

USE different_schema_name;
DELETE FROM schema_name.table_name WHERE id = 123;

When I delete something without previously selecting a database on master, the query is ignored on the slave. But when I issue a USE schema_name on the master before the statement, even if it is a different schema, then it does run on the slave.

Regardless of the USE and whether they run on the slave, they always appear in the binary logs on master.

We noticed this as we have a script that tidies up old shopping carts that loops over a list of databases and issues DELETE FROM schema_name.carts WHERE created_at ..., but replication was breaking when an INSERT INTO carts SET cart_number = 'xxx' call took place on the slave; the cart number (unique column) no longer existed on master (after a successful check to validate such) but on the slave it still existed so it threw the error and broke replication.

Is this intended behaviour? What's the best method to ensure this kind of behaviour doesn't happen?

For reference and possibly meaningful, master is MariaDB (in a drbd cluster) and slave is Percona.


texts: SQL Server Merge Replication Error: The format of a message during Web synchronization was invalid

texts:
I am doing Merge Replication using Web Synchronization. On Subscriber end, there is SQL Express 2012 on Windows 10 Pro. On publisher end, there is SQL Server 2016 on Windows Server 2016. Web Synchronization is on Windows Server 2016 Hosted on IIS 10. Replication is working perfectly fine as long as the data is not large. Actually I have applied filters on article to replicate data according to my need. So when I applies a filter that pulls data from replication in less than 3000 chunks, it works perfectly. But as soon as it reaches 3100, I get error. On Subscriber end the error is:

  The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web
  server.

On the IIS Server, where Web Synchronization is configured; the error is:

  CHttpListener, 5732,  2756,  S1, ERROR: ErrNo = 0x80070715, ErrSrc = , ErrType = 10, ErrStr = The process could not write the
  response message due to OS error 995.
  CHttpListener, 5732,  1272,  S1, ERROR: ErrNo = 0x80070715, ErrSrc = , ErrType = 10, ErrStr = The processing of the response message
  failed. CReplicationListenerWorker, 5732,   334,  S1, ERROR: Failure
  generating response message, hr = 0x80070715.

It happens exactly when my replication chunks reaches 3100. And it takes around 20 seconds to reach 3100 chunks. So timeout is not an issue I believe.

I checked the file sizes in Snapshot folder. They are max 10 MB large collectively. But the data records to insert are in thousands, or some millions.
I have tried setting WebSyncMaxXmlSize to 2000000 following this post.
I have also tried disabling TCP send/receive Payloads following this post.
But all in vain.

I am using MergePullSubscription, have tried setting DownloadGenerationsPerBatch from 10 to 100. It also didn't work.

Please let me know with your valuable advises. What else I should try? Or if anyone faced the same issue, what is the proper solution to it?

Update (Answer)

I managed to fixed this problem. Below are the steps which helped me:

I turned on the Data Partitions
Also checked the 'Automatically define a partition and generate a snapshot if needed......'.

This is what I am talking about:


texts: How to update scheduler job credentials in oracle enterprise manager?

texts:
I have a job scheduled in oracle 11g which runs on daily basis. Job is running with User "user_dba" (unix server user). The job is failing from the day I changed the password for the user "user_dba". I navigated to scheduler jobs in OEM and found the credential name "user_dba_credential". The job is failing because this named credential still has old password where as I have changed the password. So can anyone please tell me how to update the password in the "user_dba_credential" to my new password via OEM?

Thanks in advance.


texts: performance benefits of shrinking a database

texts:
I have a database of 1.5 TB, which had only 200 GB free space before.Due to performance issues, business decided to reduce 1 month data retention policy to 1 week.Due to this 3 weeks data was deleted and now it has 700 GB free space.

The 500 GB free space increased due to data delete is no more required, and there no chance to grow data again to occupy this 500 GB space.

I am planning to shrink the database(using shrink file command ) and run index optimization process to  rebuild indexes, if fragmentation is greater than 30%.
The released space wont be used by other systems, however can be used for log file and temp db growth efficiently in near future.

Is there any benefits on performance by reducing the size of database, other than space release ?

Previous questions related to shrinking(in stack exchange) does not properly answer to my queries.

Thanks in advance for your kind reply !!!


texts: Automate the patching of SQL Severs on Windows

texts:
We are getting approximately 50-100 lower environments SQL Servers newly build.

We are assigned some basic task of SQL DBA which includes one as patching SQL Servers.

How can this be achieved, specially when we have servers, where only Windows authentication can be used whose password needs to be generated every 24 hours. That makes this look complex or may be something I am expecting too.

Please share your inputs how can we achieve this either via powershell, cmd or any best known approach?

Edit: These boxes are all lower env like DEV/SAT/QA. Some of them have log shipping configured but that too for side reporting , even though not that critical. These are not 365*7 and are generally used during the business EST hours.

Also, we have multiple logins each assigned to a DBA who has sys admin rights on these boxes but that login needs to have a password generated which expires every 24 hours.


texts: Any disadvantages to enabling federated storage engine?

texts:
We've encountered a couple use cases where having federated tables prove useful, despite their limitations and caveats.

As best I can tell this must be enabled with a federated declaration in the .cnf upon startup.

Our use cases are limited and certainly don't warrant restarting every instance just to have it.  At the same time I'm thinking of adding it as a default to our standard config so it is available on instances as they get bounced during more pressing maintenance.

Is there any reason not to have this option available unless decided absolutely necessary?

For a final clarification: This question is not about the disadvantages of actually using a federated engine; rather it is just about just enabling as an available option. Perhaps to put it another way: Is there good reason it is disabled by default other than making sure you don't shoot yourself in the foot if you understand the consequences?


texts: How to Import all the files in a directory with a single command using mysqlimport?

texts:
I'm trying to dump and import some data from a MySQL (5.7) database.

Running the command

mysqldump --tab=/mysqldump mydatabase

I'm able to get a .sql and .txt file for each table.

How can I import all the files/tables using mysqlimport in a single command?

I have tried mysqlimport mydatabase /mysqldump but it does not work.

Thanks.


texts: mysql read slave to fetch entire database on start

texts:
I am building a standart infrastructure, where writes are going to master db, and reads are going to slave dbs. The idea is that when I need more read power - I just spawn more MySQL slave servers.

Now I am aware of standart procedures of how to create a new slave. And they are a little to slow ... What I am looking for is to create an server image that I could spawn when I need more read power and that image would automatically sync to master and become its slave for reads.

At the moment I think of doing it like this.

Have a database backups every 4 hours or so/
Write a script that would run first time server is started.
The script would download the latest backup and import it in a regular way ... and bla bla bla ...

Maybe someone has done something similar and could share some resources ?


texts: Load flat file into a predetermined table

texts:
Scenario

Table created

Data in flat file loaded to the table but with errors. please advise how to fix it

(a) 'address' is split to 3 columns causing mismatch between the source and destination

(b) 3 Nulls in the 'B_Date' column due to different format. Do I need to massage the raw file before load into the Microsoft SQL or what is the SQL formula?

raw file


texts:
I need to calculate values for a series of measurement data. The measurement data is divided into 1000 groups of consecutive rows as each call of the query must output a series of 1000 values. Typical input sets will contain between 10,000 to 1,000,000 rows. The following solution is the best idea i could think of. It works fine, but execution is rather slow. Because one of my requirements is to trigger the calculation quite often i need to optimize for execution time. Unfortunately it’s not an option to precalculate the values for the individual groups because every new measurement row affects the group size.

Schema Setup

create table devices
(
    id varchar not null
        constraint devices_pkey
            primary key
);

create table processes
(
    id        integer not null,

    constraint processes_pkey
        primary key (id, device_id),

    device_id varchar not null
        constraint fk_processes_devices
            references devices
            on delete cascade
);

create index processes_device_id_idx on processes (device_id);

create table measurements
(
    timestamp  timestamp with time zone not null,
    current    real                     not null,
    process_id integer                  not null,
    device_id  varchar                  not null,

    constraint measurements_pkey
        primary key (timestamp, process_id, device_id),

    constraint fk_measurements_processes
        foreign key (process_id, device_id) references processes
            on delete cascade
);

create index measurements_process_id_device_id_idx on measurements (device_id, process_id);

INSERT INTO devices (id) VALUES ('123');

INSERT INTO processes (id, device_id) VALUES (456, '123');

WITH numbers AS (
  SELECT *
  FROM generate_series(1, 1000000)
)

INSERT INTO measurements (timestamp, current, process_id, device_id)
SELECT NOW() + (generate_series * interval '1 second'), generate_series * random(), 456, '123'
FROM numbers;

Query

select min(timestamp) as timestamp,
       case when sum(current) < 0 then -SQRT(AVG(POWER(current, 2))) else SQRT(AVG(POWER(current, 2))) end,
       456            as process_id,
       '123'          as device_id,
       index / 1000   as group_index
from (select timestamp,
             current,
             row_number() over (order by timestamp) as index
      from measurements
      where device_id = '123'
        and process_id = 456
      order by timestamp) as subquery
group by group_index
order by group_index;

db-fiddle: https://www.db-fiddle.com/f/uVTcf9Q2JDEkPf3S5hgvfB/2

Query plan visualization: http://tatiyants.com/pev/#/plans/plan_1569689658707

How to optimize the query?

Query Plan

Sort  (cost=100157.88..100158.38 rows=200 width=60) (actual time=927.340..927.402 rows=1001 loops=1)
  Sort Key: ((subquery.index / 1000))
  Sort Method: quicksort  Memory: 103kB
  ->  HashAggregate  (cost=100144.74..100150.24 rows=200 width=60) (actual time=926.828..927.036 rows=1001 loops=1)
        Group Key: (subquery.index / 1000)
        ->  Subquery Scan on subquery  (cost=0.42..77644.74 rows=1000000 width=20) (actual time=0.049..704.478 rows=1000000 loops=1)
              ->  WindowAgg  (cost=0.42..65144.74 rows=1000000 width=20) (actual time=0.046..576.692 rows=1000000 loops=1)
                    ->  Index Scan using measurements_pkey on measurements  (cost=0.42..50144.74 rows=1000000 width=12) (actual time=0.029..219.951 rows=1000000 loops=1)
                          Index Cond: ((process_id = 456) AND ((device_id)::text = '123'::text))
Planning Time: 0.378 ms
Execution Time: 927.591 ms


texts: Many-to-many relationship, what's the best choice for primary key?

texts:
In advance, I'd like to apologize if it's a duplicate question... I can't find a direct answer, probably because I'm not a native english speaker.

Straight to the subject:

I have 2 tables: company, which contains some companies (of course) and service, which contains general services that can be provided by the companies. Now I need to create another table in order to build the link between company and service, since it's a many-to-many relationship.

Which is the best choice for primary key in this case (considering conventions and performance)?

- like this, making use of an "independent" unique field id

CREATE TABLE IF NOT EXISTS `mydb`.`company_service` (
  `id` INT NOT NULL,
  `company_id` INT NOT NULL,
  `service_id` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM

or

- like this, with the two fields that represents the foreign keys composing the primary key?

CREATE TABLE IF NOT EXISTS `mydb`.`company_service` (
  `company_id` INT NOT NULL,
  `service_id` INT NOT NULL,
  PRIMARY KEY (`company_id`, `service_id`))
ENGINE = MyISAM

... or none of these, maybe a third design?

My working environment is MySQL.