texts: Do I need a partition scheme for each partitioned table

texts:
Sorry if this is a stupid question, but I'm setting up table partitioning for the first time.  It will be on multiple existing tables all within the same database.

I know I need a partition function and a partition scheme, but do I need one of each for each table or a combination of 1 function and multiple partition schema or just one of each?

I'm using SQL Server 2014 Enterprise edition.

I tried Google but couldn't find the answer.

Hope someone can help.

Thanks

Alex


texts: No records in Mysql.func table even though there are stored functions in server

texts: When i am creating a user defined function in one of my mysql server, it's not creating any row in mysql.func table . Even though i have multiple functions in my mysql server , this table is empty . Can someone explain me why its not getting logged . it should create row like mysql.proc table right whenever we create any stored procedure. I am using mysql 5.5.40. Thanks in advance.


texts:
While attempting to restore a 4 TB MDF/data database with a 1.8 TB LDF/log file, I'm receiving the following error:

Write on '???' failed: 1117 (The request could not be performed because of an I/O device error.
Error: 3202, Severity: 16, State: 1

The backup appears to have created successfully on the primary machine, but will not restore to the backup machine. Both servers are running the SQL Server 2012 Enterprise Edition. The location of the .LDF transaction log file is being moved during the restore from the primary's I drive to the backups L drive, but this never posed an issue in the past.

The database is made up of 3 .MDF files: one approximately 2 TB, another at 1.4 TB, and another around 300 GB. The .LDF transaction log file is about 1.8TB. The restore locations have enough space, with the drive for the .mdf files having 6 TB total and the drive for .ldf having 2 TB total.

Any thoughts would be greatly appreciated.

FYI:  This article is also posted in StackOverflow at:
https://stackoverflow.com/questions/27116806/sql-server-restore-error-3202
I'm uncertain which location is best for this type of question.


texts: DatabaseIntegrityCheck Fails on Large Database Every Day

texts:
On one of my databases, the integrity check keeps failing with the error:

  Connection may have been terminated by the server. [SQLSTATE HY000]
  Msg 596, Sev 21, State 1: Cannot continue the execution because the
  session is in the kill state. [SQLSTATE HY000].

There is nothing else going on that I can tell and so this is a particularly hard thing to solve.  It's as if the database is timing out or something on the CheckDB command.


texts:
SSMS Version 17.9.1
It looks like the intellisense may not be working? Shouldn't it auto complete Select etc? Also, it looks like the intellisense is from SSMS itself (doesn't work that great)

I've checked the Intellisense setting and it's there just fine, also put the cache size on unlimited


texts:
I have a table (user_access) with the following columns in mysql database

id      int
user    int
access_time    datetime

I am trying to run a query that gives me the number of times users accesses the system (access_time) every 15 minutes between two specified timestamps. I am still developing the query, this is what I have now. Any suggestion will be highly welcomed.

select user, count(user) as users from user_access
where (access_time between '2013-05-28 02:00:00' and '2013-05-28 10:00:00')
group by user

Sample Result will be something like:

Time                User       No of Times
--------------------------------------------------
8:00am - 8:15am     user1          20
8:00am - 8:15am     user2          5
8:15am - 8:30am     user1          15
8:15am - 8:30am     user2          23


texts: How can I get running totals of recent rows faster?

texts:
I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for testing purposes.

CREATE TABLE [dbo].[Table_1](
    [seq] [int] IDENTITY(1,1) NOT NULL,
    [value] [bigint] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

And I tried to get 10 recent rows and its running totals, but it took about 10 seconds.

--1st attempt
SELECT TOP 10 seq
    ,value
    ,sum(value) OVER (ORDER BY seq) total
FROM Table_1
ORDER BY seq DESC

--(10 rows affected)
--Table 'Worktable'. Scan count 1000001, logical reads 8461526, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Table_1'. Scan count 1, logical reads 2608, physical reads 516, read-ahead reads 2617, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--
--(1 row affected)
--
-- SQL Server Execution Times:
--   CPU time = 8483 ms,  elapsed time = 9786 ms.

I suspected TOP for the reason of slow performance from the plan, so I changed query like this, and it took about 1~2 seconds. But I think this is still slow for production and wondering if this can be improved further.

--2nd attempt
SELECT *
    ,(
        SELECT SUM(value)
        FROM Table_1
        WHERE seq <= t.seq
        ) total
FROM (
    SELECT TOP 10 seq
        ,value
    FROM Table_1
    ORDER BY seq DESC
    ) t
ORDER BY seq DESC

--(10 rows affected)
--Table 'Table_1'. Scan count 11, logical reads 26083, physical reads 1, read-ahead reads 443, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--
--(1 row affected)
--
-- SQL Server Execution Times:
--   CPU time = 1422 ms,  elapsed time = 1621 ms.

My questions are:

Why the query from 1st attempt is slower than the 2nd one?
How can I improve the performance further? I can also change schemas.

Just to be clear, both queries return the same result as below.


texts:
I need to index some  tables and for that i found that there are different kinds of indexing.  I am kind of clueless on their proper place of usage.
Should i create index on all the fields that want to retrieve quickly or Should i create composite index on couple of fields?
Is it OK to create composite indexes along with the normal indexes? suppose i have 3 fields :

FirstName
LastName
State

Can i individually index each field and then have different composit indexes on these fields as well?
For example by writing :

Create index indx_name on tblTest(FirstName);
Create index indx_lastname on tblTest(Last name);
Create index indx_state on tblTest(state);
create index indx_cmp_fls on tblTest(FirstName,LastName,state);
create index indx_cmp_lfs on tblTest(LastName,FirstName,state);
create index indx_cmp_sfl on tblTest(state,FirstName,LastName);
create index indx_cmp_slf on tblTest(state,LastName,FirstName);

Is it OK?
Is it suggested? if not what is the best way ?
Is it also OK to have index on all fields ?


texts: Save PostgreSQL logs to a table in different database

texts:
I have a postgres database with multiple tables in it and i am maintaining logs of the database on a local drive in files .csv format on daily basis, using postgres.conf

log_destination = 'csvlog'
logging_collector = on
log_statement = 'mod'

But i don't think it's the best way to do logging. Then i tried to copy the log files in to a table created on a database other than the source database, using psycopg2

COPY postgres_log
FROM '/path/to/csv/log.csv' DELIMITER ',' CSV;

i was successful in doing that but again it's not the best way, because each day a new log file is created with new name (log-%D%M%Y.csv) and i don't want to do this over and over again

I have also read about triggers and postgres_fdw extension but i don't know how to write the result of a trigger in to a table other than the source database.

What i want to do is to store the log in a table placed on a database other than the source database with an automated process. Any idea?


texts: "SHOW TABLES" command returning tables not in the schema

texts:
I was debugging a backup script at work and noticed that for some of our bigger tables (greater than 5GB), I'm noticing an odd discrepancy. If I run the command

mysql -h $host -u $user -p $pass -e 'show tables' $schema

I end up getting back something like

sales
sales_1
transaction
transaction_1
etc...

If I search for the tables with _1 using any MySQL client, they don't exist. The backup script is able to dump these tables just fine, but what are they for? More importantly, is there any reason I should take backups of them or is it safe to just exclude them?

This is occurring on Ubuntu 15.10 using MySQL 5.6 Community Edition.


texts: From a user table check if one of them exists on a table

texts:
I'm developing a database with SQLServer 2012 SP2.

I have these tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [...],
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [Created]       NVARCHAR (50) NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

I also have this user type:

CREATE TYPE [dbo].[SerialList]
AS TABLE
(
    Serial nvarchar(20)
);

I use this type as a parameter for a Stored Procedure. I use it to pass a list of Serials.

The stored procedure prototype is:

CREATE PROCEDURE [dbo].[AddSerials]
      @serials as dbo.SerialList READONLY
    , @parentCode nvarchar(20)
    , @isAuto bit

I need to loop @serials to check if all of them are in AggregationChildren table. I know that I can do it using a CURSOR but I wondering if I can do it using something faster than a CURSOR that need to loop over all of the @serials rows.

If need something than return 0 if one (or more) of the Serial in @serials don't exist on AggregationChildren table.

Any idea?


texts: Is there any database modeling software which provides a real-time (and editable) ERD, which I can manipulate alongside a traditional GUI?

texts: In the web development world, the designer can write his code and watch the webpage materialize in real time. Why is this such a convoluted process when it comes to the database world? In MySQL Workbench, synchronizing your model with the the source is such a pain. Trying the same thing within Oracle SQL Developer is also not any better. Why is this so? I understand that this may have something to do with the delineation between the logical model, and the physical model, but if someone can please shed some light on this.


texts: Logical Replication to different schema name

texts:
We have PostgreSQL 11 database with structure one database one schema (public).

dbname : production_one
schema : public

dbname : production_two
schema : public

I would like to set up a logical replication, so that it will have structure One database, multiple schema as below:

dbname : prod_slave
schema : prod_one, prod_two

is it possible to do logical replication as above?

I have successful replication using streaming replication, but the situation need us to use logical replication as above.


texts:
I want to increase Max-connection upto 1000 .How much RAM is required current 4GB RAM with Max-connection 500 ...

If any formula please share to me


texts: continuously extending a table in SQL Server

texts:
We have a large data warehouse database where we continuously get new rows inserted in 5 different tables, at the left-hand side of the b-tree (=at end of the table)

This cause a lot of page splits.
Paul Randall calls these "good" page splits as in they do not cost much when inserting the data.

But when we query sys.dm_db_index_physical_stats we see an avg_fragment_size_in_pages
just under 8, namely the 8 8kB pages that can fit into one 64kB extend. A table with 50000 pages has 6300 fragments.
Not very "good" page splits anymore!

The only way I know we can bring these extends together (in order to get better read-ahead), is to REBUILD the table (or table partition).

Question 1)
Are there other options?

We have one table that in addition to the new inserts get a lot of updates to the newer rows. I.E we get 6 rows every hour, and once a week all rows for the entire week are recalculated (=updated). Fillfactor only works for index REBUILDs

Question 2)
Is there any way I can get SQL Server to use a FillFactor look-alike when inserting rows on the left-most page in the B-Tree?


texts: allow Linux ROOT user access MySQL server without password - how this is done?

texts:
MySQL Server 5.1
OS - RHEL 6.10
I have got this new client to work on and I don't see the MySQL root password saved anywhere. Not in any file under  root's home directory or in any other configuration file under [client] section or anywhere.
This is MySQL 5.1 and there's no question of auth_socket.
There's no mysql_config_editor used. There are no hidden files under ~root home directory that contain MySQL root password. There are no aliases that would save the actual command.
The only thing I see is that there's no password under mysql.user table for user root@localhost.
Thus, I am able to login to MySQL using Linux root without any password but not for other normal users.
[root@localhost]# mysql -uroot --> works
[root@localhost] # mysql --> works because user is root here
[regular_user@localhost]$ mysql -uroot --> doesn't work

Could anyone explain how this is achieved.
https://stackoverflow.com/questions/43386530/show-mysql-databases-as-linux-root-user
https://serverfault.com/questions/563714/allow-linux-root-user-mysql-root-access-without-password/563717
Run MySQL client as user without user's password if have root access


texts:
I would like to tool that when record data changed inside all tables. so later i would get a report like

table user

added record - 3,user3..

deleted record - 2,user2..


texts: Does MS SQL Server have generate_series function

texts:
Does MS SQL Server have Series Generating Function(s) (aka generate_series) like Postgresql has. If not is there a basic way to implement the function? Timestamp version is preferred

I found this question which is an old one. Maybe there is a better solution in new versions.


texts: Which blocking session is shown in Oracle 12c in V$SESSION, when wait event is "enq: TX - allocate ITL entry"?

texts:
We created a testcase on Oracle 12.1 as consequence of totally stucking application because of endless
"enq: TX - allocate ITL entry" in a scenario with about 50 parallel processes. Each of them do the same type of update:
UPDATE MYTAB SET CHARGE=:1 WHERE USAGE=:2 AND CHARGE IS NULL;
During analysis, we detected multiple sessions, showing in V$SESSION.BLOCKING_SESSION any other SID, that holds an open TX.
However, this is usually clear for e.g. row-lock contention, where another session holds exactly this particular row and the blocking session can distinctively be identified.
In our case, we forced the same situation by reducing PCT_FREE to 0 with reorg, because the lock was caused by "enq: TX - allocate ITL entry", which is clearly waiting for a free ITL-Slot in block-header. By reducing PCTFREE, there is not enough free space for dynamically allocating additionally ITL-Entries, which forces this kind of ITL-lock.
Since increasing INITRANS is due to the high number of 50 parallel independend threads no perfect solution, I would like to understand the situation more detailed.
However, since there can be multiple ITL-Slots and so multiple transactions having uncommitted data in this block, the question is, which one is shown in V$SESSION.BLOCKING_SESSION? Just the first matching? Or the oldest? Or any other criteria?
The answer would help us to get a better understanding of the situation.
BTW: We have meanwhile almost understood, that Oracle's deadlock detection cannot work properly, because the lock-chain is partially outsourced to the application, where another session does similar work on same objects with other sessions, which finally creates an endless deadlock via Application, but this is another story...


texts: How to deny access to any system user who attempts to log in without password usage?

texts:
I have mysql-server-5.5 in Ubuntu Server 12.04

I realized that when typing mysql command logged as any system user I got access to mysql, despite I can only display test and information_schema databases, how can I disable this accesses?

I don't have those users registered on mysql so I don't want this to have access.

This is what happens

user-one@server:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39950
Server version: 5.5.38-0ubuntu0.12.04.1-log (Ubuntu)...

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

user-two@server:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39950
Server version: 5.5.38-0ubuntu0.12.04.1-log (Ubuntu)...

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

When looking as root for this users in case they are registered

mysql> use mysql
mysql> SELECT * FROM user WHERE User = 'user-one';
Empty set (0.00 sec)

mysql> SELECT * FROM user WHERE User = 'user-two';
Empty set (0.00 sec)


texts: How can I monitor the import progress of a large CSV File in mysql?

texts:
I am trying to import an existing CSV file into my database and I want to see the progress. I usually use pipe viewer when I do my sql files but for csv files I cannot figure out how to do it.

I use macOX 10.15 and mysql 8.02 (not home-brew).

My idea was:

  pv users.csv | mysql -u user -p -db < File/to/users.csv

Without using PV I just connect to my mysql change to my db and use this command:

LOAD DATA INFILE '/File/to/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';

but I do not the see process.


texts: Converting PostgreSQL table to TimescaleDB hypertable

texts:
I have a PostgreSQL table  which I am trying to convert to a TimescaleDB hypertable.
The table looks as follows:
CREATE TABLE public.data
(
    event_time timestamp with time zone NOT NULL,
    pair_id integer NOT NULL,
    entry_id bigint NOT NULL,
    event_data int NOT NULL,
    CONSTRAINT con1 UNIQUE (pair_id, entry_id ),
    CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id)
        REFERENCES public.pairs (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

When I attempt to convert this table to a TimescaleDB hypertable using the following command:
SELECT create_hypertable(
        'data',
        'event_time',
        chunk_time_interval => INTERVAL '1 hour',
        migrate_data => TRUE
);

I get the Error: ERROR:  cannot create a unique index without the column "event_time" (used in partitioning)
Question 1: From this post https://stackoverflow.com/questions/55312365/how-to-convert-a-simple-postgresql-table-to-hypertable-or-timescale-db-table-usi my understanding is that this is because I have specified a unique constraint (con1) which does not contain the column I am partitioning by - event_time. Is that correct?
Question 2: How should I change my table or hypertable to be able to convert this? I have added some data on how I plan to use the data and the structure of the data bellow.
Data Properties and usage:

There can be multiple entries with the same event_time - those entries would have entry_id's which are in sequence

This means that if I have 2 entries (event_time 2021-05-18::10:16, id 105, <some_data>) and (event_time 2021-05-18::10:16, id 107, <some_data>) then the entry with id 106 would also have event_time 2021-05-18::10:16

The entry_id is not generated by me and I use the unique constraint con1 to ensure that I am not inserting duplicate data
I will query the data mainly on event_time e.g. to create plots and perform other analysis
At this point the database contains around 4.6 Billion rows but should contain many more soon
I would like to take advantage of TimescaleDB's speed and good compression
I don't care too much about insert performance

Solutions I have been considering:

Pack all the events which have the same timestamp in to an array somehow and keep them in one row. I think this would have downsides on compression and provide less flexibility on querying the data. Also I would probably end up having to unpack the data on each query.
Remove the unique constraint con1 - then how do I ensure that I don't add the same row twice?
Expand unique constraint con1 to include event_time - would that not somehow decrease performance while at the same time open up for the error where I accidentally insert 2 rows with entry_id and pair_id but different event_time? (I doubt this is a likely thing to happen though)


texts: Executing local scripts against remote server?

texts:
I am in a situation where I would like to execute a number of sql scripts that are on my local machine against a remote MySQL instance.  I found this question which seems to point me in the right direction.  After reading this I tried the following:

$ ssh -L 12341:127.0.0.1:3306 root@myServer &
$ mysql -h 127.0.0.1 -p 12341

But it's been throwing an "Access denied..." error for 'abe'@'localhost'.  What I would like to do is connect as root from the destination server.  So instead of running as abe@localhost I'd like to run as root@myServer.  I tried:

$ mysql -h 127.0.0.1 -p 12341 -u root@myServer

but this only returns the same error for user, 'root@myserver.com'@'local'. I also tried -u root and got access denied again.

Is there any way for me to specify that I want to connect at root on the destination server in my above example?


texts: Why doesn't jsonb constructor from a NULL record behave like hstore constructor?

texts:
When I construct an hstore

CREATE EXTENSION hstore;
SELECT hstore(null::my_type);
                     hstore
------------------------------------------------
 "name"=>NULL, "street"=>NULL, "location"=>NULL
(1 row)

While the jsonb method,

SELECT to_jsonb(null::my_type);
 to_jsonb
----------

(1 row)

This creates a surprising effect too when you try to merge (||) another like type,

SELECT hstore(null::my_type) || hstore('name', 'Evan');
                     ?column?
--------------------------------------------------
 "name"=>"Evan", "street"=>NULL, "location"=>NULL
(1 row)

SELECT to_jsonb(null::my_type) || jsonb_build_object('name', 'Evan');
 ?column?
----------

(1 row)


texts: Migrating MySQL 5.0 to 5.5 - simple databases

texts:
I have a LAMP server on my LAN running a very old version of RedHat with MySQL 5.0, and I'm trying to migrate it to Ubuntu 12.04 to update it.

I know the advice is to migrate MySQL 5.0 to 5.1 then 5.2, and so on.  However, there are no triggers or stored procedures in these databases, just simple MyISAM and InnoDB tables and indexes.

Can anyone tell me what if I can just export the databases in 5.0 from the RedHat server using phpMyAdmin, and then import them into 5.5 on Ubuntu?

The data is mainly used by PHP to build dynamic web pages.


texts: Fan trap and redundant relationships issues

texts:
Here're a few domain rule resulting the current design of the relations. A project application form can have only one company responsible supervisor, and one company where the project is conducted. A company can have multiple supervisors, and be applied within many project application form. However, a company record together with the company supervisor record are only first created when there's an application on them. The system shall be able to look up company name and find company information, associated supervisor information, and previous project information in the application form. It shall also show a project's company, supervisor when someone process the project application form.

From the design, I can see one of the relationship is redundant and there's fan trap. How can I resolve these without breaking the domain rules.


texts: How to implement on duplicate update for SQL Server?

texts:
I'm trying insert/update data for table T1(int id,char name, float data), which has unique a index on id and name.

I'm using SQLBulkOperation(SQL_ADD) to add records to the table, and SQLBulkOperation(SQL_UPDATE_BY_BOOKMARK) to update them.

Now my use case is that I have to update records if they already exist and insert if not. For this, MySQL provides INSERT INTO ... ON DUPLICATE KEY UPDATE.

How to implement the same in SQL Server? Is there any other efficient way to do this?

I'm using libmsodbcsql-13.0.so.0.0 to connect to SQL Server.


texts: PostgreSQL 10: deleting foreign key locks selects

texts:
I'm trying to delete a table (called incoming) in a busy database with 100s of transactions per second. The table itself is empty (performed a truncate table before). If I try to do drop table incoming a lot of transactions on various other tables are waiting for drop table to finish - obviously not good.

I began deleting indexes, constrains and columns 1 by 1 to identify what is blocking the table deletion and got to a foreign key constraint that was causing the lock. Here's the 'final' incoming table schema:

create table incoming
(
    account_id integer
        constraint incoming_account_id_fkey
            references account
);

From the blocked queries (taking the ticket table below as an example) it doesn't look like the account table queries are blocked but the queries on ticket - a busy table that has a foreign key to the account table. Why would deleting a FK in incoming block queries in ticket?

Here's the outline again on the 3 tables:

incoming - this is where the foreign key I want to delete lives (see schema below).
account - foreign key reference table.
ticket - busy table (select, insert, update, etc..) that has a foreign key constraint to account.

What I tried doing:

I tried disabling the triggers on the table: ALTER TABLE incoming DISABLE TRIGGER ALL; and making the FK DEFERRABLE: ALTER TABLE incoming ALTER CONSTRAINT incoming_account_id_fkey DEFERRABLE;. Didn't help.

Here's the output of the pg_constraint:

SELECT conname, conindid::regclass, confrelid::regclass, * FROM pg_constraint WHERE conrelid = 'incoming'::regclass:


texts: Use one big table or multiple small tables for described scenario

texts:
I have a question regarding my database scheme:

Lets assume I have 100+ measurement devices, each of them is equiped with a  voltage and current sensor. Multiple (50-100) of those devices are aggregated in a bigger device (DEVICE)
Shall I create a mysql table for each device and each quantity and store the values like:

DEVICE1_voltage:
Timestamp, sub_device1_value, sub_device2_value, sub_deviceX_value...

DEVICE1_current
Timestamp, sub_device1_value, sub_device2_value...

DEVICE2_voltage:
Timestamp, sub_device1_value, sub_device2_value, sub_deviceX_value...

DEVICE2_current
Timestamp, sub_device1_value, sub_device2_value...

...

Or is a relational scheme like:

DEVICES:
id, name

sub_devices:
id, DEVICE_id

voltage_reads:
id, timestamp, sub_device_id, value

current_reads:
id,timestamp, sub_device_id, value

more appropriate? The advantage I see with version 2 is that my scheme will never change, as with version 1 I get two new tables with each new device. These Tables then have different numbers of colums as they differ in sensor setups.

The big disadvantage of version 2 is see is when I get a lot of measurements and the table grows very big....plus that when I query Data this is already the format I need later

Which scheme is better in "theory"?

Thanks in advance!

Edit: Renamed tables and colums, to make more clear that each device can have multiple sub devices


texts: How to update foreign keys in a large fact table using FORALL?

texts:
I have 2 tables, one is a fact table (100000000 records), and the other is a catalog (2000), I need to update the FK at the fact table, can I use FORALL in this case?. I wrote this but it doesn't work.

CURSOR cur_matched IS
SELECT  PER_C_PERIOD_ORIG, PER_C_PERIOD
FROM   TBL_CATALOG
order by PER_C_PERIOD;

registro cur_matched%ROWTYPE;

VAR_PER_C_PERIOD_ORIG VARCHAR2(150);
VAR_PER_C_PERIOD  NUMBER;
vEstatus varchar(3);

TYPE tab_matched IS TABLE OF TBL_BIG_TABLE.PER_C_PERIOD%TYPE;

rec_matched tab_matched := tab_matched();
rec_match_tot tab_matched := tab_matched();

indx  number;

BEGIN

         OPEN cur_matched;
         LOOP

                indx := 0;

             FETCH cur_matched INTO VAR_PER_C_PERIOD_ORIG,VAR_PER_C_PERIOD;
             EXIT WHEN cur_matched%NOTFOUND;

                 SELECT  PER_C_PERIOD_ORIG
                 BULK COLLECT INTO  rec_matched
                 FROM TBL_BIG_TABLE
                 WHERE PER_C_PERIOD_ORIG = VAR_PER_C_PERIOD_ORIG;

                 FORALL indx IN 1 .. rec_match_tot.COUNT
                 UPDATE TBL_BIG_TABLE
                 SET PER_C_PERIOD =  VAR_PER_C_PERIOD
                 WHERE PER_C_PERIOD_ORIG = rec_match_tot(indx);

                COMMIT;

        END LOOP;
        CLOSE cur_matched;


texts: SQL query to replace attribute value with another attribute value- postgre

texts:
I have a table on PostgreSQL as below:

  id | user_id | attribute_key | attribute_value

I have data in the table as below:

1  | 1 | first_name | Jesus
2  | 1 | last_name  | Christ
3  | 1 | Email      | oldEmail1@test.com
4  | 1 | newEmail   | newEmail1@test.com
5  | 2 | first_name | Ram
6  | 2 | last_name  | Krish
7  | 2 | Email      | oldEmail2@test.com
8  | 2 | newEmail   | newEmail2@test.com
9  | 3 | first_name | Muhammad
10 | 3 | last_name  | Nab
11 | 3 | Email      | oldEmail3@test.com
12 | 3 | newEmail   | null

Now I need to check for each user_id, is there a "newEmail" exist and if exist, the corresponding "Email" value should be updated with "newEmail"value. and delete the "newEmail" attribute.

Expected output of above data table as below:

1  | 1 | first_name | Jesus
2  | 1 | last_name  | Christ
3  | 1 | Email      | newEmail1@test.com
5  | 2 | first_name | Ram
6  | 2 | last_name  | Krish
7  | 2 | Email      | newEmail2@test.com
9  | 3 | first_name | Muhammad
10 | 3 | last_name  | Nab
11 | 3 | Email      | oldEmail3@test.com

Can anyone help me with this?


texts: One table with 2 foreign key mapping same primary key

texts:
SELECT
  status,
  schedule_switch.Switch_Employee_ID,
  schedule_switch.Requesting_Employee_ID,
  employee_info.FirstName,
  DATE_FORMAT(schedule_switch.date, '%m-%d-%Y') AS switchdate,
  DATE_FORMAT(time_slot.date, '%m-%d-%Y') AS date,
  start_time,end_time
FROM
  work_schedule
    join time_slot on work_schedule.Slot_ID = time_slot.Slot_ID
    join manager_info on manager_info.Manager_ID = time_slot.Manager_ID
    join schedule_switch on work_schedule.Schedule_ID = schedule_switch.Schedule_ID
    join employee_info on schedule_switch.switch_Employee_ID = employee_info.Employee_ID
    join employee_info on schedule_switch.requesting_Employee_ID = employee_info.Employee_ID
where
  status is null
order by
  switchdate;

I am stuck with this query. I need to get the first name of switch_employee_id and requesting_employee_id both connected to one primary key Employee_ID in the employee_info table.


texts: Should I disable filesytem journaling for MS SQL Server (Linux/Windows)

texts:
For a PostgreSQL server installation on Linux, it is beneficial to disable filesystem journaling.

  Tip: Because WAL restores database file contents after a crash, journaled filesystems are not necessary for reliable storage of the data files or WAL files. In fact, journaling overhead can reduce performance, especially if journaling causes file system data to be flushed to disk. Fortunately, data flushing during journaling can often be disabled with a filesystem mount option, e.g. data=writeback on a Linux ext3 file system. Journaled file systems do improve boot speed after a crash.

from: Official documentation Write-Ahead Logging (WAL)

Unfortunately I did not found similar information about SQL Server on Windows or Linux, but...

Since SQL Server Sever as all modern relational database management systems do WAL, it seems logical that one also can and should  disable file-system journaling on a Ext4 filesystem which is dedicated for MS SQL Server data on a Linux installation , and perhaps the same statement stands for NTFS on a Window installation.

In what measure the previous statements are true?


texts: Giving permission for each table within a database

texts: I want to give different permission for different tables within a single database for a user.For example a user has full permission to a table while for another table only read permission.How it can be done within a database?


texts: Finding Damage Done by FOREIGN_KEY_CHECKS=0

texts:
MySQL allows an end user to temporarily disable foreign key checks with the following statement

SET FOREIGN_KEY_CHECKS=0;

This means it's incredibly common (at least in my corner of the world) to find production MySQL databases where some rows have invalid foreign keys.  For example, if you setup two tables with a foreign key relationship

CREATE TABLE `foo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `bar` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `foo_id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

and insert some legitimate data

insert into foo(id,name) VALUES (1,'Test 1');

insert into foo(id,name) VALUES (2,'Test 2');

insert into bar(name, foo_id) VALUES ("Hello", 1);

and then use the above statements to turn off foreign key checks and set some bogus data

SET FOREIGN_KEY_CHECKS=0;
insert into bar(name, foo_id) VALUES ("Hello", 100);
SET FOREIGN_KEY_CHECKS=1;

You now have a bar table with a foo_id of 100, but no such row in the foo table.

My Question: Does MySQL have any built-in tools for finding this sort of bogus data?  If not, are there common DBA tools for finding this sort of bogus data?  Or am I writing a program to manually scan every table for foreign_keys, and then check each row individually?


texts: Optimize the DB select query JSON B type extract

texts:
We have a query which we want to optimize for high performance.
The message column in table is of JSONB type and it holds a large json object.
Is there a way to optimze this?
We have an index already on the batch_num
SELECT jsonb_array_elements_text(message #> '{results}') AS res FROM TABLE WHERE batch_num = '11';


texts: Why shouldn't a pre-hashed password be used with CREATE LOGIN all the time?

texts:
(For the purpose of this question, assume that I only need to support SQL Server 2008 and higher. Or to future-proof this question, let's say recent, current, and near-future versions.)

The documentation for the CREATE LOGIN statement says (emphasis mine):

  HASHED
  Applies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option is not selected, the string entered as password is hashed before it is stored in the database. This option should only be used for migrating databases from one server to another. Do not use the HASHED option to create new logins. The HASHED option cannot be used with hashes created by SQL Server 7 or earlier.

There's no explanation as to why this option shouldn't be used to create new logins, and if there is a reason, it's not obvious (to me, at least).

The documentation references KB918992, which while a little unclear, describes steps that indicate legacy (2000-2008 R2) password hashes are upgraded automagically. So I can take a password hash generated on 2000-2008 R2 and CREATE LOGIN ... WITH PASSWORD ... HASHED on 2012(+?) and have the hash converted to 2012 format when the principal logs in for the first time. I tested this does indeed work as described.

The password hash itself appears to have a 6-byte signature/metadata header, consisting of what I think is a 2-byte tag/version and a 4-byte salt. When a hash is upgraded, the tag/version is incremented while the salt remains the same.

The question is then: if Microsoft has already baked in this kind of future compatibility (which I assume will need to be maintained going forward), is there any reason why this method shouldn't be used for creating new logins?


texts: Can I reduce MySQL memory usage, or do I need to add more RAM?

texts:
I have a VPS with 2.25GB of RAM. MySQL is using 1.4GB with only four users on my site right now. It seems to use this amount even when no users are online. The available memory of my server regularly dips below 100MB, which is disturbing.
Yesterday MySQL kept crashing with OOM errors and resulted in database corruption, so that's what really prompted me to look into this issue.
I'm using the following configuration generated by the Percona Configuration Wizard (slightly modified):
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5_20120208
# MODIFIED SLIGHTLY

[mysql]

# CLIENT #
port                           = 3306
socket                         = "/var/lib/mysql/mysql.sock"

[mysqld]
slow-query-log=1
long-query-time=1

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = "/var/lib/mysql/mysql.sock"
pid_file                       = "/var/lib/mysql/mysql.pid"

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 256M     # Percona said 16M, but I need it larger to avoid errors
max_connect_errors             = 1000000

# DATA STORAGE #
datadir                        = "/var/lib/mysql/"

######### removed the following because the logs were taking up TONS Of space:
# BINARY LOGGING #
#log_bin                        = /var/lib/mysql/mysql_bin
#expire_logs_days               = 14
#sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0 # was previously 1
query_cache_size               = 0 # was previously 32M
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535 # was previously 4822
table_definition_cache         = 1024
table_open_cache               = 2048 # was previously 1024

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 1456M

# LOGGING #
log_error                      = "/var/lib/mysql/mysql_error.log"
log_queries_not_using_indexes  = 1

As you can see, I increased max_allowed_packet, open_files_limit, and table_open_cache. I made the changes because I was receiving errors (I forget exactly what was causing them now) and making these changes caused the errors to go away.
Also, I noticed the innodb_buffer_pool_size setting is close to how much RAM MySQL is using (which may just be a coincidence), but as this answer suggested I ran the following:
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

And the output was 2, which I believe means the recommended innodb_buffer_pool_size is 2GB. If that's correct, then my current setting is even below the recommended amount.
I really don't know what I'm doing and would prefer not to screw things up by blindly making changes to my.cnf. Are there changes I can make to reduce memory usage, or do I just need to upgrade my VPS plan?


texts: SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell

texts:
I've installed and successfully configured our SQL Server 2012 AlwaysOn 2-node servers for our new "Intranet" that is coming out. I've gotten AlwaysOn working great, and our Front End servers for the Intranet will be using SharePoint 2013. The glitch is that SharePoint 2013 is configured to add databases automatically to our SQL Server 2012 back end, but NOT to AlwaysOn. In reading about this and in contacting Microsoft MSDN support, the default answer is "you must manually find, select, back-up and then add those new databases individually to get them into AlwaysOn."

But wait; that can be quite a task, constantly checking the SQL Server back-end servers to see what databases were created, then having to add them into AlwaysOn, 7/24!   I'm looking for a script or process that will check for new databases, back those new databases up in FULL mode, (for being added to AlwaysOn, of course) then add those databases to AlwaysOn, all automatically. Or have this run every...1-2 hours? (without user intervention)

What I've come up with so far is this script that actually identifies the newly-added databases, (not yet in AlwaysOn), and then backs them up to a shared location. My next task is to find those newly-added databases and through the various processes needed, get them added to AlwaysOn. This will involve some sort of looping action, I imagine. I'm not a T-SQL/scripting guru; is there any solution or script that I might access that would do this? (add databases to AlwaysOn automatically)?

Please advise, I'm sure I'm not the first person to have this issue. I have seen previous posts on various Internet Sites (including this one!) , and the solution is either incorrect, or states something like "sure, go ahead and just script that!". Thanks, but I need just a little more detail there.

Thanks again,

-Allen

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup

-- specify database backup directory
SET @path = '\\atel-web-be2\backups\'

DECLARE db_cursor CURSOR FOR
select name from sys.databases
where group_database_id is null and replica_id is null
    and name not in('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @fileName = @path + @name + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName
    FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor


texts: How to drop and recreate catalog view in postgres

texts: Can any one please help me to drop and recreation of sys views in EDB(Postgres).


texts: How to maintain a global index on a large table undergoing regular partition purges?

texts:
Problem

I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate querying by column needle. This is simple enough.

The issue arises at the end of the month when the oldest table partition is dropped. I can't afford to rebuild a global index on a 3 TB table every month. Only the four most recent month partitions of haystack are in the database at any given time. So roughly 25% of the table is removed when dropping a monthly partition.

Attempted Solutions

I've tried creating an index on column needle which was global partitioned by hash on needle. But this means having to rebuild the entire global index when dropping an old month partition from the table. No good.

I've also tried creating an index on columns (date, needle) which was global partitioned by range on date with ranges that match the table's monthly partitions. But again, I'm still forced to update my entire global index when dropping that old month partition from the table - even though my table and index are partitioned identically (excepting the table's subpartitions).

Of course, I can't simply create a locally partitioned index. I would need the partitions to be local but without the subpartitions. From what I can tell, that's not possible; it's an all or nothing choice with the local partitioning option on indexes.

Dropping Partitions

For completeness, here's a few options I've tried for dropping old partitions at the end of the month...

alter table HAYSTACK drop partition P_MONTH update global indexes;
This takes far too long, as it attempts to maintain the global indexes during the drop.

alter table HAYSTACK drop partition P_MONTH; Without the global index clause, my global index is left with all of its partitions marked unusable. (Understandably so.)

alter index HAYSTACK_IDX drop partition P_MONTH; Dropping my index partition invalidates the partition whose high value was just above the dropped partition's. If I were to go on and drop the table partition now, I'm still going to have to rebuild my entire global index.

My Question

Is there a way I can purge old table partitions without invalidating or rebuilding the global index partitions? Essentially, can I somehow tell Oracle to handle these global index partitions as if they were local, but without subpartitioning? (Sadly, my research says no.)

Or... Is there another (better?) way to solve my problem?


texts: Count business days between 2 dates in postgreSQL


texts: Sessions, Processes, Object Instances, Sequential and Parallel Processing Theory

texts:
A stored procedure contains a view which is accessed multiple times with the confines of the SP.

If the SP is executed multiple times in one query window with different parameters, e.g.

EXEC spMySP 1, 2, 3
GO
EXEC spMySP 4, 5, 6
GO

Questions

If one assumes that the first call will complete execution before the second one starts and therefore the view within the SP, will it be bound by the connection, session and process?
If we were to place each SP execution in its own query window and execute the all, roughly at the same time - we assume they would execute asynchronously?
Can we assume that the view would within the SP be operating within a new instance (More a theoretical question)?


texts: Weak entity with a many-to-many relationship with its owner

texts:
I have a database that keeps track of the hours worked by a contractor per week. I have one table for contractors that contains "con ID" as primary key. The other table Weekly Hours Tracker contains month day year (representing a particular week) and hours worked. Now from what I understand this table is a weak entity and relies on contractor table for its existence.

The relationship is defined as one contractor can work for many weeks and in one week there are many contractors working. So there is a Many to Many relationship.

So is the Weekly Hours Tracker a weak entity that has a many to many relationship with its owner entity Contractors?

Is following the correct way to represent the above tables?

(Bold attributes are keys)

Contractors Table

ConID, Name, HourlyRate, Address

Weekly Hours Tracker

ConID, Day, Month, Year, HoursWorked


texts: Ola Hallengren - Parallel Backup Jobs failing on SINGLE_USER DB - 'Database is in transition' [952]

texts:
I'm using Ola's DatabasesInParallel option for "multi-threaded" execution. Last night a db was temporarily placed into SINGLE_USER for an application-based archive process and it caused all 5 threads of my parallel backup jobs to fail when attempting to backup the log of this database.

If it were any other type of failure the job would log it and continue it's execution. In this case, each parallel job tried to back it up and each one failed immediately, skipping log backups on hundreds of databases.

Fortunately, they were all backed up again 15 minutes later with no issues, but it was quite alarming to have our on-call resource get woken up at 3am.

The error at the very bottom of each job's log file is as follows:

Msg 952, Sev 16, State 1, Line 1:
Database 'FAKEDBNAME' is in transition. Try the statement later. [SQLSTATE 42000]


texts: Postgres ON DELETE Rule Not Working With WHERE Clause

texts:
I'm trying to implement "Soft Deletes" given the following schema:

CREATE TABLE categories(
  id serial not null primary key,
  num integer,
  "name" text,
  deleted_at timestamp default null
);

CREATE OR REPLACE RULE delete_categories AS
  ON DELETE TO categories
  WHERE old.deleted_at IS NULL
  DO INSTEAD
    UPDATE categories SET deleted_at = NOW()
    WHERE categories.id = old.id;

The expected behavior is that if I try to delete a record with a NULL deleted_at value, it will instead be set to the current timestamp.  If I try to delete a record with a non-NULL deleted_at value, it will be deleted normally.

Instead, running the below sequence returns no records, instead of a record with a timestamp in the deleted_at column:

insert into categories(num,name,deleted_at) values(9999,'Test Category',null);
delete from categories;
select * from categories;

It appears as though the RULE does not get triggered at all, and the record is simply deleted, whereas if I modify the RULE by commenting out the WHERE clause, the record is updated as expected but I am barred from being able to delete it fully:

CREATE OR REPLACE RULE delete_categories AS
  ON DELETE TO categories
  -- WHERE old.deleted_at IS NULL
  DO INSTEAD
    UPDATE categories SET deleted_at = NOW()
    WHERE categories.id = old.id;

insert into categories(num,name,deleted_at) values(9999,'Test Category',null);
delete from categories;
select * from categories;

Results:

+----+------+---------------+----------------------------+
| id | num  |     name      |         deleted_at         |
+----+------+---------------+----------------------------+
|  3 | 9999 | Test Category | 2015-03-03 20:05:44.660208 |
+----+------+---------------+----------------------------+


texts: SQL Server: Interplay of personal login and group membership

texts:
I created a login for a user using his windows user then I created a login for an AD group in which the same user is a member. I did NOT give any access to the personal login any access to the DB and did not add this login to any server or DB fixed roles. I gave the AD group access to the DB. I have then DISABLED the 1st login (the one using the Windows user) and you know what? The user lost access to the DB!!! When I re-enabled the login (the one using the Windows user) the user again had access to the DB. From this observation I conclude: .) For the authentication when connection to the server, SQL Server either gives precedence to the personal login over the AD group login OR it simply scans the syslogins table to find the first login (which is either the user's personal login or an AD group in which the user is a member) .) After the successful authentication on the instance level, the resulting permissions on the DB is the cumulative permissions of the personal login + of all the AD groups the user is member of.

The first part of this observation does not seem right to me at all. Is this a bug?

Has anyone observed the same behaviour?

TO CLARIFY: The personal login was NOT mapped to the DB at all. The login based on the AD group WAS mapped to the DB and had db_datareader fixed DB role assigned to the user. Still, when I disabled the personal login the user lost all access to the DB. This is IMHO an important point. I did not assign DENY permission to the personal login, I simply disabled it. Therefore my expectation was that the user would continue to have access to the DB through his group membership. But that wasn't the case and that doesn't seem right to me.


texts:
I want to alter the view for the drop tables.

For an example:

View:

create view View1 as
select * from table1
union all
select * from table2
union all
select * from table3
union all
select * from table4

I droped table1 and want create MasterView contains View1.

Note: Need to alter view view1 as table1 has been droped.

My try:

create procedure udsp_createallview
as
begin

declare @sql varchar(max) = ''
declare @tbl varchar(max) = ''
declare @view varchar(max) = ''
declare @create_view varchar(max) = ''
declare @query nvarchar(max) = ''
declare @create_view1 varchar(max) = ''
    begin try

        set @sql = 'create view dbo.MasterView AS
                    select * from View1
                    union all
                    select * from View2';

        print(@sql);
        exec(@sql);

    end try
    begin catch

        select @tbl = replace(SUBSTRING(ERROR_MESSAGE(),CHARINDEX('''',ERROR_MESSAGE()),LEN(ERROR_MESSAGE())),'''.',''''),
               @view = ERROR_PROCEDURE();

    end catch

    if @tbl <> '' and @view <> ''
    begin

        select @create_view = VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS WHERE Table_name = @view

        --set @create_view = 'replace(replace('+@create_view+','+@tbl+','''')';
        set @query = 'select @Result = replace('''+@create_view+''','+@tbl+',''''),''create'',''alter'')';

        --exec(@query);
        exec sp_executesql @query, N'@Result varchar(max) out', @create_view1 out
        print(@create_view1);

        print(@tbl);
        print(@view);
    end

end
go

exec udsp_createallview;
go

Unable to remove rest of the select statement part. Sometimes select statement may come with where clause.


texts: Admin DB replicated in MongoDB replica set

texts: I'm setting up a mongodb replica set across 3 different hosts. If you create the admin user on one host and turn on auth and keyfile in the /etc/mongodb.conf, do you have to create that same user on nodes 2/3?  Or can I just add those nodes to the replica set and the admin database will be replicated with the admin user I added from node 1 to the other 2? I'm having some trouble doing that if I bring up the other nodes with auth/keyfile turned on.


texts:
I use galera cluster with 4 nodes, mariadb, we insert nearly 12million data. Insert task was finished. However we have lost 11000 data. I don't know where they go. In error log it writes;

151220 17:14:46 [ERROR] mysqld: Deadlock found when trying to get lock; try restarting transaction

I looked Show engine innodb status in all nodes. However I couldn't see any deadlock information. It looks everything is going fine. what should I do, what does error mean?


texts: ORDER BY gives ERROR: function array_position(text[], character varying) does not exist

texts:
I have a pretty basic categorical column in a Postgres database that is currently stored as VARCHAR. I can select a count of each with:

I though adding an ORDER BY array_position() would do it:

SELECT color, count(*)
FROM research
GROUP BY color
ORDER BY array_position(ARRAY['Red','Orange','Yellow','Green','Blue'], color);

But I'm seeing a type error:

ERROR: function array_position(text[], character varying) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 98

What do I need to cast color as so I can use the array_position function to order it?


texts: MySQL query to reference previous field in subqueries

texts:
I have a question on how to best do this.

I have a table that contains how users are enrolled in a class, say "manual", "db", "email", etc. (enrolment type).

each class has a list of their own class enrolment type with their own ids. For example class A, B, C, etc... have their own enrolment type id's for their class. In other word,s class A's and class B's "db" enrolment type id is different and only for their class use.

So what I want to do is to first look up all class enrolments with a certain type, say "db" (id=88) for each class and use that output to feed into another query that shows what their enrolment type id is for "manual" (id=123) associated with that class. Then the last query would update statement to change the enrolment type to what we want to "manual" instead of "DB".

How can that be done in subqueries? I've written them but as a scripting language in PHP to use their loop function but I want to try and learn to write it as a nested subqueries to accomplish the same in MySQL.

Table:

class

classid | name | start date |... etc.

1 | ..
2 | ..
3 | ..
4 | ..

class_enrolment

classid | userid | role | enroltypeid |.. etc
1 | 1234 | student | 40 | ..
1 | 5678 | student | 40 | ..
1 | 9012 | student | 40 | ..
1 | 3456 | student | 40 | ..
1 | 7890 | student | 40 | ..
1 | 7521 | instructor | 40 | ..

class_enroltype

classid | enrolid | enrolname | etc.
1 | 39 | manual | ..
1 | 40 | db | ..
1 | 41 | ldap | ...
1 | 42 | email | ...
....
34 | 897 | manual | ..
34 | 898 | db | ...
34 | 899 | ldap |...
34 | 900 | email | ...
...
1045 | 6234 | manual | ..
1045 | 6235 | db | ...
1045 | 6236 | ldap |...
1045 | 6237 | email | ...

In the above example for classid number 1, I want to change their enroltypeid from "40" to "39" and the same logic for all other enrolments in other respective classes ("db" to "manual" enrolment type).

thoughts?

thanks!


texts:
I'm just getting started with Postgres. Reading this document I came across this query:

SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;

I can understand everything in this query, except for this: FROM apod, ....

What does this , mean? I'm used to joins but not to multiple FROM statements separated by a comma.

I searched the net for no avail. After looking at it and thinking, it seems to me that it's declaring a variable called query so it can use it multiple times. But if this is true, what does it have to do with FROM?


texts: Master-Master replication MariaDB failover resync

texts: I'm working on a project to replicate two sites running MariaDB 5.5 version. Replication works flawless until a host restarts, losing connection still works after reconnecting. When the node restarts if i manually enter the new master status to both servers they resume and replicate all data which were non replicated. Is there a way to auto-resume and resync the replication whenever something like this happens? Nodes are in a production environment so downtime with no recovery isn't allowed. Thanks in advance


texts: MySQL: What does event `/io/table/sql/handler` mean?

texts:
Yesterday I experienced a MySQL incident

the load was extremely high
All select statements takes more than 3 times time than usual.
All insert statements takes 10+ times time than usual.

The MySQL performance monitor tool shows:

there are lots of /io/table/sql/handler has been generated during the period of incident.
lots of active threads within the period of incident.
lots of blocked thread within the period of incident.

Questions

What does /io/table/sql/handler mean?
Do you know the possible root cause?


texts: PLSQL search procedure to return best and near matches based on multiple criteria

texts:
i have a requirement to write a stored procedure that takes 6 potential parameters.
I need to do a search on a table that returns rows that match as closely as possible to the search parameters entered.

For example.

Table quotes
(status {incomplete, pending, ordered},
date_created date,
type {autoGenerated,manual,split}
company {PipedPiper, Hooli,Oracle...}
...
)

Here is a use case to illustrate what i need to achieve.
Lets say a user enters the following search criteria on the front end:
they want to get a list of quotes that met the following criteria, or the next best match:

quote status = pending
company = PipedPiper
type=manual
date = Not selected

So, once the stored proc is called it should get the list of quotes that meet that criteria.
If, no quotes are in pending then any Piped Piper manual quotes should be returned.
Perhaps there are no pending quote for PipedPiper.
At that point it should return any manual quotes in the db.

The SP needs to return a cursor and also a flag to confirm what criteria entered is met by the row. please note, it should not return anything against the date flag as the user did not select any date entries for this particular search.
For example

if anyone can guide me in how to code this, provide pseudo code, or any guidance is much appreciated.
I am poor at SQL so I am struggling to design an SQL statement that can achieve the above.

thanks in advance,
Adrian.


texts: Error using an Execute SQL script to load a date variable in SSIS

texts:
I'm trying to populate a variable using an Execute SQL task in SSIS.  The table structure is as follows:

CREATE TABLE BuildControl
(Id INT NOT NULL Identity, BuildDate Date,
     Country char(2), BuildStatus varchar(20),
     BuildStatusDate DateTime)

The SQL Statement is

SELECT TOP 1 ? = Id, ? = BuildDate
FROM BuildControl
WHERE BuildStatus IS NULL
ORDER BY Id

and it works fine if I remove the ? = BuildDate.  The parameter mapping tab looks like this:

The error I'm recieving is this:

SSIS package "WFG Statement Build.dtsx" starting.
Error: 0xC002F210 at Get Build Data, Execute SQL Task: Executing the query "SELECT TOP 1 ? = Id, ? = BuildDate
FROM BuildContr..." failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Get Build Data
Warning: 0x80019002 at WFG Statement Build: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "WFG Statement Build.dtsx" finished: Failure.

I believe the problem is with the date output variable since when I remove it I don't have a problem.  Both the server and the version of SSIS I'm using are SQL Server 2008 R2.  The Connection Manager is an OLE DB connection using the Native OLE DB\SQL Server Native Client 10.0 provider.  I've tried changing the table structure to use a DateTime with no success, and tried DBDate and DBDateTime as Data Types in the Parameter Mapping page also with no success.

I'm completely baffled.  Thanks for any help!


texts: Oracle Enterprise Manager - top activity

texts:
I'm looking at the Top Activity view in OEM.  The left axis is labeled "Active Sessions" (up to 24 in the graph I'm looking at) and there is a line labeled "Maximum CPU".  The measured activities include: user i/o, concurrency, commit, etc...

My question:  Is OEM simply reporting the number of sessions involved in the activity, as the axis indicates?  Or is it measuring the total duration of the activity over all sessions?

To put a point to it, concurrency (we believe) is the activity of presenting a consistent block to each session that requires it.  Session A may see one version block 33.444  where session B might see a different version of block 33.444.  (Because session A cannot rows updated by B which are not yet committed, and vice versa.)

But presenting a consistent version of a given block might be a simple task, or it might  be a complicate task depending on how many uncommitted changes have been made to a block, and how many sessions are interested in it.

So if "concurrency" simply counts the session involved (as the vertical axis indicates) then we get one interpretation of the graph.  But if it counts the duration, we get a different view.

Any insights on what OEM specifically measures would be appreciated.  Thanks in advance!

Chris Curzon


texts: Does the collation of ENUM columns impact their size?

texts:
What does it mean when we say that an ENUM has a charset of utf8mb4, versus latin1?
My understanding is that the ENUM holds a numeric index (1, 2, 3, 4...) that identifies which of the ENUM values (abc, def, ghi, jkl...).
If I use utf8mb4 for an ENUM column, is the column using more storage than it would if I used latin1?


texts:
I've been trying out GCPs SQL high availability options. Is there a way to improve the fail over speed?

Attempting manual failovers through the UI takes literally over 5 minutes where mysql is completely unavailable.

I am using the smallest second generation 5.7 instances possible currently with no traffic hitting it.

It looks like it's actually stopping and starting instances. I'm having trouble seeing how this is an option for "HA" meaning minimal downtime.


texts: sys.partitions row count is badly wrong - how to correct this?

texts:
Querying sys.partitions can return an approximate row count for a table.

I've noticed that this is returning the same row count for all partitions, regardless of the actual content (even for empty partitions).

The table has a clustered columnstore index and statistics have been created on almost all of the columns. Statistics are updated daily after each data load. The table is partitioned by date.

sys.partitions query:

    SELECT   convert(date, convert(varchar,rv.[value])) as partitionDate, p.rows as syspartitions_RowCount
        FROM       sys.tables t
        join       sys.schemas  sc on sc.schema_id = t.schema_id
        JOIN        sys.partitions p                ON      p.[object_id]         = t.[object_id]
        JOIN        sys.indexes i                   ON      i.[object_id]         = p.[object_id]
                                                    AND     i.[index_id]          = p.[index_id]
        JOIN        sys.data_spaces ds              ON      ds.[data_space_id]    = i.[data_space_id]
        LEFT JOIN   sys.partition_schemes ps        ON      ps.[data_space_id]    = ds.[data_space_id]
        LEFT JOIN   sys.partition_functions pf      ON      pf.[function_id]      = ps.[function_id]
        LEFT JOIN   sys.partition_range_values rv   ON      rv.[function_id]      = pf.[function_id]
                                                    AND     rv.[boundary_id]+1      = p.[partition_number]
        WHERE   p.[index_id] <=1
                and t.[name] ='tbl'
                and sc.name = 'temp'
                and convert(date, convert(varchar,rv.[value])) > '2016-05-31'
                order by convert(date, convert(varchar,rv.[value])),
                t.[name]

table query:

                    select date, count_big(*) as real_count
                from temp.tbl
                where date > '2016-05-31'
                group by date
                order by date

Sample results from both queries:


texts: build users availability report/Table (using sql)

texts:
I got a question from our product team to build a "Table availability of customer service representatives (user)". f

For example : user_A was available to accept a call from 12:00 to 12:03, so I have in the database a table with UserName = user_A,StartDatetime = 20151110 12:00,EndDateTime = 20151110 12:03.

From this data I need to create an availability report that can show which users were available in minute interval (every minute) along the all day .


texts: Alternative for taking the backup of SQL DB's on backup drive

texts:
We just got new servers to take care of, regarding their backups, handed over by the company B who used to manage the backups, but they never did via any mant'ce plans or SQL jobs.They did that task through NBU client. Thus backups were never backed up on the drives on that respective SQL server.

Now, as per the security/audit findings we need to have backups on the drive.

But the issue is we never had any backup drive on those servers. Moreover many of the servers only have a single data drive.

Therefore we came up with a plan to back them over NW onto the server with ample amount of space as of now, till we get the backup drives installed on each server.

But again we came with a challenge of resources that is being utilized while copying / taking backups over the NW like CPU and Memory usage got high.

Can someone help with their expertise on what alternative can be used, because we are short of space as of now and adding a drive per our process takes a month.

Kindly suggest!

Thank you!


texts:
I have a MySQL db on Machine A and I want to access the mysqldump from machine B.

Both machines are running on Linux and I also have the credentials of both, but I can't access the mysqldump. What can I try?


texts: Search 200M documents of text by keyword in text

texts:
Data

~5M documents, would love to have ~223M, with the text of ~10-1000 words each. It must be utf8 and support any spoken language. All words in a text are unique and I can also order them or do whatever needed to have better performance. Full text is saved in another location. Data is almost read-only. For performance, I can generate data, add indexes and mark it as read-only.

Data structure:

{
  id: 1,
  reference_id: 1,
  text: 'text of unique. 10-1000 words each ',
}

Requirements

Results must come under 0.1s
Sample queries:

find text with words: x or y or z
find text with words: x and y and z
would be great to find text with part of the word: hous would find house, housing etc.

Questions

Is it possible to make it happen with MySQL/PostgreSQL?
What database solutions you would offer?
What config to look at?
Index type?
Query sample?
Server requirements for RAM/HDD/CPU?
Better more powerful machines or array of simpler?


texts: Using PostgreSQL 8.4, how to convert bytea to text value in postgres?

texts:
In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly fine i.e. results in Octet format String. See below example,

Input String : \n\t\f\b\p\k\j\l\mestPrepared

Output String : \\012\\011\\014\\010pkjlmestPrepared

Output String is inserted in the database. Now I retrieve that data from the database in a java code using JDBC. How I can unescape the string back to its original value ?

I thought of two possible approaches,

Change the database retrieval query and pass this field to any String manipulation function of postgres i.e. which can convert bytea to text.
Do the decoding in Java code.

I can understand that the approach 1 will be more efficient. I have tried almost all the functions listed here but nothing is working. Please Help!!

I am using version 8.4 of postgres on a linux machine.


texts: migrating from one SQL cluster to another cluster

texts:
I have 2 existing SQL clusters. Both 2008 R2. Each have unique instance names.

Server1\inst1

ServerA\reports.

We are decommissioning Server1 cluster and need to migrate DBs over to ServerA\reports

So I don't have to change application and SSRS report connection strings is it possible to add a SQL alias of Server1\inst1 to ServerA\reports.

If possible this would allow you to connect using either Server1\inst1 or ServerA\reports.

Thanks,

-Jess-


texts: How to see today compiled object in Oracle?

texts: Is there a way to find objects that were compiled today?


texts: MySQL / Fusion IO Configuration Question

texts:
I need a little configuration optimization. I think what I have is pretty good but feel like there's still room for improvement.

Current Config

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#innodb
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M

innodb_read_io_threads=8
innodb_write_io_threads=8

innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=8
innodb_io_capacity=10000
#eliminating double buffering
innodb_flush_method = O_DIRECT
flush_time=86400

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
#tmpdir=/dev/shm
#tmpdir=/usr/tmpfs
skip-name-resolve
#skip-locking
#safe-show-database
query_cache_limit=4M
query_cache_size=256M
sort_buffer_size=8M
read_rnd_buffer_size=1M
max_connections=5000
interactive_timeout=60
wait_timeout=300
connect_timeout=30
thread_cache_size=32
key_buffer=124M
tmp_table_size=4096M
max_heap_table_size=256M
join_buffer=16M
max_connect_errors=2000
table_cache=2048
thread_concurrency=12
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log
#table_definition_cache=384
max_allowed_packet=1024M
#server-id=20
#log-bin=mysql-bin
#expire_logs_days=10

event_scheduler=ON

#master-host =
#master-user =
#master-password =
#master-port = 3306

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

Dedicated Database server specs

VMWare VM
Dual Quad Xeon X5680  @ 3.33GHz (8 vCPU)
24gb RAM
Fusion IO mounted on /var/lib/mysql
Rough total of 500 tables between all databases

I know this kind of question really kind of requires eyes on the database server but I will gladly append information to this question as needed in order to get a much better configuration.

Thanks for the help in advance.


texts:
I have a CTE statement that returns line item information for me.  This is great, but I need to manipulate this in order to only get aggregate counts by Employee where the criteria is met.  For example data when this query is executed this is returned

Employee saleid   SaleAmt  SaleDate
Emp A    abc123   1100  2017-01-03
Emp A    def456   1200  2017-01-03
Emp B    ghi789   1500  2017-01-09

Now all that I want returned is

Employee  Count
Emp A 2
Emp B 1

How should this query be modified in order to only return aggregate data?
**Note I am using the RTRIM() and the LTRIM() functions as sometimes a user will input extra whitespace before or after an employee name (cleanup is going to take place soon :)--)

WITH DollaAmt AS
(
    Select
    cm.empName employee
    ,jb.saleID saleID
    ,CAST(COALESCE(jp.saleamt,0) As DOUBLE PRECISION) DollaAmt
    ,CAST(jp.saleDate As Date) saleDate
    FROM salePieces jp
    Inner Join sale jb
    on jp.saleID = jb.saleID
    Inner join employee cm
    on cm.esID = jb.esID
    WHERE jp.saleDate BETWEEN '2017-01-01' AND '2017-03-31'
    AND cm.empStatus IN ('A', 'E', 'L')
    OR RTRIM(LTRIM(cm.empName)) = 'Trainee'
),
employees As
(
    select
    cm.empName As Employee
    ,cm.empStatus As employeeStatus
    FROM employee cm
    ORDER BY Employee ASC
)
Select
DISTINCT(RTRIM(LTRIM(Employee))) As "employeeName"
,ia.saleID
,"TotalDollaAmt"
,ia.saleDate
FROM employees cm
JOIN (
        Select
        RTRIM(LTRIM(employee)) As employee
        ,SUM(DollaAmt) As "TotalDollaAmt"
        ,saleDate
        ,saleID
        FROM DollaAmt
        GROUP BY RTRIM(LTRIM(employee)),saleDate,saleID
) As ia
ON RTRIM(LTRIM(ia.employee)) = RTRIM(LTRIM(cm.Employee))
WHERE CAST(saleDate As Date) BETWEEN '2017-01-01' AND '2017-01-31'
AND "TotalDollaAmt" >= '1000.00'


texts: @TimeLimit setting in Ola-Hallengren's IndexOptimize Running Much Longer Than The Value Set

texts:
We start our indexing at 10:00 PM every evening and we want to allow it run until 4:30 AM, the next day.  Therefore, we set the @timelimit variable / parameter to 23400 seconds.  The issue is that the system continues to run well past the allotted time, by over 3607 seconds, or until I shut it down.
Thoughts on why it would not stop processing all commands, which is what the code is to promote via the variable?


texts: Slow running aggregate of aggregate query

texts:
I am looking for the total balance for all accounts that had a negative balance as of a specific point in time.  I've already got the code below, and it works, but works horribly.

The transactions table contains 20 million rows. The query below takes about 30 seconds which isn't bad, but there's a second part in that I need to repeat @report_date for the first of every month since the inception of the database, which balloons the execution time to ~30 minutes.

This is on MSSQL2008 and I'm not getting any missing indexes warnings on the execution plan, but I suspect my problem is still with the indexes so I'm intentionally leaving them off of here.

CREATE TABLE transactions(
    transaction_id int,
    account_id int,
    department_id int,
    location_id int,
    post_date date,
    amount money
    );

SELECT
    t2.department_id,
    t2.location_id,
    SUM(t2.credit_balances)
FROM
    (
    SELECT
        t1.department_id,
        t1.location_id,
        t1.account_id,
        SUM(t1.amount) as credit_balances
    FROM
        transactions t1
    WHERE
        t1.post_date < @report_date
    GROUP BY
        t1.department_id,
        t1.location_id,
        t1.account_id
    HAVING
        SUM(t1.amount) < 0
    ) t2
GROUP BY
    t2.department_id,
    t2.location_id;

The table contains 38 distinct department_id,location_id combinations, and 4.5 million distinct department_id,location_id,account_id.


texts: Sybase ASE: How to know if a device has free space to extend a database on it

texts:
I am new to sybase ASE.

I would like to know what are all the databases that have been using a device in sybase instance. sp_helpdevice or sp_helpdb are just giving not enough information.

Is there any such way to know the information in the below format.

device_name   db_id   allocated used free
.             .       .         .    .
.             .       .         .    .
.             .       .         .    .


texts: How could you implement a custom sort that groups matching records from other source

texts:
This is obviously simplified.  I have 2 applications:  Driver and Match.  I'm presenting data to users in this order and asking them to select which of the people in the Match application is truly a match for the person in the Driver application.
Data needs to be ordered as you see below:  Driver rows need to be in alpha order by PersonName.  Below each Driver record, list the Match records that have the same PersonMatchID, in PersonName alpha order.  Some Driver records will have no corresponding Match records.

If you started with the rows below in random order, how would you write a SQL query to return them in this order?  I am dealing with about 3000 rows at most, so I don't have to worry overmuch about optimization.

Source-system    PersonName    PersonPossibleMatchID
-------------    ----------    ---------------------
Driver           AA            77777
  Match          AA            77777
Driver           AB            11111
  Match          AA            11111
Driver           BB            33333
  Match          BA            33333
  Match          BB            33333
Driver           CC            99999
  Match          CB            99999
  Match          CC            99999
  Match          CD            99999
Driver           DD            44444
Driver           EE            22222

I've tried

  SELECT * ORDER BY LEFT(PersonName,1), PersonPossibleMatchId,
  Source-system

but this doesn't return Driver records in strictly alpha order - you get all the A's first, but within that the order on second letter is randomized.  For example, in the above list, I might have Driver AB listed prior to Driver AA.

Added:  I'm using MS SQL (2012)


texts: Adding rows that share a foreign key ID from the same table *in a new row*

texts:
My goal is as follows (simplified):

find the newest record from table events
use a FK events_meta_id to find all records from that same table (events) that have the same value in that field, if they exist

But, I'd like the subsequent records to appear as different rows, not as a part of the same row (like it does with INNER JOIN)

Schemas

CREATE TABLE `events` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `events_meta_id` int(11) unsigned NOT NULL,
  `tickets_quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `events_meta_id` (`events_meta_id`),
  CONSTRAINT `events_ibfk_1` FOREIGN KEY (`events_meta_id`) REFERENCES `events` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `events_meta` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `artist` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

My query is as follows:

SELECT * FROM
(
    SELECT * FROM events
    ORDER BY id DESC
    LIMIT 1
) latest_events
INNER JOIN events
ON latest_events.events_meta_id = events.events_meta_id

events_meta contents

| id |   artist   |
|----|------------|
|  1 | Joe Cocker |
|  2 | BB King    |
|  3 | Nirvana    |

events contents

  id | events_meta_id | tickets_quantity
 ----|----------------|------------------
   1 |              1 |               10
   2 |              2 |               20
   3 |              3 |               30
   4 |              1 |                5

The resultset right now is this:

| id | events_meta_id | tickets_quantity | id | events_meta_id | tickets_quantity |
|----|----------------|------------------|----|----------------|------------------|
|  4 |              1 |                5 |  1 |              1 |               10 |
|  4 |              1 |                5 |  4 |              1 |                5 |

But I want this:

| id | events_meta_id | tickets_quantity |
|----|----------------|------------------|
|  4 |              1 |                5 |
|  1 |              1 |               10 |

Any ideas how?


texts:
I'm having a difficult time wrapping my head around this issue. I need to get the previous status of our finished customers. I learned about the LAG function and it almost does what I need, but the issue is it can potentially get the status from a different customer because the previous row is a different CustomerId.

Is there a way to limit the LAG function to only return a value of null if the previous CustomerId is different or something that will generate the same affect? I created some dummy data here: Rexter Lag Testing

As you can see, the 4th row is for customerId 2, but PreviousStatus value is from CustomerId 1. It should be NULL.


texts:
I have three tables and I want to insert data into that three tables as per their respective data.

Query:

with acupressure_point_data as (
    insert into acupressure_point(id, url)
        values('LL909', 'ww/33/11')
    returning id as acupressure_point_id
),
acupressure_sub_type as (
    insert into acupressure_point_region_sub_type
        (acupressure_point_id, region_sub_type_id)
        values(acupressure_point_id , '9898')
    returning id as acupressure_sub_type_id
);

But it gives the error as:

  "ERROR: syntax error at end of input Position"

I can not understood this. Please help me. Thanks


texts: Two records in the same table must be related somehow

texts:
I've inherited a somewhat strange table whose records must somehow be linked to each other. In the real world, these two "structures" are combined into one larger "structure", so there should be a way of linking them.
Should I use an "association table" to link the two IDs, or is there a better way for this sort of thing? Perhaps I've answered my own question, but I thought I'd ask if there's another way...
Another option would be to delete the second record, at the same time as I add a reference to that second record as a new column, in the first record.
Maybe this is a ridiculous open-ended question for this site, but surely I'm not the first person to run into this...
Thanks,
Sean


texts: How to join two result sets from same table with different conditions on same column?


texts:
Thomas LaRock (SQL Rockstar) put up an article on how to write executed commands directly to the SQL Server Error Log without having to use Profiler (or SQL Trace).  The article/video can be found here.

My question is, would this be advisable to anything beyond quick and limited troubleshooting?  In other words, I can't imagine it wouldn't impact performance by keeping trace flags 4032 and 3605 set for an extended period of time.

Is it a fair statement that the instance would suffer by having all the I/O to the error log for every single statement on the instance?


texts: count aggregate between three date ranges

texts:
I have a mysql query where I want 3 columns of data. day0 to be between now() and - INTERVAL 24 hours
day1 between end of day0 - 48 hours
and day2 between end of day1 and 72 hours

This is what I have so far.

select distinct nc.flowstepname,
       count(distinct(nc.id)) as day0,
       count(distinct(nc.id)) as day1,
       count(distinct(nc.id)) as day2
from sparq.nc nc
where nc.flowstepname like "1%"
    and nc.created > NOW() - INTERVAL 72 HOUR
group by nc.flowstepname ASC;

Like this:

select distinct nc.flowstepname,
       (select count(distinct(nc.id))
        from sparq.nc nc
        where nc.flowstepname like "1%"
        and nc.created = now() - interval 24 hour) as day0,
       (select count(distinct(nc.id))
        from sparq.nc nc
        where nc.flowstepname like "1%"
        and nc.created = interval 24 hour - interval 48 hour) as day1,
       (select count(distinct(nc.id))
        from sparq.nc nc
        where nc.flowstepname like "1%"
        and nc.created = interval 48 hour - interval 72 hour) as day2
from sparq.nc nc
group by nc.flowstepname ASC


texts: Unable to Mask data after applying mask function in Azure Synapse table

texts:
I have created table prd.CustomerDimension

CREATE TABLE [prd].[CustomerDimensions]
(
    [WWI Customer ID] [int] NOT NULL,
    [Customer] [nvarchar](100) NOT NULL,
    [Bill To Customer] [nvarchar](100) NOT NULL,
    [Category] [nvarchar](50) NOT NULL,
    [Buying Group] [nvarchar](50) NOT NULL,
    [Primary Contact] [nvarchar](50) NOT NULL,
    [Postal Code] [nvarchar](10) NOT NULL,
    [Valid From] [datetime2](7) NOT NULL,
    [Valid To] [datetime2](7) NOT NULL,
    [CreditLimit] [decimal](18, 2) NULL,
    [PhoneNumber] [nvarchar](20) NULL,
    [FaxNumber] [nvarchar](20) NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
)
GO

Trying to apply Mask on column PhoneNumber using below query:

ALTER TABLE  [prd].[CustomerDimensions]
ALTER COLUMN [PhoneNumber] ADD MASKED WITH (FUNCTION = 'default()');

The query gets executed successfully but when i fetch data from the table it shows original data only no masking is applied.

Even applied mask function while creating table but still not able to mask column.

Please help me to figure this out.


texts: Cassandra nodetool repair scheduling and gc_grace_period

texts:
I am trying to devise a scheme with minimal repair frequency to not overload the system, and have a problem to understand how can I ensure that the data is not getting resurrected.

I have a cassandra scheme with gc_grace_seconds specification of 48 hrs (172800 seconds)

I know it is recommended that the repair job [must be run before gc_grace expires to ensure deleted data is not resurrected][1]

I run the nodetool repair with the –pr key, so that it take less time to complete (it takes about 1.5 hours per node with the –pr option and around 6-7 hours per node without the –pr option).

Now, I don't see a scenario where I can be sure the data is not becoming visible again after the tombstone is invalidated.

In my understanding, if we create a tombstone at a date 1/1 00:00, it will be gone on 3/1 00:00.

If the repair job is scheduled to start every 2 days at the same times, the repair will start at 3/1 00:00 and until it will take care of the disappeared tombstone, we have a period that the data is resurrected.

If the repair job is scheduled to run, say, every day at the same times, then still we have the same problem - there will be a delay between the tombstone invalidation and nodetool repair action which will start at 00:00 every day.

If we make gc_grace_period more then 48 hours, say, 60 hours, then in my understanding the "resurrection period" will get even longer - the tombstone will get expired at 3/1 12:00 and "wait" till 4/1 00:00, even if we run the repair every day.

So how will the nodetool take care of the tombstones so that there is no period that the data is going back to life?


texts: How to generates VALUES literal expression using a query?

texts:
During development and testing VALUES literal expressions are useful because they enable you to store data definition in your SQL query.
WITH foobar(foo, bar) AS (
  VALUES
  (1::integer,'a'::text),
  (2,'b'),
  (3,'c'),
  (4,'d')
)
SELECT * FROM foobar;

However this can become tedious when trying implement really wide or big table.
And it's even more frustrating when this could have been generated from an existing table.

So I there a way to output rows in format easily copy/paste-able as a literal VALUES expression?
The closest I could come by is to output row as record(please note the meta because this SQL actually try to reverse engineer literal VALUES back from a given literal VALUES).
WITH foobar(foo, bar) AS (
  VALUES
  (1::integer,'a'::text),
  (2,'b'),
  (3,'c'),
  (4,'d')
)

SELECT foobar::record FROM foobar;

Here is the psql output:
 foobar
--------
 (1,a)
 (2,b)
 (3,c)
 (4,d)
(4 rows)

However this need extra editing to rightfully quote,type and escape content so should i look for a formatting output trick or an SQL trick?

EDIT: The current top answer is already very good but ideally I would like to know if there is generic way to generate a value expression that can adapt to whatever record type you can throw at (even if it is a composite row with a random numbers of columns).
Let say this could be wrapped in as function like:
row_to_values(IN myrowtype record, OUT myrowtype_as_literal_values text)

At this stage one might wonder "Why not use row_to_json() ?" and yes this could be a valid alternative but we are drifting away from the initial use case I had in mind (quickly generate SQL for testing/occasional purpose) although using json should perform well and is now widely available among PostgreSQL releases.
Even better would be an aggregate function that directly output a clean text definition that specify types on first VALUE and add columns name as alias (but at this stage it's almost a feature proposal and here is not the right place and I'm nobody to ask for such a thing).
Unfortunately I guess this is currently an out of reach goal because as far as my understanding goes it would require a low level access to dynamic metadata about query record type. It's clearly doable for an existing table by querying pg_catalog.pg_attribute but I don't see how to achieve that with dynamic queries.
So if someone come with a nice trick I haven't though off, that would be impressive, otherwise the less generic answer clearly deserve an acceptance.


texts: How AlwaysOn Availability Group secondary replica catches up with primary after secondary server long downtime

texts:
Can someone point me to the MS article or blog post that explains in details how AlwaysOn Availability Group secondary replica catches up with primary after secondary server long downtime?

I did below tests with AAG (async, manual failover, read-only configuration).
A) Killed secondary instance during continuos insert into primary and started secondary instance few minutes after. AAG dashboard turned into a green almost immediately after secondary restart and started to catch up with primary until number of rows became the same in both instances. No transaction log backup was done.
B) Same as A) but few transaction logs were done from primary during the test.

Questions are:

1) What is the size of log cache/messaging framework etc that are used to keep tran log blocks (which are sent to secondary replica)

2) Can above structure (log cache/send queue etc - whatever is used as transport for AAG replication) sizes be configured/increased (similar to encrease of tran log backup retention period in log shipping, for example)?

3) As I backed up (truncated) tran log in test B) and secondary replica was syncronised with primary automatically what was used to find row difference between primary and secondary (apparently not tran log as it was truncated) and then bring then in sync?

4) How does this automatic catch up process work and what are its limitations?


texts: dbcc checktable will write in my dbcc page (x,9,3) for the field dbi_dbccLastKnownGood

texts:
If I run DBCC CHECKALLOC,DBCC CHECKCATALOG and DBCC CHECKTABLE in all objects it will update my internal page with the actual date in dbi_dbccLastKnownGood field?

The main reason of the question is for a db with 14 TB and my window is short so I need lot of windows.


texts: What is wrong with the following Python code -- trying to insert into a database?

texts:
This works in MySQL Workbench:

insert into nametest (name,surname) values('John''Krailling');

I am trying a get similar result with Pycharm with the following code:

import MySQLdb

db = MySQLdb.connect(host="localhost", # your host, usually localhost
                 user="root", # your username
                  passwd="iop", # your password
                  db="new_schema")# name of the data base
cur=db.cursor()

cur.execute("insert into nametest(name,surname) values('John', 'Green')")

The  entry is added in Pycharm, but not in the table in the underlying database.


texts: I have a requirement. The leadership wants an email list of all the users hitting the database and last access date. I have created the script that has the username and the time accessed. The issue is with SQL authentication. SQL authentication is only given to applications and the application accesses the database. The leadership wants me to design something that goes in and finds out the Active directory username for this SQL authentications hitting the database. I personally do not think this is achievable in SQL however, I would like to take the opinion of the community. Please advise.


texts:
I am setting up Alwayson,somehow my cluster is gone ( get-clusternode shows down,down,down and Joining). SQL Server is working fine. What Should I do? Create new cluster or build new server? Any suggestions, best practice or Whitepaper will help.

Thank you,
Coorsktm


texts:
I have three tables, one, two and three.

one

two

three

With this query

select one.id, count(two.id) as num_twos, count(three.id) as num_threes from one
left join two on one.id = two.one_id
left join three on one.id = three.one_id
group by one.id

I expect to get

1 2 1
2 1 4
3 0 0
4 0 0

because there are

two records in two that relates to one_id 1 and one record in three that relates to one_id 1.
one record in two that relates to one_id 2 and four records in three that relates to one_id 2.

but I get

Why? The counts seems to get multiplied. Can I get the desired behavior without subqueries?


texts:
We are trying to speed up the queries in our application and in the process identified all queries which took more than 100ms. We noticed that there were few insert statements which took more than 200ms. For example we have an insert to the following table which is consistently taking more than 200ms. Is there some way of increasing the performance of the insert? Also there is another table called message_Details which has a foreign key relation with this table.

CREATE TABLE `message` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `subject` varchar(255) DEFAULT NULL,
       `message` mediumtext,
       `message_type` int(3) NOT NULL DEFAULT '0',
       `posted_on` datetime DEFAULT NULL,
       `posted_by` bigint(20) NOT NULL DEFAULT '0',
       `posted_to` bigint(20) NOT NULL DEFAULT '0',
       PRIMARY KEY (`id`),
       KEY `posted_by` (`posted_by`),
       KEY `posted_to` (`posted_to`)
     ) ENGINE=InnoDB AUTO_INCREMENT=305845 DEFAULT CHARSET=latin1

Any advice is greatly appreciated as I am not a database guy.


texts: Occasional error shrinking transaction log files

texts:
I have a transaction backup job which runs every hour.

First it runs a backup of the database log to another drive, then it shrinks the log and deletes old .trn files.

The middle task shrinks the log fle, using this code:

USE myDB
DBCC SHRINKFILE('myDB_LOG', 0, TRUNCATEONLY)

However, sometimes I received the following error.

  Description: Executing the query "USE myDB  DBCC
  SHRINKFILE('myDB_LOG'..." failed with the following error: "Backup,
  file manipulation operations (such as ALTER DATABASE ADD FILE) and
  encryption changes on a database must be serialized. Reissue the
  statement after the current backup or file manipulation operation is
  completed.  Changed database context to 'myDB'.". Possible
  failure reasons: Problems with the query, "ResultSet" property not set
  correctly, parameters not set correctly, or connection not established
  correctly.  End Error  DTExec: The package execution returned
  DTSER_FAILURE (1).  Started:  22:00:19  Finished: 22:04:17  Elapsed:
  238.26 seconds.  The package execution failed.  The step failed.

Why is this happening?

Is there a better way to backup and shrink the database log?


texts: Table Design - Identical tables for different statuses

texts:
I am developing a transactional System and the below is a simplified scenario of my problem.

Users enter JOBS in a system.
A User first enters a JOBS with a DRAFT status (and the Job is given a draft number)
When the user submits the JOB, the status is changed to SUMBITTED (it is given an actual number)
Finally when the JOB is completed, the status is changed to POSTED.

I was thinking of creating 3 separate identifical 'JOB' tables for it. when a Job changes from draft to submitted, I move the data.
This is mainly for performance reasons. Most queries would be on the current JOBS and this would ensure that its table is not cluttered with all the posted.
Furthermore such a division of tables would provide structure improvement because I can use the JOB number as the primary key. If there was a single table, the draft number would need to be changed to an actual number. And primary keys should never change.

Now, my question is - does my approach make sense? is it customary to have separate tables? If anyone had experience with this and can provide feedback, that would be great.