texts:
i create a materialized view fast refresh. it takes almost 45 min to create but it did not refresh in 24 hours. i tried it on with index and without index. i check the log of all table max record in log table is 2 lac. query is as under please suggest what changes needed

CREATE MATERIALIZED VIEW LOG ON a WITH ROWID,   SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON P WITH ROWID,   SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON PG WITH ROWID,  SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON PN WITH ROWID, SEQUENCE (COLUMN USED FROM THIS TABLE))
/
CREATE MATERIALIZED VIEW LOG ON AP WITH ROWID
/
CREATE  MATERIALIZED VIEW C_INFO
NOLOGGING
BUILD IMMEDIATE
refresh fast with rowid
on demand
AS
SELECT
   A.ROWID ACTROWID , P.ROWID PREMROWID,
   PG.ROWID PGROWID,AP.ROWID APROWID, PN.ROWID PNROWID,
   ...
FROM     A,    P,   pg,    ap, pn
WHERE
        p.id             =  pg.id (+)
    and pg.columname  (+)=  'Value'
...


texts: How to group a view by the tables from which it gathers its data?

texts:
I have four different tables, let's say "LAMPS", "CARS", "DRONES" and "OVENS". Each of these tables has a column called "STATUS", which has a value of either 'ON' or 'OFF'. I want to create a view that show the data from all these four tables.

I then want to filter this view by the "STATUS" column. I am interested in only seeing the entries with the 'OFF' value.

Finally, I want to group the results by the table from which it came. I'm thinking of creating a new column in the view that would get the original_table_name for each of the rows and group by that value.

+------------+------------+-----------------+--------+
| TABLE_NAME |  SOMECOL1  |    SOMECOL2     | STATUS |
+------------+------------+-----------------+--------+
| LAMPS      | somevalue1 | someothervalue1 | OFF    |
| CARS       | somevalue2 | someothervalue2 | OFF    |
+------------+------------+-----------------+--------+

This is what I have so far:

CREATE VIEW my_view
  AS SELECT * FROM LAMPS l, CARS c, DRONES d, OVENS s WHERE l.STATUS = 'OFF' OR c.STATUS = 'OFF' OR d.STATUS = 'OFF' OR s.STATUS = 'OFF';

Without modifying any of the original tables, how can I group a multi-table view by the tables from which it gathers its results?


texts: How to divide data per client from a single database?

texts:
I am looking for best practices to separate data from a single database into multiple client specific databases. Currently my application dumps data for all the customers into single database and I need to separate data for each client(for security reasons I can't use single database) into respective client databases.

Can we use SSIS effectively to perform above task? Is there any way this SSIS package will understand that new customer is added (New ClientID) and create a new database for it?


texts: Using Composite Keys To Define "Dynamic" Relationship Types

texts:
The scenario
I'm working on a project that has pretty heavy relationship requirements. We have two competing ideas on how to approach the database design, and we're split on which one to use.
We have two types of entities, pet and adult. We are recording the common columns in a "master" table called Organism, and the columns only applicable to pets and adults are in their respective tables, Pet and Adult.
The issue is how to relate them, as well as defining relationship type. If it was just a matter of relating pet to adults, this would be simple: a "bridge" table between the two. The complexity comes in where we have to also relate pet to pet and adult to adult. We might need to say one adult is the spouse to another adult, or the pet sitter to another adult. We may need to say two pets live together, or don't live together but are blood related. We may need to later add other tables like Vet where we relate both Pet and Adult tables to as well.
Idea no. 1
So the first idea is that for each type of relationship we add in a "bridge" table, so one between Pet and Adult, and to relate Pet to Pet we would have a "bridge" table that relates Pet to Organism, and another "bridge" table to relate Adult to Organism.
If we later added Vet, we would need to add a "bridge" table between it and Adult as well as between Vet and Pet, and continue adding more "bridge" tables for each new relationship.
Idea no. 2
The other idea is to create a "dynamic" relationship table that has composite keys for both primary and foreign keys, so:
PrimaryID    PrimarySource  Relationship  ForeignID    ForeignSource
---------    -------------  ------------  ---------    -------------
1            Adult          Pet Sitter    4            Pet
2            Adult          Spouse        10           Adult
42           Pet            Blood Sibling 76           Pet

The "source" columns determine what tables the relationships are from, and can scale up so that any additional relationships are easy to make.
Considerations
The first option from what I'm told is more standard but the people against the second method can't seem to come up with any practical reason why this design would fail. I am interested in the practical application here, and what issues could arise in the two systems, if any. Is there a clear right answer here, and if so, what is it?


texts: trigger to delete records with same value from another table after inserting record in another table

texts:
i have two tables normal and abnormal,i want to create a trigger that will be able to delete records from normal table once i insert the same details in the abnormal table for example if a  person is sick and we insert his or her details in the abnormal table,the moment i submit the insert values in the abnormal table a trigger should delete the persons record in the normal table with a message deleted from normal table because this person is sick.

mysql> desc normal;
+----------+-------------+------+-----+---------+
| Field    | Type        | Null | Key | Default |
+----------+-------------+------+-----+---------|
| Id       | int(11)     | NO   | PRI | 0       |
| FirstName| varchar(20) | YES  |     | NULL    |
| lastName | varchar(20) | YES  |     | NULL    |
+----------+-------------+------+-----+---------+
3 rows in set (0.00 sec)

mysql> desc abnormal;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Id       | int(11)     | NO   | PRI | 0       |       |
| FirstName| varchar(20) | YES  |     | NULL    |       |
| lastName | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

lets say i do a select statement on normal table

mysql> select * from normal;
+----+----------+----------+
| Id | firstName | lastName|
+----+----------+----------+
|  1 | Micheal  | jacobs   |
+----+----------+----------+
|  2 | benjamin | clayto   |
+----+----------+----------+
|  3 | jackson  | boxer    |
+----+----------+----------+

then i insert values that exist in normal i insert then into abnormal table

insert into abnormal values('1','Micheal','jacobs', 'under treatment');
mysql> select * from abnormal;
+----------+---------+---------------+
|firstName | lastName| extra         |
+----------+---------+---------------+
| micheal  | jacobs  |under treatment|
+----------+---------+---------------+

the end result on normal table should not have the record for micheal it should be deleted

mysql> select * from normal;
+----+----------+----------+
| Id | firstName | lastName|
+----+----------+----------+
|  2 | benjamin | clayto   |
+----+----------+----------+
|  3 | jackson  | boxer    |
+----+----------+----------+


texts:
I thought, MyISAM is for simple tables and selects due to its architecture faster than InnoDB. Therefore I changed the engine for this table from InnoDB to MyISAM:

CREATE TABLE `table1` (
  `DateTime` datetime NOT NULL,
  `BidHigh` decimal(11,5) NOT NULL,
  `BidLow` decimal(11,5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='1 minute rates';

ALTER TABLE `table1` ADD PRIMARY KEY (`DateTime`);

These conditions apply:

I am testing it on a SLES 15.1 VM with 5 GB RAM and 8 CPU cores on a host which is mine and it doesn't have to serve other VM [just an information that there is no other VM which may influence the result].
A PHP script is running thousands of the below SELECT statements.
There are 24 of the above tables in the database which are accessed in the PHP script.
Each table has ~800k rows.
Between each test I restart the server to assure there are always the same conditions. Due to the fact the difference is so huge I am not running multiple test to get an average...

The execution time:

With InnoDB engine the script runs 199 seconds.
With MyISAM engine the script runs 1'026 seconds. More than 5 times longer.

I am running these SELECT statements:

SELECT `DateTime` FROM table1
     WHERE `DateTime` BETWEEN '2018-12-27 07:50:00' AND '2199-12-31 23:59:00'
        AND BidHigh > 0.96604
     ORDER BY `DateTime` LIMIT 1;

-- and --

SELECT MIN( BidLow ) FROM table1
    WHERE `DateTime` BETWEEN '2018-12-27 07:45:00' AND '2199-12-31 23:59:00';

OK, I figured out it is an index problem. Adding these two indexes

ALTER TABLE `table1` ADD UNIQUE `BidHigh` (`DateTime`, `BidHigh`);

-- and --

ALTER TABLE `table1` ADD UNIQUE `BidLow` (`DateTime`, `BidLow`);

fixes the performance issue and now the script needs 245 seconds, still slower than InnoDB - this is not really what I expected...

Adding these indexes to the InnoDB version doesn't improve the performance.

My questions:

Why does InnoDB not need these indexes and is still faster?
Is there a better solution?
And, what did I apparently totally misunderstood as the change to MyISAM caused such a horrible performance.


texts:
How to set constraint sql 2 colume, Data is not available elsewhere and must be used only.
Example is OK

NAME
REV
ID
PI

A
0
1
PA001

A
1
1
PA001

Example is Error

NAME
REV
ID
PI

A
0
1
PA001

A
1
1
PA002

When ID = 1 then PI is PA001 Only
We can't insert Any data to table except PA001 when ID = 1


texts:
I was wondering if I set a filter for just a particular user does it limit the amount of resource consumption used by sql server and does using a filter(depending on the filter) limit the performance hit?

Thanks in advance,

John


texts: import csv file into mysql with custom data change

texts:
I have a .csv file contains data like this:

Username   Password   Email
test123    123        test@foobar.com

I'm currently using MySQL, and I have a table named user with some columns like this:

UserID    Username    Password    Email

What i'm looking for is how can I import my csv data into user table and convert password column with MD5 method so what I have at the last is

UserID    Username    Password                          Email
1          test123     202cb962ac59075b964b07152d234b70  test@foobar.com

NB: I have a hundred record in my csv file.
Any help please. Thanks for advance.


texts: Procedure MySQL cursor taking a long time to execute

texts:
I have a MySQL procedure, with a cursor in it. The sample cursor query is given below

DECLARE cs CURSOR FOR
           (SELECT  a.mag_id FROM A a  WHERE
                a.creation_date BETWEEN (v_fromdate) AND (v_todate)
                AND a.type_id IN (SELECT type_id FROM key2 WHERE   sessionId=v_sessionId)
                AND a.mag_id IN (SELECT magid FROM    key1 WHERE   sessionId=v_sessionId order by magid)
                )
     UNION
                (SELECT  b.combo_mag_id FROM B b
                WHERE
                b.creation_date BETWEEN (v_fromdate) AND (v_todate)
                AND b.type_id IN (SELECT type_id FROM    key2 WHERE   sessionId=v_sessionId)
                AND b.combo_mag_id IN (SELECT magid FROM    key1 WHERE   sessionId=v_sessionId order by magid)
                );

DECLARE
CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET v_cur_time = now();
SET v_cur_query = 'cursor';
OPEN cs;
SELECT timestampdiff(SECOND,v_cur_time,now()) into v_diff;
SELECT CONCAT('Current Query: ', v_cur_query, ' Time taken: ', v_diff);

Both table A and B have millions of records.I have partitions created in both the tables with partition by range on creation_date.For 3 months of a date range the cursor takes almost 4 mins to execute.But when i tried running the same query in  MySQL workbench editor,by setting the parameters for the same date range it took only 22 seconds. Can somebody tell me why it executes faster in an SQL editor, and since i have to use it in the stored procedure, is there any way to optimize it ??


texts: Domain Certificate Authority Generated Certificate and SQL Server - Keyset does not exist

texts:
I do not have much experience with certificates or certificate terminology, so I may be incorrect in how I title things.
I am trying to use a domain issued certificate as the certificate used in the creation of a Column Master Key (CMK) to setup Always Encrypted (AE) columns.
When I try to add the certificate I receive the error "Keyset does not exist".
Here is the program location:

I did find information about the certificate needs to be, referenced here, and it says:
To be a valid column master key, a certificate must:

To be an X.509 certificate.
be stored in one of the two certificate store locations: local machine or current user. (To create a certificate in the local machine certificate store location, you must be an administrator on the target machine.)
contain a private key (the recommended length of the keys in the certificate is 2048 bits or greater).
be created for key exchange.

Now, I don't quite know how to verify that information, but I believe we have set the certificate up correctly.
I have gotten AE to work with a self-signed certificate in SQL Server, but we wanted to use the Domain Certificate Authority. Any suggestions?


texts: Upgrade Single Config Server to Replica Set

texts:
Recently we wanted to upgrade our Config Server to Replica Set. Since it is a development environment, we only have one Config Server running.

However, the official mongoDB documentation "Upgrade Config Servers to Replica Set" is written for three Config Servers.

How can we upgrade a configuration with only one Config Server?


texts: Select JSON elements from nested documnet

texts:
I have a JSON document such as:

{ "type" : "episode", "AssetId" : "urn:episode:ABCDEFG", "_id" : { "$oid" : "12345678" } }

Here is the table definition:

CREATE TABLE "v22"."assets_exp" (
    "_id" UUID NOT NULL,
.......
 "references.descendants" JSON,
.......

I would like to select type,  AssetId in addition to "$oid" as "_id".

I tried json_populate_record:

CREATE TYPE descendant_t AS (
    "type" text,
    "AssetId" text
);

select r.*
from  "v22"."assets_exp",
lateral json_populate_record(null::descendant_t, "references.descendants") r;

...but I get an error message, either that

  function json_populate_record doesn't exist

or

  ERROR: cannot call populate_composite on an array.

Any ideas?

The only function that works on this data is json_array_elements_text.

I am using PostgreSQL 11 and the data is imported from Mongo hence the naming of the columns.


texts:
I'm getting this error:

  SQLSTATE[HY000]: General error: 1025 Error on rename of './salt/ghost' to './salt/#sql2-4ab-630b' (errno: 152). Failing Query:
  "ALTER TABLE ghost DROP FOREIGN KEY ghost.row_id"

I looked for some information but I didn't get a solution..any help?

This is my DDL:

ghost,"CREATE TABLE `ghost` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `packet_number` int(11) DEFAULT NULL,
  `tbl_name` varchar(50) DEFAULT NULL,
  `row_id` int(11) DEFAULT NULL,
  `row_id2` int(11) DEFAULT NULL,
  `column_name` varchar(50) DEFAULT NULL,
  `old_value` mediumblob,
  `new_value` mediumblob,
  `ghost_action` varchar(255) DEFAULT NULL,
  `ghost_user_id` int(11) DEFAULT NULL,
  `ghost_action_comments` varchar(255) DEFAULT NULL,
  `ghost_status_id` int(11) DEFAULT '1',
  `ghost_status_comments` varchar(255) DEFAULT NULL,
  `serialized` mediumblob,
  `parent_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `updated_by` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_change_1_idx` (`ghost_user_id`),
  KEY `fk_change_2_idx` (`ghost_status_id`),
  KEY `row_id2_idx` (`row_id2`),
  CONSTRAINT `ghost_ghost_status_id_ghost_status_id` FOREIGN KEY (`ghost_status_id`) REFERENCES `ghost_status` (`id`),
  CONSTRAINT `ghost_ghost_user_id_sf_guard_user_id` FOREIGN KEY (`ghost_user_id`) REFERENCES `sf_guard_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=120450 DEFAULT CHARSET=utf8"

Javi


texts: Dynamic Data Masking Doesn't Seem To Work Correctly With ISNULL

texts:
Here is the code to reproduce the issue:
CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

-- Partial data masking works correctly here
Select
    EmployeeId,
    LastName,
    FirstName
From dbo.EmployeeDataMasking

-- Partial data masking does not work correctly here. Full masking is used.
Select
    EmployeeId,
    ISNULL(LastName,'') as LastName,
    ISNULL(FirstName,'') as FirstName
From dbo.EmployeeDataMasking

It appears, when using ISNULL, the partial masking is being converted to a full (default) mask. Is this the expected behavior?
Update: I discovered that COALESCE does not exhibit this problem. These queries return the expected results:
Select
    EmployeeId,
    COALESCE(LastName,'') as LastName,
    COALESCE(FirstName,'') as FirstName
From dbo.EmployeeDataMasking

Using Azure SQL Database


texts: Relationship normalization in SQL Server

texts: I want to know should I remove PersonTypeId from Persons table or is it a good idea to have it in there?


texts: Cannot access remote table after parallel/insert direct load txn

texts:
This is the Oracle error message I'm getting ORA - 12840.
We just upgraded from 10i to 12c version and some of the processes aren't working as they did before. I get this error when I execute a CREATE TABLE tablename AS SELECT ... and within the SELECT statement there's a call to a function. This function recovers information from another database so I'm guessing this is the root of the problem, but calling the function outside the create table works with no problem at all. I reckon there must be some parameter adjustment that is not set up properly for this kind of operation, but I haven't been able to find it.


texts:
I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. When the back up service was stopped we see no issues. Is this something common?

We are using SQL Server 2005 and the backups run on Sunday's and Wednesdays. I am also going to look into other things within the database itself, but this is what stood out.

-Edit-
I ran a perfmon on the machine for 3 days 5/27 to 5/31:

SQLServer:SQL Statistics\Batch Requests/sec
Average 355.412275
Median  306.610812
Min 108.9369962
Max 916.6332837
Std Deviation   141.7791552

SQLServer:General Statistics\User Connections
Average 83.14025501
Median  77
Min 52
Max 147
Std Deviation   19.27016231

SQLServer:Buffer Manager\Page life expectancy
Average 33.72386157
Median  21
Min 0
Max 246
Std Deviation   36.53737617

Looks like the maintenance backup that runs is not causing a problem while its running. It runs at 3am and on Sundays and Wednesdays and it completes successfully in about 2 hours.
I ran the SQL Server Profile:

I found one of the sp's that runs alot periodically takes a long time to execute.

Most of the time the Duration is 976 (microseconds) but sometimes its a big difference:
13922851
13025390
13021484
13019531
13018554
13017578
13016601

I am reading this correctly, right? 13 seconds?

If I run the same query from my machine in executes in a fraction of a second.

-Edit-

I have been looking at blocking and I haven't come up with much yet. I was just looking for recompile blocking in the SQL Server Profiler, but I did not see any events of that type (sp:recompile). I am still investigating blocking issues.

I also have noticed when I ran my last Profiler trace that there were a high number of sp_reset_connection events. Some of these sp_reset_connection 's were over 9 seconds long. Is this a normal type of behavior?


texts:
I have a role called readwrite, created by a user called postgres. This role has following grants applied:

GRANT CONNECT ON DATABASE confere TO readwrite;
GRANT USAGE, CREATE ON SCHEMA airflow_staging TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA airflow_staging TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA airflow_staging GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA airflow_staging TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA airflow_staging GRANT USAGE ON SEQUENCES TO readwrite;

After that I created a user called confere and assigned it to readwrite role:

create user confere with password '...'
grant readwrite to confere

When I run a create table statement with postgres user, confere select it without any problem:

--as postgres
create table airflow_staging.test (a text)

--as confere
select * from airflow_staging.test
a|
-|

But when I create a table with confere user, postgres cannot see its content because it gets a owner error:

--as confere
create table airflow_staging.test_2 (a text)

--as postgres
select * from airflow_staging.test_2
SQL Error [42501]: ERROR: permission denied for table teste_2

Why? Is there a way for postgres user to see tables created by confere?

Obs.: This is not a PostgreSQL superuser, I'm running Google Cloud SQL and Postgres 11


texts: What to choose for SQL Server storage - RAID 5 with 15K drives or RAID 10 with 10K drives

texts:
Im looking to configure a new sql server with a dell r730xd 24 bay chassis and need some help with choosing the drive configuration for sql server. Need help on what to choose for our RAID configuration on the data and log drives. What will we get the best overall performance out of the machine.

Currently we have 2 databases, and would like to separate them onto their own sets of drives (data and log) to a total of 4 drive letters. They are 600gb and 500gb on the data side. Currently they are both around 20gb log files. We would like to get to 1.8tb total space for the data drives and 600gb for the log files.

We have a choice of RAID 5 with 15K drives or RAID 10 with 10K drives. the 15K drives come in 300gb and 600gb. The 10K drives come in 300gb, 600gb, 1.2tb and 1.8tb. What would be optimal for performance?


texts: How can I capture error thrown by SET_LOCKTIMEOUT in ola Hallengren

texts:
A stats only job for Ola Hallengren was blocked for the 180 seconds as specified below.  The job threw an error and emailed me and the other DBA.  However, upon review of the CommandLog table and the output file, there was no record of what the error was.  I eventually figured out what had happened by looking at our monitoring tool, noticing the blocking for exactly the amount of time as the locketimout value and then looking at Ola's site to find that it throws a severity 16 error when the lock timeout is reached.
Is there a way to have the Ola scripts log the error so it's easier to figure out what went wrong?
EXECUTE [dbo].[IndexOptimize]
    @Databases = 'USER_DATABASES' ,
    @FragmentationLow = NULL ,
    @FragmentationMedium = NULL ,
    @FragmentationHigh = NULL ,
    @UpdateStatistics = 'ALL' ,
    @OnlyModifiedStatistics = N'Y' ,
    @LockTimeout = 180,
    @LogToTable = N'Y';


texts: Performance of a single table versus single table with jsonb versus multiple tables?

texts:
This one is difficult to summarize in the title.

I have a set of data such that a single table can contain all information in a single row per item except for one column. That column will contain a URL or many URLs. So I have three ideas:

Add another table to store all URLs with a foreign key back to the main table for each
Use a single table and match the URLs with a regular expression
Use a single table and store the URLs in a jsonb column as an array

For case #2, I would be storing the regular expression in the column and matching values against it, e.g.:

select *
from foo
where 'http://someting.example.com/foo' ~ url

The url column has a row with the value: http(s)?://.+\.example\.com/foo.

Let's assume the row set for foo would number in the 100s (1000 would be a very large set), but most likely in the 10s. Let's further assume that the URL count could be double or triple in number.

Would the performance of each of these three possible designs be inline with each other? In other words, would the choice of design be a matter of preference? Or would designs #2 and #3 be so much worse than #1 that it would never be a good idea to choose them over #1?

I need to match rows by the URL. But I'd also like to keep maintenance to a minimum.


texts:
Consider this simple query

UPDATE [SomeTable] SET Value = Value + 1 WHERE Id = 1

For almost any programming language this has a potential risk of race condition if it is executed by more than one thread at the same time.

The question is: Does it apply to Sql?

I believe UPDATE will grab a write lock to that row that prevents other write operations to it

So if two threads try to execute this query the order should be like

T1: write lock
T2: attempt to grab write lock (blocked)
T1: read value
T1: write value = value +1
T1: release write lock
T2: write lock
T2: read value
T2: write value = value + 1
T2: release write lock

Result: Value = Value + 2

Or could it look like this

T1: read value
T1: write lock
T2: read value
T2: attempt to grab write lock (blocked)
T1: write value = value +1
T1: release write lock
T2: write lock
T2: write value = value + 1
T2: release write lock

Result: Value = Value + 1

Mostly interested in Sql server behavior but would be nice to know if the behavior is consistent across all ANSI compliant DBMS


texts:
I am playing around with a MariaDB Galera Master Master Cluster and I have some issues which I do not understand.
[mysqld]
bind-address=0.0.0.0

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
default_storage_engine=InnoDB
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='192.168.2.67'
wsrep_node_name='KVM-1'
wsrep_sst_method=rsync
wsrep_sst_auth=repl_user:PASS

On the other nodes

[mysqld]
bind-address=0.0.0.0

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
default_storage_engine=InnoDB
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://IP_ADDR_MAIN_NODE,OTHER_NODES_ADDRS"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='192.168.2.68'
wsrep_node_name='KVM-2'
wsrep_sst_method=rsync
wsrep_sst_auth=repl_user:PASS

when i login to mysql and query for

SHOW STATUS LIKE 'wsrep_cluster_size';
Empty set (0.00 sec)

Am i missing anything?


texts: Ran a Index Rebuild offline but DB is still highly fragmented

texts:
Over the weekend I placed my databases into Single User Mode and ran the index rebuild script found at this site. It took the script about half an hour to run against my database and reported it completed successfully. This morning I checked the fragmentation of the indexes using this script here. Some of the indexes are (over 25 indexs in one table) have between 50 and 98 percent fragmentation.

Is there a better way to rebuild the indexs and get rid of this high fragmentation?


texts: Add Postgresql server to postgres-common

texts:
I've created postgresql cluster using initdb. Now I can't use it with common tools.
Can i somehow add this cluster or if no, how can i delete it to create new one with pg_createcluster. I know there's a DROP SERVER command but it requires a name and i don't know how to look it up.


texts: PostgresSQL -- Restrict listing access when using dbeaver or pgadmin

texts:
I have a schema in Postgres named -- public

When I login via dbeaver or pgadmin, I can go to database > schema > public > tables and it shows me the list of all tables present in public schema.

Is there anyway to create a user named bob and give access to public schema but only 5 tables in it?

So, when bob logs in to postgres using his password, he can go to database > schema > public > tables and ONLY view 5 tables. If he looks in information_schema or other postgres related tables and he finds there are other tables, it is fine. But in dbeaver or pgadmin, he should be able to view ONLY 5 tables.

If bob does select on any other table, it is fine too.


texts: changing from inner joins to left joins to include null values?

texts:
I need to have this query return all NULL Values for hours for each jobcode. I have tried to change the inner joins to left joins, but the output is the same (only showing the 1 jobcode for one date in the range). Do I need to change the cross join to an outer join as well?

SELECT EmployeeNumber,
   JobCode,
   [Date],
   SUM(Hours) as [Hours]
FROM
(
SELECT        tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]

FROM          AdjustUserRoleRequestStatusLookup
CROSS JOIN    tServiceJobCodes
INNER JOIN    tServiceHours
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode
INNER JOIN    tServiceReports
ON            tServiceHours.ReportNo = tServiceReports.ReportNo
INNER JOIN    tServiceReps
ON            tservicereports.employee = tservicereps.repid
INNER JOIN    tRegions
ON            tServiceReports.Region = tRegions.RegionCode

WHERE         Date between '2017-01-01' and '2017-01-05'

GROUP BY      tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
) ctHours
GROUP BY EmployeeNumber, JobCode, [Date];

Output for inner select query

EmployeeNumber  JobCode      Date               Hours
10203             82    2017-01-03 00:00:00.000 3.00
10203             82    2017-01-04 00:00:00.000 3.50
10203             82    2017-01-05 00:00:00.000 3.00
10203             86    2017-01-02 00:00:00.000 8.00
10203            210    2017-01-03 00:00:00.000 5.00
10203            210    2017-01-05 00:00:00.000 5.00
10203            215    2017-01-04 00:00:00.000 4.50

@DominiqueBoucher  i understand your confusion. this was built by non-dba admins. servicehours and servicereports both contain the ReportNo column. servicehours also contains the jobcode column.

The query works where there is data to report against, the issue is, I need to see Null where no data exist.

an example of this, would be if I pull from 2018/01/05, no records should exist, but I need to see:

 employee#     jobcode      date             Hours
 12345                1              01/05/2018        0
 12345                2              01/05/2018        0


texts: Querying a many to many relationship in access

texts:
I have just created a many to many relationship in MS Access with three tables as shown below

AE Career Fair - Struc contains a list of companies where the company name is distinct.

Office Locations - Int is a junction table containing in one column all the companies occuring in AE Career Fair - Struc, but duplicated based on the countries in which they occur.

Countries contains a list of all the countries in the world.

I was able to run a query for companies occuring in one particular country of interest. I would like to take this a step further and query this relationship and determine which companies are in more than one country. I cant really think of a way to do this based on how I currently have it set up. Could anyone provide indications as to how this would be done? Thanks.


texts: Trouble producing a query that groups a selection of groups

texts:
In terms of context, I believe that all that is needed is to know that I have a table, t_sale, that logs sales made. It logs a few things, such as day and location of sale in the shop, but what is important for this is that sale_time and sale_value (arbitrary categories for the sale price, LOW, MED, and HIGH) are logged.

The problem I am having is that I need to produce a report that breaks down sales in each hour, for each value category. I have successfully done both of these things individually with. For getting the number of sales by hour:

SELECT EXTRACT(hour FROM sale_time) AS Hour, COUNT(sale_time) AS Sales
FROM t_sale
GROUP BY EXTRACT(hour FROM sale_time)
ORDER BY Sales DESC;

which results in this (shop is open 0900 to 1700):

*HOUR*  *SALES*
13      99
16      99
9       94
10      91
11      89
15      87
12      82
14      79

And producing a report for sales by value, I used this query to get the data and columns I needed:

SELECT sale_value AS "Sale Value", COUNT(sale_value) AS "Total Sales"
FROM t_sale
GROUP BY sale_value
HAVING sale_value IN ('HIGH', 'MED', 'LOW');

which results in this:

*VALUE* *SALES*
HIGH    241
LOW     244
MED     235

What I am imaging is being requested is something that, in place of sales for the values example, further breaks it down by each hour. Something like:

*VALUE*    *HOUR*    *SALES*
HIGH       09        22
           10        14
           11        31
           etc...    etc...

MED        09        52
           10        16
           11        19
           etc...    etc...

LOW        09        63
           10        33
           11        28
           etc...    etc...

I've used dbForge Studio 2020 for Oracle for the report on the values due to no end of issues with Oracle Forms and Reports. I'm confident I can make the report if I knew what query to use to generate the data. However I am completely at a loss as to what functions to use.


texts: Is it a good idea to use null values to distinguish between records?

texts:
I have a table with two columns. State and Name. State can have two values, 0 and 1.
When the state is 1, there needs to be a value in the column Name. If the state is 0 the Name must be null.

My question is what is considered as a good database design. I've come up with two options.

Remove the column state and just distinguish the state whatever the Name value is null or not.
Have the column State distinguish the state. But then I need to somehow enforce not null values in the case state is 1.


texts:
Object Relational Mapping (take ActiveRecord and Rails, for example) allows a programmer to define relations between models, and callbacks on delete.

So my question is: do I still need to define foreign keys in my database? does a foreign key have other functions (speed for example?), or its only function is to preserve relational integrity?


texts:
Consider a scenario, where you have a MySQL master and slave server in replication. As we know, binary log enabled on the master and the slave reads from the binary log of the master. From MySQL documentation I understand that, only the fully committed transaction will be written to binary log, so there is no chance of missing any transaction in replication (if everything works well). So my assumption is that, once the transaction is completed and written to tablespace, it will be written to binary log.
My doubt is whether the write operation of the binary log is concurrent or sequential?
It means, for example, before writing to binary log and after written to tablespace, if the server shuts down or crashes, what happen to the transactions which happened in mean time?
Also, I am using mysqlbinlog utility and set with stop-never parameter. I need to know, when stop-never enabled the binary log will be in sync on the destination server (I tested it with flush logs - it creates new log on destination too).
QUESTIONS

How the copying of binary log works?
Is it first writes to master binary log and copied to destination log?
Is it writing on both the source and destination binary log simultaneously?
What will be impact of MySQL performance and disk IO performance?

If I misunderstood the concept, kindly correct me. Thanks in advance.


texts: DBCC CheckDb on Secondary Asynchronous Replica

texts:
Can I run Ola Hallengren's CheckDB job on a Secondary Asynchronous Replica  (DR) that is readable during business hours? Currently the Asynchronous Replica is not readable...

First question is if it is possible.
Secondly, is it safe?

I have a primary server that is Live and I am doing a CheckDB on a secondary server during business hours / off hours.


texts: Mariadb/MyISAM: Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an

texts:
We have a db with around 150 tables in, of which about 30 or so are partitioned as such :

ALTER TABLE `myTable`
  REORGANIZE PARTITION PFUTURE INTO (PARTITION P201910 VALUES LESS THAN ('2019-11-01'),
             PARTITION PFUTURE VALUES LESS THAN MAXVALUE);

We've been automatically partitioning these tables on a monthly basis for around 3 years now without any issue.  Recently on 1 of the tables, after a partitioning exercise (with no errors/warnings reported) we see this :

  Got error 190 "Incompatible key or row definition between the MariaDB
  .frm file and the information in the storage engine. You have to dump
  an

from storage engine MyISAM" whilst running a show table status.  I have restored from a backup (after stopping/starting mariadb) and re-issued the command, and the same error is reported.

Repair table fails with the same message.

Is there a process I can follow to un-corrupt the table and make the partitioning process complete?

We're using MariaDB 10.4.8.  We are not in a position to use any other table format, MyISAM is what we have and what we have to stick with for now.

What I should mention is that we recently upgraded to MariaDB 10.4.8, and although I'd instantly jump on that as being the cause, the other 29 tables partitioned perfectly.


texts: How can I fix the Oracle syntax of this update by the result of a select in Oracle 10g?

texts:
I have a problem with an update statement, that I developed under 11g, when running under 10g.

I reduced the problem to:

Create table test (
   col1 number(10)
);

insert into test values (1);

update test set col1 = (select 1  from DUAL ) + 1;

select * from test;

When executing on Oracle 10, I get the following error:

update test set col1 = (select 1  from DUAL ) + 1
Error at line 1
ORA-00933: SQL-Befehl wurde nicht korrekt beendet

Any ideas, how I can modify the statement to run under 10g?
I want to use a single statement.


texts: How do I make a citation table where work can't refer to itself?

texts:
CREATE TABLE Work(id INT NOT NULL AUTO_INCREMENT,
    title CHAR(20),
    date DATETIME,
    description VARCHAR(1000),
    CONSTRAINT Work_PK PRIMARY KEY (id),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- In the following, do we have to worry about work_1 being = to work_2, how can we prevent this ?

CREATE TABLE Work_Citation(id INT NOT NULL AUTO_INCREMENT,
                       work_1 INT NOT NULL,
                       work_2 INT NOT NULL,
                       CONSTRAINT Work_Citation_PK_1 PRIMARY KEY (id),
                       CONSTRAINT Work_Citation_PK_2 UNIQUE (work_1,work_2),
                       CONSTRAINT Work_Citation_FK_1 FOREIGN KEY (work_1) REFERENCES Work(id),
                       CONSTRAINT Work_Citation_FK_2 FOREIGN KEY (work_2) REFERENCES Work(id)) ENGINE=InnoDB DEFAULT  CHARSET=utf8;

-- A second query, can I do without the id column in Work_Citation? Any comments on when we should / should not use it?


texts: Additional info on some rows in the table

texts:
I am trying to design an MS Access 2013 database to keep track of an inventory of items.

All items have certain common properties, like name, date they were stored, location of the physical item, etc.

The items fit into broad categories. The category is reflected in the item name.

Most items have a standardized name, made up of three letters and a positive integer. The letters indicate the category, the number is a serial number within that category. For example, PEN6' is item #6 in the "pens" category,LTR6` is item 6 in the "letters" category.

There is a small number of items which have very non-standard names like `Bob's letter to his friend'. Their category cannot be easily determined by the name, but they still belong to some definite category (recorded elsewhere).

There are some kinds of data that are only relevant for some categories. For example, "From" and "To" need to be stored for all LTR, but not for PENs. "Color" needs to be stored for pens, but is irrelevant for letters.

How can I store this additional info? I came up with two possibilities, but both have issues I couldn't resolve.

Solution #1: I make separate table for each category: An LTR table, a PEN table, etc. Each table has whatever fields are desired for that table. But when adding a new item, how do I check that the name is unique within the whole database? Because of how I chose my name format, most of the time this is already guaranteed, but for every non-standard name I would have to do a database-wide query to make sure it doesn't already exist.

Solution #2: I make a single table for all items. For each category, I project this database into a new table showing only the category I want (how?). The new table then has the particular fields of that category. When rows of this projected table are edited, the change is propagated back to the original table (if relevant, e.g. for date). When a row is added to the projected table, the corresponding row is also added to the original table (How?).


texts: Update column with different value for multiple rows using a csv file

texts:
I often find myself using Excel to create update queries.

When I need to update multiple rows with different values for a column, I simply use Excel to create the update queries (multiple).

Here's an example SQL Fiddle.

If I have a list like:

Productname,Text
OpenIDM,testing
OpenAM,Tested
OpenDJ,great

I want to update the table with these values, so I need to create queries like this:

update ForgeRock set text = 'testing' where productname = 'OpenIDM';
update ForgeRock set text = 'Tested' where productname = 'OpenAM';
update ForgeRock set text = 'great' where productname = 'OpenDJ';

The format I receive the "To Be Updated" string in could be anything: Excel, csv, text.

The number of rows involved varies, depending on the project. Sometimes it could be just a few thousand rows, but other times it could be tens of thousands. I never timed myself or the time it takes to execute, but the execution time isn't really the problem. I just want an easier way to achieve this.

By "easier", I mean I should be able to update the table as mentioned without the need of Excel or a temporary table.

For example: to load data from a file it is possible to use load data infile.


texts: Home sensors database - optimising model

texts:
I am trying to develop a home automation/monitoring system.  This involves a bunch of sensors distributed around the house/garden/garage. I'd like to store the values for the sensor readings.  I am trying to make the database design flexible and general so that I don't have to change it around once it is set up.  However, I seem to have ended up with two possible options and I can't quite see which is the most optimal.  The tables that are common to both are:
tbl_region = id, region_name - (e.g 1, first)
tbl_room = id, region_id, room_name - (e.g 1, 1, lounge)
tbl_sensor = id, sensor_name - e.g (1, dht11)
tbl_measurement = id, measurement_name - e.g (1, temperature)

Since each sensor can have many measurement types (e.g temp, humidty, pressure) and a measurement can be done by many sensors (e.g dht11, bme280) there needs to be a junction table to define the many to many relationship
tbl_sensor_measurement = id, sensortype_id, measurementtype_id - (e.g 1, 1, 1) for a dht11 sensor's temperature measurement (it does other things too)

I can potentially have multiples of the same sensor in the same room so there is a many to many relationship between rooms and sensors. Here is where my two ideas diverge:

I could have a junction table between tbl_room and tbl_sensor_measurement:

tbl_room_sensor_measurement = id, room_id, sensor_measurement_id

this would give me mutliple rows for a sensor in a room (one for each type of measurement) and allow the same type of sensor to be housed in that room.  This would mean that my final data table would only have four columns
tbl_value = id, room_sensor_measurement_id, value, timestamp

I could have a junction table between tbl_room and tbl_sensor

tbl_room_sensor = id, room_id, sensor_id

this would give me one row for a sensor in a room and allow the same type of sensor to be housed in that room.  This would mean that my final data table would now have five columns
tbl_value = id, room_sensor_id, measurement_id, value, timestamp

I think that the model 2 is going to be easier to retrieve data later on (I can pick a sensor in a room and get all of the measurements easily.  I am new to rdb design and i'm not sure which to go for (or if there is an entirely different way altogether) and would appreciate any pointers.
Thank you for your help,
Martyn


texts: How to read files using the OPENDATASOURCE method?

texts:
I am trying to understand what a particular piece of source code does. The original author has written the following excerpt:
/*******************************************/
-- Inserează în tabela temporară.
/*******************************************/
INSERT INTO #tmp
(
    [tip]
  , [ev]
  , [ap]
  , [cant]
  , [preț]
  , [data]
)
SELECT *
FROM
OPENDATASOURCE
(
    'Microsoft.Jet.OLEDB.4.0'
  , 'Data Source=C:\ETU; Extended Properties="TEXT; HDR=yes; FMT=DELIMITED"'
)...[EV#csv]

When I am executing this piece of code in SSMS, I get the following error:

Msg 7314, Level 16, State 1, Line 4
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "EV#csv". The table either does not exist or the current user does not have permissions on that table.

MSDN shows certain syntaxes for linked servers, but I do not think that the original author meant that.
There is a certain file on the hard disk which full path is C:\ETU\EV20150126000001.csv.
I have attempted to rename the file to the EV#csv new name, yet to no avail.
SELECT @@VERSION says:

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)   Sep 16 2010 20:09:22   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition with Advanced Services on Windows NT 5.1  (Build 2600: Service Pack 3)


texts: Updating and/or inserting several rows in the same SQL statement

texts:
I am implementing an account "ability" type thing - a users account is given the abilty to do one or many things in the system, like (simple examples) cancel orders or pay invoices.  I have a table like this:

ac_id     ability        active
  1        CANCELORDER      Y
  1        PAYINVOICE       Y
  1        EDITOREDR        Y
  2        PAYINVOICE       Y
  2        PAYWAGES         Y
  2        CANCELORDER      Y

ac_id is a link to the account table.

When a user account is first created with some abilities it is easy to insert the abilities into the table with an insert statement.

When a user account's ability list is changed updating seems to be tricky.  Here's what I think needs to be done:

list      DB
ABC       ABC
GHI       DEF
JKL       JKL
MNO       MNO

If list contains the new list of abilities for an account and DB contains the current list for that account we need to

remove DEF from the DB list because it is not in the new list.
add GHI into the DB list.

The first is straightforward SQL like:

update userabilitytest set
  active = 'N'
where
  account_id = 1 and
  active = 'Y' and
  ability not in (''ABC', 'GHI', 'JKL', 'MNO' )

(which sets the active flag to 'N' rather than actually deleting).

However, I can't think of a way of doing the 2nd part. I have it working by figuring out whether the ability is already in the DB for that user in my application code, which does insert for each not-yet-in-the-db ability.

It would be nice to push this into SQL and run on the server.

Any ideas?


texts: RDS is quickly running out of storage, how can I debug this?

texts: I am using RDS and  looking at my POSTGRES size via \l+ and I see that it is using 154 GB hard disk space.  However, when I look at my StorageSpace statistic in RDS, it says that I’m using: 210GB.  I am trying to figure out where the 56GB that are unaccounted for, are coming from?  I found that 16GB of this is Log Files.  Can it be pglogical and my WAL taking up a lot of space (I recently learned that the sync is not replicating at the moment). Any other ideas of what it can be?


texts: Why am I getting ERROR: duplicate key value violates unique constraint "movies_pkey'

texts:
So, I'm creating a database for one of my classes using pg admin. I got all the data in no problem but my one issue is this one error that I don't really understand.

ERROR: duplicate key value violates unique contraint "movies_pkey"
DETAIL: key(movie_num)=20 already exists

The above is the error I am getting.

-- DROP'ping tables clear out any existing data
DROP TABLE IF EXISTS movies;
DROP TABLE IF EXISTS actors;

-- CREATE the table, note that id has to be unique, and you must have a name
CREATE TABLE actors(
    id INTEGER PRIMARY KEY,
    name VARCHAR(20) NOT  NULL
);

INSERT INTO actors(id, name) VALUES(
    1,
    'Sean Connery');
INSERT INTO actors(id, name) VALUES(
    2,
    'George Lazenby');
INSERT INTO actors(id, name) VALUES(
    3,
    'Roger Moore');
INSERT INTO actors(id, name) VALUES(
    4,
    'Timothy Dalton');
INSERT INTO actors(id, name) VALUES(
    5,
    'Pierce Brosnan');

INSERT INTO actors(id, name) VALUES(
    6,
    'Daniel Craig');

-- CREATE the table, note movie_num is unique and title and year are required fields, though the actor can be optionally empty
CREATE TABLE movies(
    movie_num INTEGER PRIMARY KEY,
    title VARCHAR(35) NOT NULL,
    actor INTEGER,
    year INTEGER NOT NULL
);

INSERT INTO movies(movie_num, title, actor, year)
VALUES(
    2,
    'From Russia With Love',
    1,
    1963);
INSERT INTO movies(movie_num, title, actor, year)
VALUES(
    3,
    'Goldfinger',
    1,
    1964);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    4,
    'Thunderball',
    1,
    1965);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    5,
    'You Only Live Twice',
    1,
    1967);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    6,
    'On Her Majesty''s Secret Service',
    2,
    1969);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    7,
    'Diamonds Are Forever',
    1,
    1971);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    8,
    'Live and Let Die',
    3,
    1973);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    9,
    'The Man With The Golden Gun',
    3,
    1974);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    10,
    'The Spy Who Loved Me',
    3,
    1977);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    11,
    'Moonraker',
    3,
    1979);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    12,
    'For Your Eyes Only',
    3,
    1981);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    14,
    'A View To A Kill',
    3,
    1985);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    15,
    'The Living Daylights',
    4,
    1987);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    16,
    'License To Kill',
    4,
    1989);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    18,
    'Tomorrow Never Dies',
    5,
    1997);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    20,
    'Die Another Day',
    5,
    2003);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    1,
    'Dr. No',
    1,
    1962);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    13,
    'Octopussy',
    3,
    1983);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    17,
    'Goldeneye',
    5,
    1995);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    19,
    'The World Is Not Enough',
    5,
    1999);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    20,
    'Casino Royale',
    6,
    2006);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    21,
    'Quantum of Solace',
    6,
    2008);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    22,
    'Skyfall',
    6,
    2012);
INSERT INTO movies(movie_num, title, actor, year) VALUES(
    23,
    'Spectre',
    6,
    2015);

And this is the database I created.

Any help is much appreciated.


texts: Selecting Multiple Values from Same Coulmn

texts:
I want a query to find out who doesn't have a specific role (user, create or admin)

DB is Structured as Follows:

name_last | name_first | role

Sample Data:

brown | john | user |
brown | john | create |
brown | john | admin |
house | chris | user |
brady | bill | user |
pink | deb | user |
pink | deb | admin |
green | ashley | user |
green | ashley | create

Who doesn't have the create role?

select name_last, name_first, role
where role != ??


texts: SSDT: Requires Visual Studio? Requires connection to SQL Server? (SQL Server 2016)

texts:
I need to make some SSIS workflow packages. I see I can do this in SSDT.

I have not used SSDT before.

It appears that SSDT installs into Visual Studio. OK, I can do that. (I have VS 2015 installed.)

But my workstation has no visibility to the SQL Server. The SQL Server is firewalled, etc.

Will I be able to author in a disconnected way in SSDT and then package a file that I can transfer to SQL Server?

If not, what is the workflow to author in the SSDT SSIS Designer and get the package to an isolated SQL Server?


texts: Uniq join 3 tables with relationship table not uniq

texts:
I am using Postgres.

I have 3 tables:

table_1
  id

table_1_2
  table_1_id, table_2_id

table_2
  id

The problem is the relationship table table_1_2 is not uniq.

I.e., table_1_2 will have the same tuples multiple times:

{1,1}, {1,1}, {1,1}, {1,1}, {1,1}, {1,2}, {1,2}, {1,2} etc.

Is there a way to join the table_1 and table_2 using table_1_2, but with only uniq result?

Currently, I will get all the repeated items as from the relationship table.


texts: Merge Replication between two Embedded Databases?

texts:
I have an application that uses SQL Server CE 3.5.  I am trying to expand the database to have a remote database accessible from various locations.  I will need both the local (which might or might not have consistent internet connections) and the remote databases to be able to sync with one another and to be able to send and receive updates from the other database upon internet connection.

Is it possible to replicate an embedded database such as SQL Server CE with another embedded database or do you have to set up a server for this functionality?  If not, what would be a recommended server database to use (preferably to be set up on a cloud server)?

Thanks,
Stuart


texts: 1236 On Slave After Master Master Failure

texts:
I have a master (master 1) that replicates to another master (master 2), which then replicates to its slave.

So master 2 had an issue with the binary logs:

  Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Which I've run into before and was able to resolve by running the same commands I ran from this thread, expire_logs_days directive requires change master? (I'll ask a question later about why this is still occuring, not my current issue though).

If interested here's the relevant bit from that thread for how I got it working:

stop slave;
reset slave;
change master to master_log_file='...' , master_log_pos=...
start slave;

so now master 2 is good and replicating from master 1. This time though the slave of master 2 broke as well.

The error I'm getting is:

  Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysqld-bin.000397' at 244145356, the last event read from './mysqld-bin.000397' at 4, the last byte read from './mysqld-bin.000397' at 4.'

When I run SHOW BINARY LOGS; on the slave I get:

  ERROR 1381 (HY000): You are not using binary logging

What happened to my slave? Why's it not working with the logs anymore? The master still has the log file so I figured running the stop, reset, change, start would resolve the issue (because it would re-request the logs) but it didn't.


texts: Can somebody check this normalisation works - I think I am missing a step or a dependency somewhere

texts:
Given these two tables:

and:

I need to first list them as an un-normalised structure(s), then I need to identify the functional dependencies/primary keys.

Once I have done this I must represent it in 3NF. I think I am skipping 1NF and going straight to 2NF, but I can't work out the intermediate step.

Un-normalised structures with PKs

Piste (PisteName {PK}, Grade, Length, Fall, Lifts (repeating group), Open)
Lift (LiftName {PK}, LiftType, Rise, Length, Operating)

Functional dependencies

PisteName →{Grade, Length, Fall, Open}
LiftName →{LiftType, Rise, Length, Operating}

Is there any more? I can't seem to see any.

Normalisation:

1NF

Piste (PisteName, Grade, Length, Fall,  Open)
Lift (LiftName, LiftType, Rise, Length, Operating)
LiftLocation (PiestName*, LiftName*)

Now from what I can tell this is 1NF, 2NF and 3NF so I have obviously missed something because the teacher is expecting us to show the steps for each NF.

Can anybody point me in the right direction/and or tell me where I've messed up?


texts:
I want to create a database with Size limitation. I am using sql server 2008r2 edition.
Maximum size should be only 20GB. That means it should allow only 20GB space for particular database.
How can I do this?


texts: Trying to count multiple values and total

texts:
SELECT
  COUNT (DISTINCT competition.genre_id) genrecount,
  COUNT(DISCTINCT band.band_id),
  genre.name,
  genre.genre_id
FROM genre, competition_band, competition, band
WHERE
  genre.genre_id = competition.genre_id
  AND band.band_id = competition_band.band_id
  AND competition.competition_id = competition_band.competition_id
GROUP BY genre.genre_id, band.band_id, genre.name, competition.competition_id
ORDER BY name;

Basically I am trying to show how many competitions of each genre are being held and how many bands have entered competitions of each genre.

Really stumped, the results of this just show genrecount and band_id as a series of 1's and multiple genres.

can anyone help?

Band (band_id, band_name uni_id, band_leader)
COMPETITION (COMPETITION_ID, COMPETITION_TITLE, COMPETITION_DATE, START_TIME, SPONSOR_ID, GENRE_ID)
COMPETITION_BAND (BAND_ID, COMPETITION_ID, SET_TIME)
GENRE(GENRE_ID, NAME)

Do we need a sub query?


texts: Transactional Replication: When to delete the initial snapshot

texts:
I would like to delete the initial snapshot after I setup transnational replication and everything is working fine.

When is it OK to the delete the snapshot that initializes a subscriber?


texts: Could not resize shared memory segment error

texts:
I have a PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) running in a docker container.
I get the following error with some queries:

could not resize shared memory segment "/PostgreSQL.860708388" to 536870912 bytes: No space left on device`

system:

Ubuntu 18.04.5
memory: 126 GB
cores: 64

disk usage is fine, as well as quotas.
Postgres settings:

work_mem: 30GB,
dynamic_shared_memory_type: posix
max_parallel_workers_per_gather: 32


texts:
new to SQL, I'm looking for a way to setup a Trigger that will update Table B when a new row is inserted on Table A. For basic understanding here are some of the columns in my tables:

TABLE A:
+--------------+--------------+
|order_id (PK) |order_details |
+--------------+--------------+

Table B:
+-------------+--------------+-------------+
|item_id (PK) |item_details  |order_id (FK)|
+-------------+--------------+-------------+

I'm using a stored procedure like this one to insert data into Table A. Obviously this procedure doesn't work but I wanted to show where my problem starts and maybe there is a better way besides a Trigger. The problem has been getting the order_id to use within the second INSERT.

/*Parameters for the first Insert Statement*/
@customer_id INT,
@track_num NVARCHAR(50),
@pckg_num NUMERIC,

/*Parameters for the second Insert Statement*/
@manuf_name NVARCHAR(50),
@model_name NVARCHAR(50),
@qty NUMERIC,
@notes NVARCHAR(MAX)

/*Parameter I need for the second statement that doesn't exist until the first statement is committed*/
@order_id INT

AS

BEGIN
/*First Insert*/
INSERT INTO Orders
    (customer_id, track_num, pckg_num)
VALUES
    (@customer_id, @track_num, @pckg_num)
/*Second Insert*/
INSERT INTO Items
    (manuf, model, qty, notes, order_id)
VALUES
    (@manuf_name, @model_name, @qty, @notes, @order_id)

END

I'd like to fire the trigger within the same procedure that would then update Table B with item details passed to the procedure from my web app and including the PK (order_id) from the newly created row in Table A as FK in Table B.

I've tried doing this other ways with no luck, came across Triggers and thought it seemed like a good choice. If you have other suggestions about different methods, please feel free! :) Thanks in advance!


texts: How do you calculate mysql max_connections variable?

texts:
How do you calculate mysql max_connections ?

What do you take into consideration ?


texts: How do I restore my Heroku pg_dump on DigitalOcean?

texts:
Simply-put, I'm trying to copy my database from a Heroku server over to my new DigitalOcean server.

This is what I've done so far:

Download (custom format) backup via Heroku
Upload the dump file to DigitalOcean
pg_restore -d postgres -1 /etc/postgresql/9.5/main/a190c52e-15f6-4fab-8a14-323275c6e174

... And then I get errors...

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 213; 1259 17190747 TABLE account_account rknsgnrsrzgwiv
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "account_account" already exists
    Command was: CREATE TABLE "public"."account_account" (
    "id" integer NOT NULL,
    "timezone" character varying(100) NOT NULL,
    "la...


texts:
The MSDN documentation tough to understand.

Are there any online tutorials available to learn SQL Profiler? If possible, videos or simple blog posts would be preferred.


texts: change character_set_connection utf8mb4 mysql

texts:
I want to change character_set_connection utf8mb4. Its showing me utf8. I have follow this https://mathiasbynens.be/notes/mysql-utf8mb4 article and changed
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Also fire this query. SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci but when I check current status in phpmyadmin using SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'; result

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

I have set alter tables and database

SET NAMES utf8mb4;
ALTER DATABASE openfire CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE ofOffline CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This is my connections string jdbc:mysql://localhost:3306/openfire?rewriteBatchedStatements=true&useUnicode=true
MySQL  5.6.33 I have check in windows with version 5.7.14 it's fine. Please give me any suggestions. Thanks in advance.


texts: Do schema changes "break" Availability Groups or are they handled transparently?

texts:
My organization is planning to adopt SQL Server 2012 Availability Groups and I am trying to understand what impact (if any) it will have on our application upgrade process.

We release application updates on an 8 week cycle and any release could include schema changes and/or data migrations.

What I am trying to understand is whether or not the HA/DR solution handles the schema changes transparently (new columns, indexes get added to secondaries) or is manual intervention required to create the schema on each instance and then turn Always On back on.

The data migration piece I'm assuming is handled transparently but would like to confirm that as well.

I guess I am also making a blanket assumption that there is no difference in these behaviors based on the Availability Groups configuration which may be false as well.  Please let me know.

In a nutshell; In any given release of my application I may change a very large table (10s to 100s of millions of records) by adding columns to it.  Some columns may be "net new" so they can make use of the Enterprise Online schema change functionality.  Other columns may be a refactoring of an existing column (FullName gets split into FirstName and LastName) and a migration will be run for each row in the table to populate these fields. Do any of these behaviors require DBAs to change the AlwaysOn configuration or is this handled by default and all secondaries get the DDL and DML statements "for free"?

Thanks for any clarity you can provide.


texts: How do I update my database generated pg_basebackup through WAL files?

texts:
Following on from this post...

I have a backup generated by pg_basebackup regularly, I would update it through the WAL files.

What is the best way to do it?


texts: Truncating MySQL table and insert newly records on daily basis

texts:
I have my database in which I have to store new data on daily basis fetching from API. The problem is that I have to truncate table everyday and then insert newly fetched records in same table. Records count would be around 10k. It takes 5-10 seconds to insert new records.

So my concern is that if I truncate the table and some users are requesting at a time so he will end up with error because I truncated the table. So can you suggest what should I do to tackle this problem? I am not a DBA so please suggest what should be the better way to do it.


texts:
I'm using Locustio service as load testing tool for my app. Without details, I'm using the same aggregation query with simulated 100 users, in this case RPS (requests per second) is about 20. In first 30 seconds every query time is about 10-50ms, but then (when about 65 users active) it increases to 500-1000ms. I'm using MMAPv1, so query is cached for sure, so the question is: why query execution time is increasing?

Of course, it's only test case, in real I'm doing different queries, but in that case I wasn't sure that all the queries was cached. That's why I decided to test It only with one query. Sorry for my bad explanation.

I think the problem is in connection number, still it's only 100. How cna I optimize the performance of my app?

UPDATE1

db.serverStatus about connections number:

    "connections" : {
        "current" : 20,
        "available" : 51180,
        "totalCreated" : NumberLong(32)
    },

All of my engine settings are defaults
This is my db.collection_name.findOne() part, which involved in aggregate query:

{
    "_id" : ObjectId("571764be1e31c8556e80b589"),
    "ppl_ids" : [
        19,
        1,
        20,
        21,
        22,
    ],
}

The pipeline of aggregate query itself:

{$match: {ppl_ids: {$in: [16,1245,592] } } }

The part of db.collection_name.getIndexes():

    {
        "v" : 1,
        "key" : {
            "ppl_ids" : 1
        },
        "name" : "ppl_ids_1",
        "ns" : "dbname.collection_name",
        "background" : false
    },


texts: How to do point in time recovery of binary log files into .sql file

texts:
I have written a shell script to do an incremental backup of MySQL database.
For point in time recovery I'm using the binary logs generated in /var/lib/mysql directory.
For point-in-time recovery, Im creating an sql file using the following statement.

mysqlbinlog --start-datetime="$STARTTIME" --stop-datetime="$ENDTIME" $file > $NOW.test.sql

The incremental backup happens such that the $STARTTIME is going to be the end time of the Fullbackup and $file is the list of log files generated since the fullbackup endtime to the time of start of incremental backup.
My question is how can I create a .sql file in /var/lib/mysql folder and move that file to the destination .
When I try the above statement, it says permission denied. How can i achieve this?
I know the permissions for the file has to be changed. Is there any alternative for this.


texts:
We have a request to build an ASP.Net application that will be used as an interface to launch commands on Oracle databases.

We need to find a way to compare the content of 2 databases. We prefer not to code this process. Instead we would like to rely on Oracle to do it (we would like to call Oracle commands).

What is the best approach for this?

We are using Oracle 10.x

Any help will be appreciated


texts: Which Edition Of SQL Server Is On The Disc?

texts:
I have several SQL Server 2008 R2 setup discs, but they're not labelled with which edition (Standard, Developer, Enterprise etc.) they are for.

Running them doesn't give much help as it just shows that it's a SQL Server 2008 R2 install, nothing more specific (unless I'm looking in the wrong place).

I hope that one of you smart cookies will be able to point me in the right direction to find out the edition of SQL Server on the discs.


texts: How I can read errors that occurred in the BBDD as processes incomplete in MS-SQL?

texts:
How I can to recognize the processes that failed on the SQL Server/bd, processes that any user run/exec.

For example the last rollback occurred in the bd after some process failed.

Would reading error  log help ? i.e  I use exec sys.xp_readerrorlog but it returns only login errors.


texts: Is it possible to export postgres database without views?

texts: I check pg_dump doc, and there seems to be an option that allows to export only views. But is there a way to dump a database without all the views?


texts: What is the point of a SRID in a geometry type?

texts:
From my understanding, SRID is a projection code; how the 2d coordinate system is fit over a spheroid.

The difference between Geometry and Geography types, from my understanding, is that a geometry type is two dimensional, and a geography type is 3 or four dimensional.Therefore what's the point of an SRID in a geometry type?


texts: How to make sure change_tracking statistics stays updated

texts:
I am using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)   Jun 15 2019 23:15:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )
I have multiple databases with CHANGE_TRACKING enabled on high insert/update tables. My retention period is 2 DAYS, with auto cleanup enabled. Database compatibility is set to 130 (SQL 2016)

Some of the tables tracked are huge and can have thousands of insert/update every day. This is the content of the change_tracking tables.

Here is an example of query we do to get the changes.

SELECT Columns
FROM CHANGETABLE(CHANGES MyTable, @TrackingKey) AS CT
INNER JOIN MyTable b ON b.Key=CT.Key
WHERE b.Status = 1

I have seen from time to time that the queries to get the latest changes on some of the tables take an awful long time to complete and generates a lot of CPU. To help with this, I've setup a daily update statistics on change tracking tables that run every night. And it helps a lot, but sometimes, during high user activity days, I have to run this update statistics even during the day. When I run the update statistics on those table, the situation is back to normal and the queries to get the latest changes run fine for some time.
We use the change tracking for some vital parts of our applications, so it has to work.
Is there any option, trace flag that I can enable to help with change tracking statistics? Anyone have experience with change tracking on high activity databases can give me some advice?

So I finally decided to do a job to check if change tracking table has changed more than 20k rows since last statistic update, then the job will update the statistics on the change tracking table.
I'll put the query here if it can help someone else. This query gives you all the statistics for change tracking tables where the table changed more than 20k times since last update statistics. It gives you a "Update Statistics" with the object_name to update. You just need to run results from the query on your database. I'll fine tune it for my workload and see if 20k is the best number.

SELECT
 -- st.object_id                          AS [Table ID]
 --, OBJECT_NAME(st.object_id)             AS [Table Name]
 --, st.name                               AS [Index Name]
 --, STATS_DATE(st.object_id, st.stats_id) AS [LastUpdated]
 --, modification_counter                  AS [Rows Modified]
'UPDATE STATISTICS sys.[' + OBJECT_NAME(st.object_id) + ']' as QueryToRun
FROM sys.stats st
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp
WHERE OBJECT_NAME(st.object_id) like 'change_t%'
AND OBJECT_NAME(st.object_id) in (SELECT  'change_tracking_' + convert(nvarchar(50),st.object_id) FROM sys.change_tracking_tables ctt inner join sys.tables st ON st.object_id = ctt.object_id inner join sys.schemas ss ON ss.schema_id = st.schema_id)
AND modification_counter > 20000


texts: MAXDOP or 1 only? Or sql can use a value between 1 and configured maxdop

texts:
Is there a way for sql server to use no of cores which is between 1 and the configured MAXDOP?

For example if maxdop is 8. Is it possible for sql to use 3? Lets say that resource gov, hints, and any other control mechanisms are not in place.

Or is it simple 1 for singlethreaded and 8 for parellelized?


texts: Script out objects that depend on in SQL Server

texts:
I have a requirement wherein I need to script out the definition of all objects that depend on a single table. See the below example.

Let's say I have a table and there are 15 objects(views, SPs, Functions, triggers etc) that depend on it. Then I need definition of all those 15 objects.

This needs to be done on 15 servers. Is there a way to script all those objects using a T-SQL script? Currently I am noting all the objects down in an excel and scripting out definitions of all the objects manually which is very tedious. :(


texts: PostgreSql - Search cost with compound selection

texts:
Assume i have the following query:

SELECT * FROM my_table WHERE Name="Tom" AND age=18;

The cost of finding records with
Name="Tom" is B1=130 blocks and the cost of finding records with age=18 is B2=200 blocks.

What would be the cost of my query ?
Is is the minimum between the two?

Thanks in advance!


texts:
I am working on encrypting a column in a postgre table using pgcrypto. The postgre version is 9.5.1. I have figured out how to encrypt using pgp_sym_encrypt() and decrypt using pgp_sym_decrypt(). My problem is figuring out how to keep the key hidden.

I am trying to avoid having the application decrypt the data that is returned. The data in this table is only sensitive if someone can view the entire table (450,000 rows) and put the data together, so its ok for the data to return to the application decrypted.

In pgAdmin, I have this in my query

 SELECT pgp_sym_decrypt(column1,'password')

Obvisouly, I don't want the query template our application uses to have the password stored in it and then pass it in everytime a query is ran. Any ideas would be appreciated?


texts:
I have a report with a variable number of columns. I can hide columns with the ismissing function based on an expression like Iif(Fields!Site.ismissing, true, false). However, the hidden columns still show when it's exported to Excel and the main purpose of this report is to be mailed out monthly via SharePoint.

My thought was to create a multivalue column whose value depends on a switch statement:

=Switch(
Fields!State.IsMissing = true, Fields!Region.Value
  , Fields!Site.IsMissing = true, Fields!State.Value
  , Fields!Site.IsMissing = False, Fields!Site.Value)

The idea is:

if the state is missing show the region field
if the site is missing show the state field
if the site is present show the site field

It works if I run the report at the maximum drill down, which is to say if I run it for a specific state the sites in the state show. But, if I go up levels in the hierarchy I get #error returned. Any help would be appreciated.


texts: How to do differential backups in a SQL Server RDS instance

texts: How to do differential backups in SQL Server rds instance?


texts: MySQL: How to create Column if not exists?

texts:
I am trying to create a Column for my table only if it does not exist.
I have researched a lot but I could not find any solution yet.

Is this really possible to conditionally create Column ?


texts: Vastly different query run-times on different servers

texts:
I've got two databases setup on two different SQL Servers. One is 2012 and one is 2016, but aside from that, they have the same CPUs, same core count, and same RAM. They're both VMs in the same location and all tests were run from the same client.

I created the tables and loaded them using the exact same method (SQLBulkCopy in .NET) and then applied the same CLUSTERED indexes to various fields on the tables (based on usage). I ran a query that is reasonably complex and returns about 200K records from a table that contains 4mil records and is JOINed to a few other tables that contain 10K (or so) records.

On the 2012 Server, my run-times (in ms) are:

21772
22864
23209

This seems pretty much about what I'd expect. But on the 2016 Server, the run-times are:

132440
122569
120030

Removing the CLUSTERED index on the largest table and replacing it with a NONCLUSTERED index results in:

72096
71042
69958

I'm looking for ideas on where to start looking for why the run-times would be so vastly different on two servers that are so similar. The run-times on 2012 are well within reason, but on 2016 they're untenable.


texts: Migration of records to the table with different column names

texts:
I have two databases, and I need to move data from one to another. The catch is they have a table with different set of columns. Here is how I need to rename columns in migration:

NULL -> ID (should be generated as I add records normally)
NULL -> category_id (should be fixed, e.g. 3)
date_art -> created_at
title_art -> title
short_art -> promo
full_art -> content

Is it possible to accomplish by a single query?


texts: How to make column data in upper case by default in SQL Server 2008?

texts:
Normally in SQL, select and insert statements use UPPER() and LOWER() functions to format the column cases. But in this application, whenever new data is inserted it must be stored as UPPER CASE by default.

Example:

insert --> INSERT
Apollo --> APOLLO
TyUiOP --> TYUIOP


texts: MySQL - Connection blocked because of many connection errors - How to find root cause?

texts:
I am developing a web application and am running into the dreaded host xxx blocked because of many connection errors message. There have been numerous questions on SO pertaining to this error and common wisdom is to flush hosts to clear the error and then bump up MySQL's max_connections setting in order to avoid this issue in the future. A summary of the issue from the MySQL manual is as follows:

  The number of interrupted connect requests allowed is determined by
  the value of the max_connect_errors system variable. After
  max_connect_errors failed requests, mysqld assumes that something is
  wrong (for example, that someone is trying to break in), and blocks
  the host from further connections until you execute a mysqladmin
  flush-hosts command or issue a FLUSH HOSTS statement. See Section
  5.1.3, “Server System Variables”.

I'd like to go a step further and determine the root cause of the issue for my application. I am relatively new to MySQL and would like to know what is the best way to trap such errors - does MySQL provide any logs or information as to which connections or queries failed or are the any tracing tools I can use to ascertain this information?

Thank you

JP


texts: Using Microsoft Access front-end with PostgreSQL

texts:
We are using a MS Access database at work and I am exploring the idea of using the MS Access front-end and use a Postgres back-end to better manage concurrent uses and add spatial capabilities with PostGIS extension.

I was wondering how the transition would be for end users that already uses Access to occasionally manipulate (viewing, inserting) non-spatial data with forms?

Would they actually notice that the back-end has changed?

Is this stable enough to use that combination in a productivity environment?


texts:
I am attempting to write a stored procedure to determine the difference in seconds between two times.  If I were to hardcode the values I know I can use

CAST(datediff(second, [start], [end])/3600.0 As Decimal(18,4)))

but I am needing to only get the difference if a condition is met.  My thought is to do something like this

Declare @diff decimal(18,4)
Create Table #Dope
(
  date1 datetime
  ,inlate decimal(16,4)
)

INSERT INTO #Dope (date1) Values
('2016-01-04 08:08:47.000'),
('2016-01-04 08:25:59.000'),
('2016-01-04 08:27:47.000'),
('2016-01-04 08:00:00.000')

if CAST(date1 As Time) > '08:00:00.000'
--Here is where the capture would come but I am unsure of how to type it
SET @diff = CAST(datediff(second, [], [date1])/3600.0 As Decimal(18,4))

How do I set the 1st datetime in my Set statement to be 8 a.m. of the day that I am needing the difference in s econds?  Meaning, I can't just hardcode '08:00:00.000' into my Set statement as the two dates my be different which would give me seconds spanning days and that is not what I am after.  I just want to see the difference in seconds between date1 and '08:00:00.000'


texts: Inconsistent behavior of aggregate functions about whether to return NULL on empty input?

texts:
On an empty input table, PostgreSQL (12.x) is returning NULL on some aggregate functions like array_agg() but normal values for others like count(), as shown below:

=> SELECT count(*) IS NULL FROM (SELECT 1 a) e WHERE a > 1;
 ?column?
----------
 f
(1 row)

=> SELECT array_agg(a) IS NULL FROM (SELECT 1 a) t WHERE a > 1;
 ?column?
----------
 t
(1 row)

Why does not array_agg() return a normal value (i.e., an empty array) in case of empty input?

This is somewhat disturbing as it's difficult to remember which ones can return NULL.

Related to this, what are the aggregates that return NULL on empties?


texts: Registered Server list and groups disappeared from SQL Server 2012 Management Studio

texts:
I recently installed SQL Server 2012. Due to some issue, the databases services did not install successfully, but Management Studio did, which was OK to start with, as I usually access remote servers.

I imported my groups of servers and worked happily.

Yesterday, I restarted my PC, and lo and behold, the list of groups and server is not there at all - all I have is an empty Object Explorer without any way to access my saved server and connect. See Screenshot below.

Through File > Connect Object Explorer I get the dialog where I can open what is listed there  but that's a pain, as I had many servers listed there and it was very user-friednly to have them listed and grouped nicely.

So my question is how can I get them listed there again.

Thanks!


texts: Will a SELECT . . . INTO use short-circuit evaluation for a COALESCE?

texts:
I'm building a stored proc that can take in two separate values that each can be used to identify a single record. One of these values may be null.

My logic is as follows: if IN_ORDER_ID is not NULL, save it to V_ORDER_ID. If it is NULL, then retrieve ORDER_ID with a query and save that to V_ORDER_ID. V_ORDER_ID is then used in the rest of the procedure.

Here is the code:

SELECT COALESCE(IN_ORDER_ID, order_id) INTO V_ORDER_ID FROM SCHEMA.ORDER WHERE SXC_ORDER_NUMBER = IN_SXC_ORDER_NUMBER;

My Question: Will the coalesce in this query still take advantage of short-circuit evaluation?


texts: How do you find home information for Oracle RAC on Unix?

texts:
Need help finding Oracle home path corresponding to a database instance in RAC environment. I am aware of few of the ways to achieve the same. Listing them below to avoid the same answers.

/etc/oratab
This file is not mandatory and hence may not have all instance information.
Parsing contents of Listener.ora
In RAC environment, the listener.ora can be located at non default location.
use TNS_ADMIN to find Listener.ora location and parse the file.
ORACLE_HOME env variable
May not be set always.
ps -ef | grep tns to get the home path from service name.
Gives path for currently running listener
select "SYSMAN"."MGMT$TARGET_COMPONENTS"."HOME_LOCATION"
from "SYSMAN"."MGMT$TARGET_COMPONENTS"
where "SYSMAN"."MGMT$TARGET_COMPONENTS"."TARGET_NAME" = <Database SID>
The schema sysman can be dropped after first time login to oracle.
SELECT NVL(SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '\', -1, 2) -1) , SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '/', -1, 2) -1)) FOLDER
FROM DBA_LIBRARIES
WHERE LIBRARY_NAME = 'DBMS_SUMADV_LIB';
So if a DBA changes Oracle Home (and hence the location of libqsmashr.so) after installation of Oracle, the path retrieved from above query would be invalid.
. oraenv
Works only for 11g

I am trying to find out a generic way which will work for all Oracle versions and it should not be dependent on anything which is not useful to DBA.

Do you have any way other than listed above to do the same?

Many Thanks in advance.


texts: Oracle 11g DBMS_CRYPTO invalid identifier

texts:
I'm trying to encrypt a column and I found many tutorials that use DBMS_CRYPTO.
However, when I test this, it says DBMS_CRYPTO is an invalid identifier.
I was using SYSDBA account.

I've searched the Internet but, to my surprise, I couldn't find an answer for this one.

How come DBMS_CRYPTO is an invalid identifier ?


texts: MongoDB: how to get the all the embedded data?

texts:
I have this data:

> db.Category.find().pretty()
{
    "_id" : ObjectId("542450a928db034349fb3bf2"),
    "name" : "A category",
    "products" : [
        {
            "_id" : ObjectId("542450a928db034349fb3bf3"),
            "name" : "Producto de categoria",
            "price" : 11.11
        }
    ]
}

When I run db.Category.find( {}, {products:1} ) I get this:

{ "_id" : ObjectId("542450a928db034349fb3bf2"), "products" : [ { "_id" : ObjectId("542450a928db034349fb3bf3"), "name" : "Producto de categoria", "price" : 11.11 } ] }

I get the data about one product, but how can I get the data about all products?


texts: Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop

texts:
I need the Oracle 11g Release 2 sample schemas (HR, OE, etc.) in order to do most of the available online tutorials.  I was hoping to install Oracle Express Edition on my Windows 7 laptop to get these; but I have never heard of anybody successfully installing Oracle XE on a 64-bit Windows platform.

Is there a version of Oracle XE 11g R2 available for Windows 7?  And if so, could you please point me to it?

Thanks...


texts:
SQLDriverConnect fails when attempting to connect to Failover_Partner.

Connection string being used:

Driver={SQL Native Client};Failover_Partner=tcp:dby;Server=tcp:dbx;
    Database=DB;Uid=user;Pwd=Pwd;Connect Timeout=300;Pooling=false

When we failover the database on dbx the next operation we perform SQLError(), which gives us the following information:

SqlState:  08S01
NativeError: 10054
MessageText: “TCP Provider: An existing connection was forcibly closed by
    the remote host."

At this point we disconnect then close off all open handles for that connection.

Then we call SQLDriverConnect using the same connect string.  When we only have a single open connection this call succeeds. When we have multiple open connections we receive the following error:

[Microsoft][SQL Native Client][SQL Server]Login failed for user 'user'.

On the dbx server the error log shows:

Logon   Error: 18456, Severity: 14, State: 38.
Logon   Login failed for user 'XSUser'. Reason: Failed to open the explicitly
    specified database. [CLIENT: 54.164.196.67]

On the dby server there are no new logs.

When we run with only one open connection the failover works fine.  We only have a problem when there are multiple open connections.

When we start up with dbx failed over, we successfully connect to dby on all connection with the connect string supplied. At this point, when we are connected to dby and we can fail over dby and all connections successfully connect to dbx.

We suspected there might be a problem with connection pooling.  However, I believe we have successfully disabled connection pooling:

SQLINTEGER pooling;
DBCRESULT rc = SQLGetEnvAttr(m_henv, SQL_ATTR_CONNECTION_POOLING ,
    &pooling, 0, NULL);

rc is 0, pooling is 0.

Any suggestions on how to proceed would be appreciated.

EDIT:

My testing procedure is to let the application connect and query some data.  Then with the application's connection still open, manually use Management Studio on the primary to Failover the database.  Then on the mirror server use Management Studio to run the same select stmt as the application, which succeeds.  At this point I release the application to execute a SELECT.  The call to SQLExecDirect fails, then I call SQLDriverConnect which also fails.  I would have expected the call to SQLDriverConnect to succeed since the SELECT in Management Studio succeeded.


texts: How do triggers work on partitioned tables?

texts:
I have partitioned a table on the basis of a column. This column can either have 0 or 1. So, only two partitions (p0, p1) have been created internally.
On this table there are 3 triggers which execute on insert, delete and update event.

Insert trigger - inserting a new record including ID of inserted row in another table.

Update trigger - Manipulate 2 other tables.

Delete trigger - Delete entries from associated tables.

Now, my concerns are below:

Do these triggers affect the performance negatively or have no effect?
In case of negative affect, what alternate approach I should take?
How does MySQL manage these 3 triggers on partitioned tables internally?
What measure changes happened in the trigger execution before and after table partition?


texts: Merging two large tables physically in SQL Server 2014

texts:
I have a requirement to merge two large tables with redundant data.

Table A and Table B both have more than a billion records, most of the data including columns are redundant except 5 additional columns in Table B.

I would like to merge them physically and delete table B by comparing the data between both table and load the data that doesn't exist in Table A but exist in Table B to Table A.

Ultimate goal is to get rid of Table B and load all data and columns to Table A and then partition table A.

Did anyone had experience doing this? How can I compare the data between two large tables? Can we use Informatica or any native SQL tools? Is it a good approach to merge the tables and partition Table A or first partition Table A and them merge the tables?

Please let me know if you need any details.


texts: MySQL failing and very slow on importing a large file

texts:
I have a 47 GB MySQL dump of a single table:

http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz

I ultimately want it into PostgreSQL, but since I didn't figure out an easy way to transform the MySQL SQL to PostgreSQL SQL I figured, I will get it into MySQL and then write a small ETL script to do this.

I initially tried to just load it using MySQL WorkBench Data Import/Restore, but it was failing.

Now I've run split -l 2000 commonswiki-latest-image.sql and have 20 files of roughly 2 GB each, but it still fails with:

23:12:28 Restoring C:\temp\commonswiki\xaa.sql

Running: mysql.exe --defaults-extra-file="c:\users\jmurdoch\appdata\local\temp\tmpi_ltz8.cnf"  --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=mediawikiimages < "C:\\temp\\commonswiki\\xaa.sql"

ERROR 2013 (HY000) at line 331: Lost connection to MySQL server during query

Operation failed with exitcode 1

00:54:17 Import of C:\temp\commonswiki\xaa.sql has finished with 1 errors

It's also terribly slow as it only imported 209236 rows during the nearly 2 hours, but I think there are about 20M items to import, so at that rate it would take 200 hours to import.

I'm using Windows 7, MySQL 5.6.14 and MySQL Workbench 6.

My main questions:

Is there a script which would feed MySQL with the splitted files and perform automatic error recovery in case it times out?
Is there a conversion tool which would convert MySQL SQL to PostgreSQL SQL without first loading it into a database?


texts: PostgreSQL: ERROR: cannot execute CREATE DATABASE in a read-only transaction

texts:
I installed PostgreSQL and and its GUI, pgAdmin III.
Setup already had a default database postgres.

When I am trying to create a new table or database, it is showing an error:

  ERROR:  cannot execute CREATE DATABASE in a read-only transaction

I uncommented the line default_transaction_read_only = off in postgresql.conf and executed the command pg_ctl start -D /home/jaison/progresData -Z datanode -l logfile but it is still showing the same error.

please help me to resolve this issue.


texts: How to delete a Backup from a backup set in SQL Server 2008 R2

texts: The title pretty much sums it up. We are running out of space on our backup disks, and need to remove old backups out of the set. I can't seem to find any information on this.


texts: How to set a 'real' field to accept an out-of-scale value?

texts:
I have an SQL Server database and one particular field that is a real.  The program that feeds it was modified and is now sending a value too small to be considered a real.

Is there a setting for that field I can set that will keep the DB from erroring out when the value is received, and just use the default (0)?

Here is the actual error:

and here is the properties dialog of the field:

The VBA (using ADO) that actually loads the value into the recordset is

rst.fields(pointname) = CSng(rCell.Value)

The low range of CSng is 1.401298E-45, while the low range for an SQL real is 1.18E-38.
I've tested and it works at E-38 and errors out at E-39.