texts: How to create a table with columns from other 4 tables(they don't have primary/foreign key relations)?

texts:
how can I create a SQL statement to create a table that has columns from 4 tables? So in my new created table I would like to extract the Id, Product Title, Items in cart(number) (from these 4 tables so it will be 4 columns with Items in cart (number)), Link. These tables have 3024 rows so how can I extract these information in one table?
I attached the tables(SQL) and the table(SQL) that I would like to have(I would like a SQL command to do that an insert or join). I will appreciate any help.

DROP TABLE IF EXISTS `csv_10_05`;
CREATE TABLE IF NOT EXISTS `csv_10_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `csv_11_05`;
CREATE TABLE IF NOT EXISTS `csv_11_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `csv_12_05`;
CREATE TABLE IF NOT EXISTS `csv_12_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `csv_13_05`;
CREATE TABLE IF NOT EXISTS `csv_13_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

And this is what I want (now I've created manually but I would like a query like join to extract the columns)

DROP TABLE IF EXISTS `amazon_stock`;
CREATE TABLE IF NOT EXISTS `amazon_stock` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `items in cart(10_05)` varchar(50) DEFAULT NULL,
  `items in cart(11_05)` varchar(50) DEFAULT NULL,
  `items in cart(12_05)` varchar(50) DEFAULT NULL,
  `items in cart(13_05)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


texts: How can i make SQL exec statements run in order

texts: SQL server 2012, I have multiple execs calling the same package with different parameters.  Normally i'll highlight one at a time and run them individually but I'd like to be able to hit go and run them all in order.  One exec pulls results that were created from another so it has to be in order.  Is there a way to pull this off?  i'm not sure if it's the default nature but I haven't been able to verify it.


texts: Architecture design for a large number of db records upgrade

texts:
Honestly struggled a bit with the title of the question. Here is the deal. I have a specific set of db operations on various tables across an db. In a simpler sense it's kind of a migration, that I want to roll out in production i.e. among live users.

So, the requirement is how I can do this in transaction without actually using transaction? There are a few assumptions and may be incorrect, please feel free to help me figure it out.

I think we can't use transaction in db level cause this process may take too long, have around 1k independent operation that may require to validate the existing state of a record and updating it.
Then again if for any reason, something thing goes wrong in between, I don't want to end up with partially executed migrations.
In another note, this feels like an software upgrade, when they put in new version changes and in case of an issue rolls back. If I strictly based on that, I have to get the current records that are going to be affected from migration, keep them for restoring purpose, then apply the migration. If anything fails, re-apply with original records (kind of reverting) with the same risk.

With a server (node.js) and live user involved, it sounds more risky and difficult to control the whole thing. Is there any idea on level of architecture that can help? Thanks in advance!

Updates

  Are you expecting that a transaction will lock a table for several
  seconds or minutes? Can it be broken into small batches?

  When you say it will "take too long," does that mean it has to
  complete within a certain timeframe for business requirements, or
  you're concerned it will block table updates for too long

Yes, it can take a significant time and indeed, we can divide the process into small batches. There are 2 things that we need to be ensure of. The whole operation (even broken into batches) should be transaction in nature. I am not really concerned about the time it's going to take, instead the tables/rows will stay locked for that time, in case anyone is trying out something.


texts:
Year    Value
2012     10
2013     20
2014      0

I need 10 * 20 * 0 for multiple rows.
If I use this function  EXP (SUM (LN (Value))), I get an ORA-01428  error:

  argument '0' is out of range.

I have seen How do I create a user-defined aggregate function? but that is for MySQL, not Oracle and my requirement is to include zeroes.


texts: SQL Server Log: The database 'master4IDR' is marked RESTORING

texts:
Why SQL Server log showing following message. I am dead sure, there is no such db on our production server naming "master4IDR"

{The database 'master4IDR' is marked RESTORING and is in a state that does not allow recovery to be run.}


texts:
I have removed Transparent Data Encryption (TDE) from my server, dropped the key and switched the databases to Simple, shrunk the log and then back to Full.

The LOG backups are now smaller as well as the full backups but the main mdf files are still the same size after removing TDE.

Is this normal? Is there a way to get these back to what they were before TDE?

I know the MDF files will stay the same size until you shrink them but the space available inside I would have expected to increase significantly but it didn't.
TDE seemed to double the size of them.


texts: How do you properly remove "REQUIRE SSL" from a single user?

texts:
I successfully granted REQUIRE SSL to a single user by doing...

mysql -u"${targetMySqlUser}" -p"${targetMySqlPass}" -e "GRANT USAGE ON dbname.* TO 'dbusername'@'%' REQUIRE SSL;"

but im failing on REMOVING or REVOKING this flag from the user, using revoke. i guess im fighting with the syntax. Is there a proper way to remove it with the "revoke" command, without revoking the whole permission ?

MySQL 5.5 manual , this site and the interwebs didn't helped me yet finding a proper counter-way.

This SQL statement will work.

UPDATE mysql.user SET ssl_type = '' WHERE ssl_type = 'any' ; FLUSH PRIVILEGES;

but I believe where is a GRANT REQUIRE SSL there must me a REVOKE REQUIRE SSL, isn't there ?


texts: Oracle 11g XE to 18c XE - What is the difference? Is 18c can be used like 11g XE?

texts:
I have worked on Oracle 11g XE about two months ago. After that I de installed them about a week ago. Now, I am curious to learn a little bit more about databases and I tried to installed Oracle 11g XE since it is free.

When I tried today to install, the site kept rerouting me to install Oracle 18c XE. Not sure why they force us to download "18c XE"?

In Oracle Site it says, they just released it 2 days ago. Is it okay to download and use like 11g XE? What are the differences between them?

(I can't find any solid evidence why I should use 18C??)


texts:
I have a situation where I prepare a list of buffers to process by a task queue. Currently my buffer table contains over 350.000 rows.

Every estimated 30 seconds the following query is ran to check what buffers to run:

SELECT "buffer_incomingbuffer"."id"
FROM "buffer_incomingbuffer"

WHERE (
  (
    "buffer_incomingbuffer"."run_date" <= '2019-02-28 08:20:19.243339'
    OR
    "buffer_incomingbuffer"."run_date" IS NULL
  )
  AND "buffer_incomingbuffer"."attempts" < 10
  AND "buffer_incomingbuffer"."completed_date" IS NULL
  AND "buffer_incomingbuffer"."locked_at" IS NULL
  AND "shop_id" = 'any-uuid-here'
)

ORDER BY "buffer_incomingbuffer"."created_date" ASC
LIMIT 10

With the following index:

buffer_incomingbuffer (run_date, attempts, completed_date, locked_at, shop_id, created_date)

This query takes over 500ms, whereas changing the ORDER BY into a DESC will take around 0.5ms.

Following the EXPLAIN ANALYZE execution of both queryies:

ORDER BY created_date ASC

Limit  (cost=0.42..794.50 rows=10 width=24) (actual time=541.306..541.553 rows=10 loops=1)
  ->  Index Scan using buffer_incomingbuffer_created_date_index on buffer_incomingbuffer  (cost=0.42..146110.50 rows=1840 width=24) (actual time=541.289..541.400 rows=10 loops=1)
        Filter: ((completed_date IS NULL) AND (locked_at IS NULL) AND ((run_date <= '2019-02-28 08:20:19.243339+00'::timestamp with time zone) OR (run_date IS NULL)) AND (attempts < 10) AND (shop_id = '43ca2e21-e1f5-4151-8fa3-c429c638d03d'::uuid))
        Rows Removed by Filter: 244674
Planning time: 0.168 ms
Execution time: 541.688 ms

ORDER BY created_date DESC

Limit  (cost=0.42..794.50 rows=10 width=24) (actual time=0.085..0.391 rows=10 loops=1)
  ->  Index Scan Backward using buffer_incomingbuffer_created_date_index on buffer_incomingbuffer  (cost=0.42..146110.50 rows=1840 width=24) (actual time=0.058..0.192 rows=10 loops=1)
        Filter: ((completed_date IS NULL) AND (locked_at IS NULL) AND ((run_date <= '2019-02-28 08:20:19.243339+00'::timestamp with time zone) OR (run_date IS NULL)) AND (attempts < 10) AND (shop_id = '43ca2e21-e1f5-4151-8fa3-c429c638d03d'::uuid))
Planning time: 0.206 ms
Execution time: 0.545 ms

To me it seems the biggest (for me unexplainable) difference between these two query plans is the "filter" part.

Reason of the order by is because we'd like to get the things that "came in" first, also "go out" first. The current execution time is a hiccup for the application and I am not sure how to tackle this issue. Like to hear if I am missing something!


texts: Generate insert statements for table AND related data

texts:
I'm looking to move data from tables and related tables between 2 databases. The trouble is the primary keys are out of sync.

Is anyone aware of a tool which can generate insert statements for a given table and it's related data (perhaps walking foreign keys)?

I've been looking at SSMS toolpack, RedGate's tools, Database project data compare, various open source data diff tools, etc etc, but not found anything.

I'm aware of being able to walk foreign keys, so perhaps some amazingly clever SQL script might be able to do it?

Seeing as I've looked at so many tools, I'm guessing it's probably blooming difficult / impossible to do it fully automated. But perhaps a technique that needs some manual intervention as well?

Or perhaps anyone has hints on how they handle this sort of task. I don't really fancy writing lots of manual sql to handle the insert - as it's bound to be error prone.

What I'm really hoping for is a way of generating SQL to tie up the PK and FK's whilst doing the inserts in the new database.

Say for example we had:

+--------------+--------------+
|     personid |     name     |
+--------------+--------------+
|            1 |         bob  |
|            2 |         fred |
+--------------+--------------+

+----------+----------------+----------+
| animalid |     animal     | PersonID |
+----------+----------------+----------+
|        1 |         cat    |        1 |
|        2 |         dog    |        1 |
|        3 |         lizard |        2 |
+----------+----------------+----------+

It would be awesome to copy bob to the new database and bob's cat and dog. Then fred and fred's lizard.


texts: Get the value of one attribute from the reference key of another table?

texts:
I have two tables named as ps_order_detail and ps_product_lang in MySQL. I want to get the product name from one table whose reference key is in another table. I have two tables. First the ps_order_detail database table is like this

CREATE TABLE IF NOT EXISTS `ps_order_detail` (
  `id_order_detail` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `product_attribute_id` int(10) unsigned DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id_order_detail`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `ps_order_detail`
--

INSERT INTO `ps_order_detail` (`id_order_detail`, `product_id`, `product_attribute_id`, `product_name`) VALUES
(1, 5, 11, 'iPod touch - Capacité: 32Go'),
(2, 2, 2, 'iPod shuffle - Color : Green'),
(3, 5, 9, 'iPod touch - Disk space : 8GB');

and another table for ps_product_lang is like this

CREATE TABLE IF NOT EXISTS `ps_product_lang` (
  `id_product` int(10) unsigned NOT NULL,
  `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
  `id_lang` int(10) unsigned NOT NULL,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id_product`,`id_shop`,`id_lang`),
  KEY `id_lang` (`id_lang`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ps_product_lang`
--

INSERT INTO `ps_product_lang` (`id_product`, `id_shop`, `id_lang`,  `name`) VALUES
(1, 1, 1, 'iPod Nano', 'In stock'),
(2, 1, 1,  'iPod shuffle'),
(3, 1, 1,  'MacBook Air'),
(4, 1, 1, 'MacBook'),
(5, 1, 1, 'iPod touch'),
(6, 1, 1, 'Belkin Leather Folio for iPod nano - Black / Chocolate'),
(7, 1, 1, 'Shure SE210 Sound-Isolating Earphones for iPod and iPhone'),
(8, 1, 1, 'Lappy');

Now you can see that in the table  ps_order_detailthere is one column named as product_id and in the table ps_product_lang there is one column for name . so my question is how can I get the name of product by combining both ps_product_lang and ps_order_detail table.
I want to get the product name from ps_order_detail product_ida attribute? So any help and suggestions will be really appreciable. Thanks.


texts: How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline?

texts:
MySQL Server 1 is running as Master.
MySQL Server 2 is running as Slave.

With both DBs online, they are in "perfect sync". If Slave goes offline, there's no problem if Master still online; they will go back sync once the Slave is online again.

Besides the server configuration, I redirected the connection (with JSP code) for the Slave DB if the Master goes offline (I tested of course with /etc/init.d/mysqld stop).

When the Master goes back online, is there any automatic method to sync the Master with Slave updates?


texts: The mysql-bin.xxxx files are growing very fast

texts:
I have a replication enviroment and is working fine, but today my disk going full.

I check and see that the mysql-bin.xxx files are growing very very fast (I know they contain all changes in the database(s), so naturally they will grow, but today it is not normal the way that they are growing... that is, very fast and continuously!).

Then I use mysqlbinlog to see the content of de last mysql-bin.xxx file but is to dificult to see any error.

My question

Is there some way to perceive, check, anything... where or why my mysql-bin files are growing?


texts: What will happen if the primary shard for a mongodb database is down?

texts:
Well as I understand each database has it's primary shard, which is mostly the first shard added to mongodb cluster. And we know that balancer will not split chunks until the chunksize is bigger than the half of the max chunk size, so the balancer will not move any data to the specified shards, for example when we use tag aware sharding. So I wonder what will happen when the primary shard is down?

Thanks a lot.


texts:
I'm trying to find the the lowest street address number for every street in my area.  My results return the wrong value, so I need help understanding why it's returning the wrong value and how to correct it so I get the desired results.

I'm using SQLServer 2012

TABLE (A) columns:

ZONE_NUMBER (int, null),
Street_NUMBER (char(10), null),
Street_Name (varchar(255), not null),
Street_Type (char(5), null)

TABLE (A) values:

ZONE_NUMBER | STREET_NUM | STREET_NAME | STREET TYPE |
13             1502         COTTONWOOD    DR
13             1544         COTTONWOOD    DR
13             1564         COTTONWOOD    DR
13             720          COTTONWOOD    DR

Query:

SELECT
   zone_number,
   min(street_number) as street_number
FROM
   A
GROUP by ZONE_NUMBER

Expected Results:

ZONE_NUMBER | STREET_NUMBER
13            720

Actual Results:

ZONE_NUMBER | STREET_NUMBER
13            1502

If I change from min() to max(), I get my desired results:

ZONE_NUMBER | STREET_NUMBER
13            720

Why?  Is there something I'm missing?

Also, if '0' Street_number had it's own record, how can I skip '0' and get the next lowest result?

Thanks for any considerations for answering this problem.


texts: Select Column value based on two values in another column

texts:
I am using the below table:

1   0051ML66220600132482    06:00:00        06:00:00        1538    100 0   1
2   0051ML66220600132482    06:00:00        06:00:00        1540    200 0   0
3   0051ML66220600132482    06:00:00        06:00:00        1541    300 0   0
4   0051ML66220600132482    06:01:00        06:01:00        1542    400 0   0
5   0051ML66220600132482    06:01:00        06:01:00        1543    500 0   0
6   0051ML66220600132482    06:02:00        06:02:00        1544    600 0   0
7   0051ML66220600132482    06:03:00        06:03:00        1546    700 0   0

Table structure is like below:

------------------------------------------------------------------
--  TABLE stop_times
------------------------------------------------------------------
CREATE TABLE stop_times
(
    id int(12),
    trip_id varchar(100),
    arrival_time varchar(8),
    arrival_time_seconds int(100),
    departure_time varchar(8),
    departure_time_seconds int(100),
    stop_id varchar(100),
    stop_sequence varchar(100),
    pickup_type varchar(2),
    drop_off_type varchar(2)
);

I am attempting to get DISTINCT trip_id if it matches both the destination and arrival stop_id

I have tried the below SQL with no luck:

select DISTINCT trip_id from stop_times where stop_id=1538 AND stop_id =1540;

Where it should produce: 0051ML66220600132482

I have also tried an INNER JOIN like below:

SELECT
    t.trip_id,
    start_s.stop_name as departure_stop,
    end_s.stop_name as arrival_stop
FROM trips t
INNER JOIN stop_times start_st ON t.trip_id = start_st.trip_id
INNER JOIN stops start_s ON start_st.stop_id = start_s.stop_id
INNER JOIN stop_times end_st ON t.trip_id = end_st.trip_id
INNER JOIN stops end_s ON end_st.stop_id = end_s.stop_id
WHERE
   start_s.stop_id = 1538
  AND end_s.stop_id = 1540;

But it is sooo slow, and takes about 8-15 seconds for this simple query.

Explain Added:

What is the fastest/best way to write this query?


texts: List database CONNECT privileges on PostgreSQL

texts:
How can I view database-level privileges like CONNECT of users?

I found 2 ways already, but neither of them is perfect:

Use function has_database_privilege().

This works, but I have to run it manually for every user one by one:

SELECT has_database_privilege('david', 'postgres', 'connect');

This one seems to return an incomplete result. It returns NULL at first, but david and other users can still connect to postgres DB. Only after I run any GRANT command (and then REVOKE of course) on that DB, the table is filled with some result: {=Tc/postgres,postgres=CTc/postgres}. So I guess I have to GRANT and then REVOKE every time before I use this method?

SELECT datname as "Relation",
       datacl as "Permissions"
FROM pg_database
WHERE datname = 'postgres';

Using PostgreSQL 10.9 on Ubuntu


texts: Postgres : Using WITH .... AS insert into to return non-inserted column

texts:
I'm creating an import job which uploads to a temporary table and then splits the data into two separate tables, joined by a key.

What I'm doing is more complicated than the following but this simple example demonstrates what I want to do.

So let's say I have a temporary table temp_import defined by :

create temp_import(
    row_id int,
    name varchar(32),
    email varchar(255)
)

I have two target tables defined by :

create users (
    userid int not null default nextval('tmp_uid_seq'::regclass),
    username varchar(32)
)

create emails (
    userid int,
    email varchar(255)
)

Currently I have some plpython which inserts a row at a time into users returning userid, followed by a second insert which uses the returned userid to populate emails.

loop:
insert into users (username) values 'Blah' returning userid
insert into emails (userid, email) values (userid, 'Blah2')
goto loop

This works OK but I want to extract better performance by doing set based operations.

I want to create a temporary mapping table :

create table mapping (
    row_id int,
    userid int
)

and try to do something like :

with rows as (insert into users (username)
select username from temp_import returning userid, row_id)
insert into mapping (userid, row_id)

The issue here is that I cannot return row_id as I am not selecting it for insert into the target table

If I could do this I could populate the emails table in one go:

insert into emails (userid,email)
select userid,email from temp_import t, mapping m
where t.row_id = m.row_id

Anyone know how I can return the row_id in the with rows as statement above?

Thanks in advance.


texts: Is database partitioning only created to get a better performance? and why should one partition a table, and a database?

texts:
I was asked if SQL Server can handle huge databases ( for them a huge database is 100GB). I know it can. I worked with 4tB databases already. But I would like to ask, thinking about really huge databases ( I don't know. maybe 30TB ).

In a busy environment, a 30tb database would be a problem if not indexed or partitioned correctly. An example is, if a table is not partitioned, when those 500 concurrents queries run, the table would be really slow to give us results, so we can partition this table, let's say, by months or years.

Is Database partition and Table partition the same? Not literal the same, but, is this for the same result? Only performance?

In the company where I work, we barely have a query. We only "hold" data. theres less then 3 queries running each time. If I have a huge database, will I have performance issues ( With so little interaction on it?)

I don't know if I could explain what I would like to.


texts: Performance of dynamic SQL Server reporting queries?

texts:
I have to build an application that allows the end user to select what columns they want in their report.

For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns available that could potentially be selected.

I am worried that this will greatly harm the performance of the query. What are my options? Create indexes for each possible query is not really possible.

Would a columnstore index be beneficial?


texts: Surprising results for data types with type modifier

texts:
While discussing a recursive CTE solution for this question:

Get the last 5 distinct values for each ID

@ypercube stumbled across a surprising exception, which lead us to investigate the handling of type modifiers. We found surprising behavior.
1. Type cast retains the type modifier in some contexts
Even when instructed not to. The most basic example:
SELECT 'vc8'::varchar(8)::varchar

One might expect varchar (no modifier), at least I would. But the result is varchar(8) (with modifier). Many related cases in the fiddle below.
2. Array concatenation loses the type modifier in some contexts
Without need, so this errs on the opposite side:
SELECT ARRAY['vc8']::varchar(8)[]
     , ARRAY['vc8']::varchar(8)[] || 'vc8'::varchar(8)

The 1st expression yields varchar(8)[] as expected.
But the 2nd, after concatenating another varchar(8) is watered down to just varchar[] (no modifier). Similar behavior from array_append(), examples in the fiddle below.
All of this does not matter in most contexts. Postgres does not lose data, and when assigned to a column, the value is coerced to the right type anyway. However, erring in opposite directions culminates in a surprising exception:
3. Recursive CTE demands data types to match exactly
Given this simplified table:
CREATE TABLE a (
  vc8  varchar(8)  -- with modifier
, vc   varchar     -- without
);
INSERT INTO a VALUES  ('a',  'a'), ('bb', 'bb');

While this rCTE works for the varchar column vc, it fails for the varchar(8) column vc8:
WITH RECURSIVE cte AS (
   (
   SELECT ARRAY[vc8] AS arr  -- produces varchar(8)[]
   FROM   a
   ORDER  BY vc8
   LIMIT 1
   )

   UNION ALL
   (
   SELECT a.vc8 || c.arr  -- produces varchar[] !!
   FROM   cte c
   JOIN   a ON a.vc8 > c.arr[1]
   ORDER  BY vc8
   LIMIT 1
   )
   )
TABLE  cte;

ERROR: recursive query "cte" column 1 has type character varying(8)[] in non-recursive term but type character varying[] overall
Hint: Cast the output of the non-recursive term to the correct type. Position: 103

One quick workaround would be to cast to text.
A plain UNION query does not exhibit the same problem: it settles for the type without modifier, which is guaranteed to preserve all information. But the rCTE is more picky.
Also, you wouldn't run into problems with the more commonly used max(vc8) instead of ORDER BY / LIMIT 1, because max() and friends settle for text right away (or the respective base type without modifier).
SQL Fiddle demonstrating 3 things:

A range of example expressions including surprising results.
A simple rCTE that works with varchar (without modifier).
The same rCTE raising an exception for varchar(n) (with modifier).

The fiddle is for pg 9.3. I get the same results locally for pg 9.4.4.
I created tables from the demo expressions to be able to show the exact data type including the modifier. While pgAdmin shows this information out of the box it's not available from sqlfiddle. Remarkably, it's also not available in psql (!). This is known shortcoming in psql and a possible solution has been discussed on pgsql-hackers before - but not implemented, yet. This might be one of the reasons the issue has not been detected and fixed, yet.
On the SQL level, you can use pg_typeof() to get the type (but not the modifier).
Questions
Together, the 3 issues make a mess.
To be precise, issue 1. is not involved directly, but it ruins the seemingly obvious fix with a cast in the non-recursive term: ARRAY[vc8]::varchar[] or similar, which adds to the confusion.
Which of these items is a bug, a glitch or just how it's supposed to be?
Am I missing something or should we report a bug?


texts: How to calculate quantity of WAL in db without archiving?

texts:
I would like to find out a quantity of WAL files which are being used in PostgreSQL database in last 24h. I was assigned to do Log-shipping but before I do it I want to know how many WAL files are being used in last 24hours in order to predict how much space I need on external storage.
Unfortunately pgmetrics doesn't have such information. Do you know how to do it?

WAL Files:
WAL Archiving?       no
WAL Files:           5
+--------------------+--------------+
|            Setting |        Value |
+--------------------+--------------+
|          wal_level |      minimal |
|    archive_timeout |            0 |
|    wal_compression |          off |
|       max_wal_size | 64 (1.0 GiB) |
|       min_wal_size |   5 (80 MiB) |
| checkpoint_timeout |          300 |
|   full_page_writes |           on |
|  wal_keep_segments |            0 |
+--------------------+--------------+


texts: How fast does SQL Server start moving data if you add another Rows Data File?

texts:
Our sql server migration was delayed (naturally) and it is going to run out of space before the new cut over date. In order to keep the database up, I need to give it another database file to start writing to.

I've got space allocated and ready to go, but when I create it, it SQL Server going to immediately start moving data over to the new file?

Question: Would I be better off creating two new files, then running

    dbcc shrinkfile (livendb, emptyfile)

on the old one? Ensuring they become equal right away?


texts: Should innodb_buffer_pool_size be much larger than the innodb_buffer_pool_bytes_data?

texts:
Our old server had 32GB total RAM, 18GB allocated to innodb_buffer_pool_size.
It also had 10GB for the php_memory_limit.

The new one had 16GB RAM, 9GB for innodb, 5GB for php. The same application seemed to have a slower response time.

Checking the status variables on the old server, I see that innodb_buffer_pool_bytes_data is only at 8.1 G anyway. So did we ever really need to allocate 18 GB? Or was that overkill? Was it perhaps the difference in PHP's memory that made the difference?

Should I be giving more memory to PHP, less to MySQL?

By the way, the new server's innodb buffer pool is currently using 8.2 G, about the same as the old one.


texts: database schema for a product with multiple categories and hierarchical categories

texts:
I need some opinion on whether I am going in the right direction with this.

I am trying to build a e-commerce site (with MySQL backend) where

a single product can belong to more one than category
categories are hierarchical in nature

For e.g.

Food > Fruit > Red > Cherry
     > Fruit > Yellow > Banana
     > Healthy > Cherry

Cherry is both Healthy and Red.
There is a hierarchy for categories

The way I am planning to implement this in the backend is:

Modified PreOrder traversal for storing hierarchy of categories. The tree would not hold products, but only categories.
ProductID - CategoryID: many-to-many table: where each product can be stored with multiple categories.

Is this the right way to go ?

One of my concerns in this approach is; even though new categories won't be added very frequently, but when we would have to add a new category, we will have to make a whole lot of updates in productID-cateogryID table since the category IDs would now be pointing to something different!

Is there a better way to deal with this ?

EDIT: Modified preorder traversal is better known as: Nested Set model


texts: sp_GetAppLock slow performance unusual behaviour

texts:
We had a critical issue yesterday where by a 1 second stored procedure started to take minutes to execute. I don't know the business logic as of yet, however want to understand the impact of using sp_GetAppLock.

So scenario is:

Stored procedure called usp_abc, no parameters

Code within stored procedure looks like this:

BEGIN
BEGIN TRANSACTION

sp_getapplock @Resource = usp_abc, @LockMode = 'Exclusive'

with cte_1
( SELECT TOP 1 ID FROM tbl1 WHERE Status = 'OK')

UPDATE tbl1
SET Status = 'Complete'
WHERE ID = (SELECT ID FROM cte_1)

COMMIT TRAN

END

This stored procedure is called from the same application but on a load balancer from two different application servers. The stored procedure is constantly executed, yesterday when we had the issue more so (multiple times per second) because of backlog.

What I was seeing yesterday when querying sp_who2 was the execution of the stored procedure was being blocked by earlier executions. There were around 5 SPIDs doing the same thing. I traced back through the first SPID to take a look at resource wait. It fluctuated between SOS_SCHEDULER_YIELD and APPLICATION:5.0 "usp_abc" - basically itself.

It looked like it had got itself in a nasty loop, however the executions sometimes did complete. When running the update on its own without the lock it ran sub second as expected.

Once the load balancer was switched off and requests were coming from a single source the issue went away at the query took sub second.

This isn't our code, however we want to make recommendations on how to stop this occurring again. From what I can tell it looks like the developer put this in place for concurrency reasons and possibly to avoid deadlocks.

However judging by the code the native sql read committed should suffice in this situation because it should lock the data being updated meaning it is not possible to update the same record twice. I don't think deadlocking will be an issue with this query.

What are your thoughts?


texts: What is a better way to keep "user online"?

texts:
I'm working on software for social network and I plan it to scale a lot. Actually, I'm updating timestamp column every time user clicks, to be sure they're marked as online. When they do not click for 30 seconds, they're marked as offline. So simply, my queries are

UPDATE Users SET timestamp = UNIX_TIMESTAMP WHERE uid = 1; -- UNIX_TIEMSTAMP is current unix timestamp given by PHP's time()
SELECT Name FROM Users WHERE timestamp > (UNIX_TIMESTAMP - 30); -- (UNIX_TIMESTAMP - 30) is given directly by PHP (time() - 30)

In this answer, answerer said that

  Will you be updating this table continuously? I doubt that would scale

is there a better way to update user's online status that would scale? Or do you think this is fine?


texts: what's extsort.* (8 - 11 MB) in MongoDB dbpath? Directory _tmp

texts:
I never saw this in my dbpath and didn't found any explanation with a search engine.

We run MongoDB 3.2.8

# ls -alh; pwd
total 151M
drwxr-xr-x. 2 mongod mongod 4.0K Sep 12 22:18 .
drwxr-xr-x. 6 mongod mongod  12K Sep 12 22:16 ..
-rw-r--r--. 1 mongod mongod 8.0M Sep 12 21:54 extsort.0
-rw-r--r--. 1 mongod mongod  10M Sep 12 21:54 extsort.1
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 22:02 extsort.10
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 22:07 extsort.11
-rw-r--r--. 1 mongod mongod 9.9M Sep 12 22:07 extsort.12
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 22:11 extsort.13
-rw-r--r--. 1 mongod mongod 9.9M Sep 12 22:12 extsort.14
-rw-r--r--. 1 mongod mongod 8.0M Sep 12 22:16 extsort.15
-rw-r--r--. 1 mongod mongod  11M Sep 12 22:18 extsort.16
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 21:55 extsort.2
-rw-r--r--. 1 mongod mongod 9.9M Sep 12 21:55 extsort.3
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 21:55 extsort.4
-rw-r--r--. 1 mongod mongod 9.8M Sep 12 21:55 extsort.5
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 21:56 extsort.6
-rw-r--r--. 1 mongod mongod  11M Sep 12 21:57 extsort.7
-rw-r--r--. 1 mongod mongod 8.0M Sep 12 21:58 extsort.8
-rw-r--r--. 1 mongod mongod 9.9M Sep 12 22:01 extsort.9
/data/name-replicaset/name-replicaset_0/_tmp


texts: SQL Server: backup error 112: not enough space on the disk - true or not?

texts:
Last night my weekly backup on SQL Server 2014, running on Windows Server 2012 R2, failed with the following error in the log:

  failed with the following error: "A nonrecoverable I/O error occurred on file "D:\MSSQL\MSSQL12.MSSQLSERVER\Backup\backup-xxxx.bak:" 112(There is not enough space on the disk.).

When I checked disk space I found there was still 1 GB free, and the .bak file looked ok, all 4GB of it, not compressed.

So I ran a RESTORE VERIFYONLY command on that file:

RESTORE VERIFYONLY FROM DISK = 'D:\MSSQL\MSSQL12.MSSQLSERVER\Backup\backup_xxxx.bak'

that responded:

  The backup set on file 1 is valid.

So which version of the story should I trust?

Note: According to Pinal Dave (http://blog.sqlauthority.com/2016/04/14/sql-server-backup-randomly-failing-error-112-not-enough-space-disk/) the first error is not accurate, especially if using compressed backups.


texts: Is it possible to set-up MySql Replication to have one slave listening to two different masters?


texts: Can we minimise the risk of using xp_cmdshell?

texts:
Using xp_cmdshell can be quite helpful and sometimes possibly the only answer to some scenarios. I've read some posts on the internet that enabling xp_cmdshell might jeopardize the security of the database/server.

My question is that, is there anything we can do to reduce the risk? For example can we set some restrictions/applying users role, etc providing safeguards to mitigate the risk?

Thanks.


texts: Cannot find /etc/init.d file in opensolaris

texts:
While installing MySQL , I noticed a point, I mean to start the MySQL server manually , use the command
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
The error shown is :
cp: cannot create regular file `/etc/init.d/mysql': No such file or directory

I have switched in as root user.I could see /etc directory but not init.d.
I am trying to do this in Solaris 10, and MySQL version is 5.5.

Reference : http://dev.mysql.com/doc/refman/5.5/en/binary-installation.html


texts: Storage snapshots for consistent backup of postgresql - different data and log volumes

texts:
We are running many Linux VM's in a vmware/shared storage environment, each running its own instance of postgreSQL (a mix of 9.0 and 9.3). Currently, the entire VM sits on a single root partition/volume, and we've had great success (~8 years) using storage-based snapshots of the underlying VMFS volumes for backup/restore process (and replication to our DR site).

Due to the architecture of our storage, it would be advantageous to separate postgres WAL files to a non-cached, mostly-write volume to give us less cache churn on the storage side. With our storage (Nimble Storage), we can assign both volumes to a single protection/snapshot group, but I haven't been able to elicit from our vendor that the snapshots will happen at EXACTLY the same time across all volumes in the protection group - it likely will, but there's always that chance that its milliseconds apart.

To that end, we ran some experiments, all while writing data to the DB as fast as possible using pg_bench. After the experiments, we restored our snapshot'ed volumes and started the VM+postgres

Snapshot both data and log volumes close to simultaneously - result: DB recovered
Snapshot data volume first, log volume ~1 minute later - result: DB recovered
Snapshot log volume first, data volume ~1 minute later - result: DB recovered
Snapshot log volume first, data volume ~3 minutes later, after a WAL checkpoint wrote new data to datafiles: result: DB recovered

So testing seem to tell us as long as both snapshots are consistent at the volume level, and relatively close together, you get a consistent copy of the DB, based on the time of the WAL/Log volume snapshot.

My question: Is this safe? What are the corner cases we are missing in our testing, and what could go wrong?

Postgres' doc indicates this is not safe, but testing seems to indicate its pretty robust:
http://www.postgresql.org/docs/9.1/static/backup-file.html

  If your database is spread across multiple file systems, there might not be any way to obtain exactly-simultaneous frozen snapshots of all the volumes. For example, if your data files and WAL log are on different disks, or if tablespaces are on different file systems, it might not be possible to use snapshot backup because the snapshots must be simultaneous. Read your file system documentation very carefully before trusting the consistent-snapshot technique in such situations.

NOTE: Yes, we know about other options to make sure they are consistent, like putting PostgreSQL into hot backup mode or using our storage's VMware integration to quiesce the VM's themselves, but we are looking for a storage-only solution for speed, convenience, and zero impact to our clients.


texts: Why Do Replication Deletes Require sysadmin Access

texts:
I am running merge replication with SQL 2012.

There seems to be a nasty consequence of the delete triggers added for replication in SQL 2012.

Inside the delete triggers are this,

select @xe_message = CAST('replica_id: ' + convert(nvarchar(100), @replnick) + ', article_id: ' + convert(nvarchar(100), @tablenick) + ', rowguid: ' + case when @article_rows_deleted = 1 then convert(nvarchar(100), @rowguid) else N'0' end + ', generation: ' + case when @is_mergeagent = 1 then N'0' else convert(nvarchar(100), @newgen) end + ', Reason: -1' AS varbinary(1000));
        exec master..sp_repl_generateevent 1, N'Event : ppm_insert', @xe_message

And an error occurs when someone does a delete if they are not in the sysadmin role,

Msg 8189, Level 14, State 10, Procedure sp_repl_generateevent, Line 1
You do not have permission to run 'SP_TRACE_GENERATEEVENT'.

So are you telling me that every user of my database has to have sysadmin access if they are going to be doing deletes?

This seems wrong. Is there a way to turn this tracing off or an alternative way to remove this requirement?


texts: How to change password version by myself in oracle?

texts:
Oracle documentation (18c) says:

....
You must expire the users who have only the 10G password version, and
do not have one or both of the 11G or 12C password versions.
For example:
ALTER USER username PASSWORD EXPIRE;
Ask the users whose passwords you expired to log in. When the users
log in, they are prompted to change their passwords. The database
generates the missing 11G and 12C password versions for their account,
in addition to the 10G password version. The 10G password version
continues to be present, because the database is running in the
permissive mode.
...

But, there is some way to do it by myself? (and to use the same old password)


texts: Active Directory authentication with SQL Server on Linux containers

texts:
I have  a problem and hope someone can help me. I have SQL Server as docker container. I want our developers able to login in SSMS (SQL Server Management Studio) using their Active Directory users. I followed this procedures which was Microsoft Doc page but it didn't work. The log file in container shows about error:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: 192.168.1.52]
Error: 17806, Severity: 20, State: 14.
SSPI handshake failed with error code 0x80090304, state 14 while establishing a connection with integrated security; the connection has been closed.
Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The Local Security Authority cannot be contacted [CLIENT: 192.168.1.52]

My container run successfully and I can connect to it using sa user but, I can't login using Windows Authentication.
This is my docker run command:
# docker run -itd -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<pass>" -p 1433:1433 --name msdb \
--user root \
-v /root/mssql:/var/opt/mssql/ \
-v /root/mssql/krb5.conf:/etc/krb5.conf \
--dns-search <full domain> \
--dns 192.168.1.49 \
--add-host <dc.mydomain>:192.168.1.49 \
--add-host <mydomain>:192.168.1.49 \
--add-host <my domain.com>:192.168.1.49 mcr.microsoft.com/mssql/server

Also, I checked this checklists but the problem didn't resolve and I got the same error again.
I hope some one can help me how can I resolve it.
Thanks


texts: Will SQL Server "int" datatype reliably truncate (and not round) decimal values when they are input?

texts:
I have a user with software that sometimes sends back a UNIT's ID as an integer(ex. 1000), and sometimes with a small decimal value appended (ex. 1000.001).  The software automatically generates MSSQL (2016 SP1) tables with "real" datatypes, and to "top it off" makes this UNIT columns part of a Primary Key.  This causes issues on the data input side and the reporting side.

I realize this is really a "bug" with the software, but I'm trying to be helpful on the DB side.  I have proven in a Sandbox region that I can change the "real" datatype to an "int", that this does not cause data loss with existing data, and every input scenario shows that the decimal values of any input will be truncated in the "int" column(not rounded).

My questions are, "Will SQL Server always reliably truncate decimal values when input into an "int" datatype (We do not have a way to dig into the Packaged App and force it to ROUND)?  Are there any scenarios where this will error, or cause issues?"  I can't find this documentation anywhere.


texts: How to reformat Oracle SQL quadratic-equation solver script?

texts:
I was wondering how I might be able to tweak this SQL script:

ALTER SESSION
SET NLS_NUMERIC_CHARACTERS='. ';

SELECT 'Not a quadratic equation.' ans
FROM dual
WHERE &&A = 0
UNION
SELECT 'x = ' || to_char(-&&B/2/&A)
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&&C = 0
UNION
SELECT 'x1 = ' || to_char((-&B+sqrt(&B*&B-4*&A*&C))/2/&A) || ', x2 = ' || to_char(-&B-sqrt(&B*&B-4*&A*&C))/2/&A
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&C > 0
UNION
SELECT 'x1 = (' || to_char(-&B/2/&A) || ',' || to_char(sqrt(-&B*&B+4*&A*&C)/2/&A) || '), ' || 'x2 = (' || to_char(-&B/2/&A) || ',' || to_char(-sqrt(-&B*&B+4*&A*&C)/2/&A) || ')'
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&C < 0;

to treat the quadratic equation solver-output from the inputs (a=1, b=5, c=1) displayed in the below image:

as separate values contained within separate columns denoted "Answer 1" and "Answer 2" respectively; so "Answer 1" column contains -2.0871... and "Answer 2" column contains -4.791...

I took a few hacks at it but I couldn't quite format according to the aforementioned.


texts: How to disable triggers from nesting/recursing when accessing the same table

texts:
So I have this data

+----+----------+-------+---------------+----------------+------------+--------+
| id | date     | accid | accid_currbal | Action         | Desc       | Amount |
| 1  | 05/06/17 | 1     | 7777          | Create Account | some desc… | 7777   |
| 2  | 05/06/17 | 1     | 32091         | Add Funds      | some desc… | 24324  |
| 3  | 05/06/17 | 1     | 32100         | Add Funds      | some desc… | 9      |
| 4  | 05/06/17 | 1     | 32600         | Add Funds      | some desc… | 500    |
+----+----------+-------+---------------+----------------+------------+--------+

And an BEFORE_UPDATE trigger that accesses this table when it fires

CREATE ..... TRIGGER
`db_moneytracker`.`transactions_BEFORE_UPDATE` BEFORE UPDATE ON
`transactions` FOR EACH ROW
BEGIN
DECLARE adj INT;

if (old.amount <> new.amount && old.accid = new.accid) then
set adj = new.amount - old.amount;

update transactions set accid_curr_accbalance = accid_curr_accbalance + adj
where accid = new.accid and `date` >= new.date;

elseif (old.accid <> new.accid) then
SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'IDs unchangeable';
end if;
END

But produces an error when updated

1442: Can't update table 'transactions' in stored function/trigger because
it is already used by statement which invoked this stored function/trigger.

I think I understood what it meant so... I want the trigger not to fire the UPDATE is fired by THIS trigger.


texts: Getting replication status for snapshot replication

texts:
Is there a way we can get stats on how the snapshot replication is working in an easier report format to check on actually how many objects like tables or sps have been replicates followed by health to check if snapshot replication is actually working as expected

Publisher is Sql 2008r2
Subscriber is sql 2012


texts: Log full of [Note] InnoDB: ft_end() after upgrade to MariaDB 10.3.10


texts: Upgrading hardware for MySQL. If we get SSDs, are CPUs likely to become a bottleneck

texts:
We are upgrading hardware for a MySQL database server (DB is around 200GB).
We're getting 4x Samsung 850 Pro SSDs and are planning to run them in a RAID10 configuration.  Also, 128GB of RAM.

The quote from our supplier includes dual 6 core Xeons (E5-2620V2 2.1G) with an option to upgrade to E5-2630V2 2.6G instead.

The price for the faster CPUs would be about $622 extra (about $311 each), which is around 6.5% of the price of the server.

Since we don't have the hardware, of course, I can't benchmark it and see whether the faster CPUs would make a difference for our database and the way we query it.  But is there some way to get an idea of whether the faster CPUs will help?  i.e. assuming the current bottleneck is I/O and we replace the disks with SSDs, is it possible/likely for the E5-2620V2 CPUs to become the bottleneck?

Thanks.


texts:
I was on mysql:8.0.15 and I updated to mysql:8.0.17

On 8.0.15 ... and (col1, col2) in (('a', 'b')) the query return results, this is what I expect
On 8.0.17 the same query with same data return 0 results.

I thought it was our application, so I downgraded back to 8.0.15 and the query is working again...

I noticed ... and (col1, col2) in (('a', 'b'),('a', 'b')) is working in 8.0.17

I tried to search mysql bug list, but I can't find any information... In the changelog I see they optimized WHERE IN in 8.0.17, but it should not break my use case.

  The optimizer now transforms a WHERE condition having NOT IN (subquery), NOT EXISTS (subquery), IN (subquery) IS NOT TRUE, or EXISTS (subquery) IS NOT TRUE internally into an antijoin, thus removing the subquery. This is similar to the existing IS NULL (Not exists) outer join optimization; see EXPLAIN Extra Information, for further information.
  In addition, the semijoin materialization can now be used for a WHERE condition having IN (subquery) IS TRUE, or EXISTS (subquery) IS TRUE, or when the IN condition belongs to a left join such as SELECT * FROM t1 LEFT JOIN t2 ON t2.x IN (SELECT * FROM t3). See Optimizing IN and EXISTS Subquery predicates with Semijoin Transformations.
  Also as a result of this work, MySQL is now able to simplify a co

It seems too big to be a bug in mysql. Am I missing something ?

Here is the full query

select `t1`.`user_id`    as `user_id`,
       `t1`.`created_at` as `created_at`,
       `t1`.`updated_at` as `updated_at`,
       `t3`.`node_id`    as `t3node_id`,
       `t3`.`node_type`  as `t3node_type`,
       `t2`.`node_id`    as `t2node_id`,
       `t2`.`node_type`  as `t2node_type`
from `table1` as `t1`
         inner join `table2` as `t2` on `t1`.`set_id` = `t2`.`set_id` and `t1`.`set_type` = `t2`.`set_type`
         left join `table2` as `t3`
                   on `t2`.`node_type` = 3 and `t3`.`set_type` = 5 and `t3`.`set_id` = `t2`.`node_id`
         left join `table3` as `ts` on COALESCE(t3.node_type, t2.node_type) = `ts`.`cache_node_type` and
                                                 COALESCE(t3.node_id, t2.node_id) = `ts`.`cache_node_id`
where `t1`.`tenant_id` = '825773475713154048'
and (`t1`.`user_id`, `t2`.`node_id`) in ( ('825773502556700160', '825773839074099712'))

On 8.0.17 I get 0 results. On 8.0.15 it works.

If, on 8.0.17, I replace the last line by and (t1.user_id, t2.node_id) in ( ('825773502556700160', '825773839074099712'), ('825773502556700160', '825773839074099712')) (notice it's the same tuple, two times) I get results.
It works with this and (t1.user_id, t2.node_id) = ('825773502556700160', '825773839074099712')
I tried with a simple query SELECT * FROM test.test as t1 WHERE (t1.col1,t1.col2) IN (('a','b')); it works on 8.0.17.


texts: I am trying to find a way to delete backups being taken by Ola's native SQL backups. I am trying to keep only 14 days of transaction logs, 1 month of diffs, and 12 months of full weekend backups. Is there a way to do it in Ola's scripts?


texts: PostGIS ERROR: operator does not exist: postgis.geometry @ geometry

texts:
I have had PostgreSQL 9.3.1 and PostGIS 2.1.0 installed (via https://github.com/PostgresApp/PostgresApp/releases/tag/9.3.1.1-RC1) and working on my laptop for some time, and have been doing Ruby (2.0.0) on Rails (4.0.0) development with them.

I was able to successfully run typical operations, for example using @ to determine whether a point (postgis.geometry(Point,3785)) is in a bounding box. Most recently I took the exact points and query I was successfully running in dev and made an equivalent feature test. This is when I began noticing issues...

Whereas a point on the dev side was stored like this (SRID 3785):

POINT (-13740247.269752283 6126532.67447704)

...the test side saw it like this:

POINT (-8238307.241152702 4970299.628630948)

I wanted to post a Stack Overflow question about the issue, so I wanted the exact DB version. Whereas select postgis_full_version(); previously worked (it's still in my Terminal scrollback history), now it was returning ERROR:  function postgis_full_version() does not exist. At that point I didn't think to check the search_path, and instead I followed https://stackoverflow.com/a/8459682 -- first I tried CREATE EXTENSION postgis; but got ERROR:  extension "postgis" already exists -- and then I executed:

psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/postgis.sql
psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/spatial_ref_sys.sql
psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/postgis_comments.sql

Unfortunately those did not do the trick, and worse, now when I run my usual query I am seeing:

PG::UndefinedFunction: ERROR: operator does not exist: postgis.geometry @ geometry

How could things go so wrong? I have not yet reset my computer. I only tried restarting PostgreSQL so far. Any ideas? Thank you for reading.


texts: Assigning empty string to INT NULL field in MySQL

texts:
Point me a reference what happens if empty string ('') is assigned to a INT NULL field.

Does it become NULL or 0?

Do all versions (starting from 5) of MySQL do the same?


texts: Facing Error in "Execute Sql task" component (The type of the value (DBNull) being assigned to variable)

texts:
Hope you are doing well
I have a table as you can see below :

I have a variable "ExcelPath"  in "SSIS" in which I keep "FilePath" column .

Then I have "Execute SQL task" and the query I wrote in this task as you can see below

If you compare the sql query with the table you find out that I need to retrieve "DestinationTable" column where the "Filepath" column is equal to what is in my variable "ExcelPath"

the parameter mapping tab is configured like this :

and the result set tab shows I want the output into another variable called "TableName"

I think everything is fine , I have no idea why I'm getting this error :
"The type of the value (DBNull) being assigned to variable "User::TableName" differs from the current variable type (String)"

I was wondering if you could help me up with this issue.
Thanks in advance


texts: Replicating a postgres database for testing

texts: I want to replicate a live server postgres database to our dev server for testing purposes but the problem is in inbuilt postgres replication feature the slave is always read only. Is there any better way to replicate live server database to dev server, It need not to be real time but at least daily.


texts: SSRS (2008 R2 to 2014) Migration Issues Running Reports

texts:
This is my first ever StackExchange question, so sorry if I something is off...I searched and found a rather helpful article that got me this far, but nothing I found addressed what I am currently seeing.

As the title states, I am having issues running reports on SSRS 2014 after having migrated from SSRS 2008 R2. A bit of background information:

SQL Server 2008 R2 was hosted on a Win 2008 R2 server
New Server (SQL Server 2014) is Win Server 2012 (not a big fan of metro interface)
I added the databases with the backup/restore method going from 2008 to 2014
All backups/restores completed smoothly.
I can view all data, and SP from the other databases and the ReportServer DB
I imported the key after backing it up form SQL Report Services Configuration manager which all completed successfully.

So the issue lies in the fact that when I connect to the SSRS home in IE, I can browse and manage everything, but nothing is able to run. I am getting to the point where I can enter parameters, but get the message:

"Query execution failed for dataset 'DataSet1'.(rsErrorExecutingCommand) Could not find stored procedure 'storeprocedurename'"

After having opened them in Report Builder on the server, it appears that when I right-click the data set to check the properties, I cannot actually login to the data source for the data set (data sets are embedded in reports). I tried creating a new data source, but logging into this proved an issue as well when it came time to add a data set - though it was able to successfully test connection to the new data source. So my guess is that the data source is not connecting to the new sql server database? At a loss and grasping for straws.

Another odd thing is that the sys accounts are not enabled for the databases I imported, nor am I given the option to enable it.

If anyone has any insight as to what I may have messed up, or what I need to modify to correct this, I would be incredibly appreciative. Thanks in advance for any help.


texts: Reducing deadlocks with update query hint or other method

texts:
I'm getting 20-30 deadlocks per day on the same table. (2 mil rows)
Profiler DeadlockGraphs show KEY LOCK and PAGE LOCK (See attched jpg)
Total datatype size per row = 114 bytes

We have multiple departments that run a process that takes orders from our PreOrders table,
checks the rows and inserts them into the actual orders table.
This very import process is VB6. So to illistrate I simplified it in TSQL (Below).

The first SELECT statement is the deadlock victim and the end UPDATE statement is the winner. (Different SPIDs)
There are 4 different departments, each running this process every 30 secs.

After reading several articles, I was thinking my easiest option would be to add WITH (ROWLOCK) to the update statement.
I am able to recreate this entire process in TSQL, except for the automatic printing of invalid rows.

What other options do I have?
This'll be the first time I've had to use a query hint and am a bit reluctant as my knownledge on hints is (still) limited.

--Loop through headers
-- THIS SELECT STATEMENT IS ALWAYS THE DEADLOCK VICTIM
SELECT  PreOrderID, <Other columns....>
FROM    PreOrders PO -- 2 mil rows
        INNER JOIN Customers C -- 50 K rows
            ON PO.CustomerNr = C.CustomerNr
WHERE   Processed = 0
        AND Department = 4 -- This is also the account number related to the Deadlock process

BEGIN
    -- < 100 orders

    -- Loop through rows
    SELECT  ProductNr, Amount, Price, <Other columns....>
    FROM    PreOrders
    WHERE   PreOrderID = @PreOrderID

    BEGIN
            -- AVG 9 rows per order

            -- Get data to validate the order row with
            SELECT  Availibilty, Price, isActive <Other columns....>
            FROM    Products P -- 100 K rows
                    LEFT JOIN ProductLocations PL -- 35 K rows
                        ON P.ProductNr = PL.ProductNr
            WHERE   P.ProductNr = @ProductNr
            -- Validate the order rows against the actual product data
            -- Bad rows get emailed and insert with a counter row (Cancels the row order)
            -- Good rows get inserted into actual Orders table
    END

    -- Generate the new order number
    -- Save the head and rows into the acutal order tables
    -- Update the preOrder as having been processed

    -- THIS UPDATE STATEMENT IS ALWAYS THE DEADLOCK WINNER
    UPDATE  PreOrders -- !!*** WITH (ROWLOCK)
    SET     Ordernumbers= @OrderNumber, -- Newly generated number)
            Processed   = 1
    WHERE   PreOrderID  = @PreOrderID

BEGIN

EDIT: Added screenshots


texts: Is it possible to enlist a TSX to an MSX in a different domain?

texts: In Domain1, I have an MSX with 75+ TSXes registered. Now I've been given some SQL Server instances in another domain. Is it possible to enlist Domain2 instances to an MSX in Domain1 ?


texts: Maintenance Plan produces BAK files I cannot use

texts:
I have a maintenance plan that does the following:

Check Database Integrity
Shrink Database
Reorganize Index
Rebuild Index
Update Statistics
Clean Up History
Backup Database
Maintenance Cleanup Task

It runs on schedule once a day and I have now tried to re-install a BAK on my local machine and I get the following error message:

  The Log or differential backup cannot be restored because no files are
  ready to rollforward.

I barely understand the concept of how Transactional Logs are handle by SQL. In my mind, when a transaction runs and completes, not sure what "trace" leaves on the log file but in min mind that should just be a trace, not operational data that the MDF depends on.

From what I read ...it seems I'm wrong :)

But going back to my initial problem, I need to create a daily LOG that is USABLE.

I thought using a Maintenance Plan was the way.

Obviously not !!!

=========
EDITED:
I removed the unnecessary blocks ...but does that solve my original problem?


texts: Org chart CTE with exact index but plan shows expensive sort node

texts:
I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it.  Names are changed, but it's the same idea.  I was thinking of adding a hierarchyid column to the mix; then I could index that and hopefully make some of the queries faster.
This query is modeled on the msdocs sample on hierarchy id.  Before I ran it, I put indexes on (employeeid), (managerid, include employeeid), and (officeid, managerid, employeeid) hoping to make it as fast as possible.  But the query took over 5 hours to run.
I looked at the plan, and the (officeid, managerid, employeeid) index is used for both parts of the cte.
What I don't understand is that the ROW_NUMBER() windowing function also appears to have added a SORT node, which ss says is taking 94% of the query time.  It's already using an index that is pre-ordered in the same way, so why is there a SORT node taking up 94% of the time?

Thanks
WITH paths(path, duns, extended_gu, extended_parent)
AS (  -- This section provides the value for the root of the hierarchy
    SELECT hierarchyid::GetRoot() AS path, employeeid, officeid, managerid
    FROM OrgChart where officeid = 0

    -- This section provides values for all nodes except the root
    UNION ALL
    SELECT CAST(p.path.ToString() + CAST(ROW_NUMBER() OVER (PARTITION BY RD.officeid, RD.managerid
    ORDER BY RD.officeid, RD.managerid) AS varchar(30)) + '/' AS hierarchyid),
    RD.employeeid, RD.managerid, Rd.officeid
    FROM OrgChart rd
    JOIN paths AS p ON rd.managerid = P.employeeid
)
UPDATE rd set tree=P.Path
FROM Paths P
INNER JOIN  OrgChart rd on rd.duns = P.Duns

EDIT: Schema, added in response to comment below.  I don't see a "Paste The Plan" as a feature in the editor; where is that?  Rather than null on the roots, those ids are 0
CREATE TABLE [dbo].[OrgChart](
    [employeeid] [int] NOT NULL,
    [managerid] [int] NOT NULL,
    [officeid] [int] NOT NULL,
    ...
    [tree] [hierarchyid] NULL
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [uixOrgChart_employeeids] ON [dbo].[OrgChart]
(
    [employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [uixOrgChart_officemgremployee] ON [dbo].[OrgChart]
(
    [officeid] ASC,
    [managerid] ASC,
    [employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [uixOrgChart_mgremp] ON [dbo].[OrgChart]
(
    [managerid] ASC
)
INCLUDE([employeeid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


texts: Total number of matches played by player

texts:
Udacity database SQL free course final project:-

I made 3 tables:

Players(pid, name, contact)
Matches(mid, pid_a, pid_b, won)
Matches_won(mid,pid)

I want to make a query so i can also be able to count the total matches played by player id.

I am able to make table with id, name, (number of matches won) with this query..

SELECT    players.pid,
          players.name,
          count(m_won.pid) as won
FROM      players
LEFT JOIN m_won
ON        players.pid = m_won.pid
GROUP BY  players.pid
ORDER BY  players.pid;

I want to show playerid, playername, total wins and total matches played in 1 table. help me.

PostgreSQL code so you can also test yourself.

CREATE TABLE players(
  pid serial PRIMARY KEY, -- pid = PlayerID
  name text,
  contact bigint
);

CREATE TABLE matches(
  mid serial PRIMARY KEY, -- mid = MatchID
  pid_a int REFERENCES players (pid),
  pid_b int REFERENCES players (pid),
  winner int
);

-- In this table,
-- I am recording matchID and playerID
-- So i can keep track of players who won matches by matchID

-- m_won = matches_won
CREATE TABLE m_won(
  mid int REFERENCES matches (mid),
  pid int REFERENCES players (pid)
);

--RAW DATA TO INSERT INTO TABLES:-(dont change the values)
INSERT INTO players (name, contact) VALUES ('Dishank', 9803050043);
INSERT INTO players (name, contact) VALUES ('Farhan', 8943789678);
INSERT INTO players (name, contact) VALUES ('Rajat', 9742434546);
INSERT INTO players (name, contact) VALUES ('Ajay', 7865875467);

INSERT INTO matches (pid_a, pid_b, winner) VALUES (1, 2, 2);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (1, 3, 3);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (1, 4, 4);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (2, 3, 2);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (2, 4, 4);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (3, 4, 3);

INSERT INTO m_won (mid, pid) VALUES (1, 2);
INSERT INTO m_won (mid, pid) VALUES (2, 3);
INSERT INTO m_won (mid, pid) VALUES (3, 4);
INSERT INTO m_won (mid, pid) VALUES (4, 2);
INSERT INTO m_won (mid, pid) VALUES (5, 4);
INSERT INTO m_won (mid, pid) VALUES (6, 3);

UPDATE_ UPDATE UPDATE UPDATE UPDATE UPDATE _UPDATE

I solved the answer 50%. (Thanks to @EVAN).

This query is counting total matches played by player.

SELECT p.player AS player,
       p.name AS name,
       count(*) AS total_played
FROM (
SELECT matches.pid_a AS player,
       players.name AS name
FROM   players
JOIN   matches
ON     players.pid=matches.pid_a
UNION ALL
SELECT matches.pid_b AS player,
       players.name AS name
FROM   players
JOIN   matches
ON     players.pid=matches.pid_b
) AS p
GROUP BY player, name
ORDER BY player;

And this query is showing total matches won by player

SELECT    players.pid AS id,
          players.name AS name,
          count(*) AS won
FROM      players
LEFT JOIN m_won
ON        players.pid = m_won.pid
GROUP BY  id
ORDER BY  id;

Now how should i combine them? so i get a table with coulums
"ID","Name","Total_Matches_Won","Total_Matches_played"

UPDATE_ UPDATE UPDATE UPDATE UPDATE UPDATE _UPDATE

Solved 100%(i am getting what i want to achieve), but still my code might be not reliable or can be further improved so keep the discussion active.

SELECT    C2.id, C2.name, C2.total_won, C1.total_played
FROM
(SELECT   p.id AS id,
          p.name AS name,
          count(*) AS total_played
FROM (
SELECT    matches.pid_a AS id,
          players.name AS name
FROM      players
JOIN      matches
ON        players.pid=matches.pid_a
UNION ALL
SELECT    matches.pid_b AS id,
          players.name AS name
FROM      players
JOIN      matches
ON        players.pid=matches.pid_b
) AS p
GROUP BY  id, name
ORDER BY  id
) AS C1,
(SELECT   players.pid AS id,
          players.name AS name,
          count(*) AS total_won
FROM      players
LEFT JOIN m_won
ON        players.pid = m_won.pid
GROUP BY  players.pid
) AS C2
GROUP BY  C2.id, C2.name, C2.total_won, C1.total_played
ORDER BY  C2.id;


texts:
My applications rely on PostgreSQL functions to carry out operations on the database. I'm having trouble figuring out a good practice to make partial updates where only some fields are provided.

Let's take a posts table as an example:

+-----------------------------------+
|             post table            |
+-----------------------------------+
| post_id | SERIAL PRIMARY KEY      |
| title   | VARCHAR(100)            |
| content | TEXT                    |
+-----------------------------------+

The application can update the data when one or any combination of fields change. For example, when only title changes, do not update content. Or when only content changes, do not update title. Or when they both change, both of them have to be updated.

This is a simple example, but it applies to all tables with many more columns, etc.

There are some ways I can think of:

I could make a function for every column: post_update_title(changed_title, post_id), post_update_content(changed_content, post_id). Each of them only change the specific column they are responsible for. But this approach gets messy really quickly when there are tables with many columns.
Create a general function that takes in the column name, changed value and post ID: post_update_column(column_name, changed_value, post_id). This approach makes me share information specific to the database with my applications. This is something I don't want.
Create a general function for updating and set a default NULL value to parameters. This way I could detect which columns have to be changed. But this introduces a problem when there are columns that can be set NULL themselves. Then I couldn't say if the column was not updated or was explicitly set NULL.
Call UPDATE queries directly from inside the application. This approach I dislike the most: I don't want to couple the application with the DB this way.

Has anyone faced something similar? How did you solve it?


texts: JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)

texts:
I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas.

Basically, what I want to do seems like a UNION where records are "collapsed" in a sense to remove NULL values (where say, the ID and DATE are the same). I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to produce the desired result. It seems like maybe MSSQL's MERGE is maybe what I'm looking for? It seems like that operation is meant for table creation, however.

My end goal is basically to create a view from the result. All I really need is the SELECT statement that allows me to do further manipulation.

To help visualize the problem, let's say I have the below tables:

+----+--------+-------------+---------------+
| ID | Date   | FloorRating | CeilingRating |
+----+--------+-------------+---------------+
| 1A | 1/1/18 | 9           | 6             |
+----+--------+-------------+---------------+
| 1A | 1/1/19 | 9           | 7             |
+----+--------+-------------+---------------+
| 3D | 2/2/19 | 5           | 8             |
+----+--------+-------------+---------------+
| 3D | 3/3/19 | 4           | 9             |
+----+--------+-------------+---------------+

+----+--------+--------------+-----------+
| ID | Date   | WindowRating | FanRating |
+----+--------+--------------+-----------+
| 1A | 1/1/18 | 4            | 7         |
+----+--------+--------------+-----------+
| 1A | 2/2/19 | 3            | 7         |
+----+--------+--------------+-----------+
| 3D | 2/2/19 | 9            | 4         |
+----+--------+--------------+-----------+
| 3D | 8/8/19 | 1            | 3         |
+----+--------+--------------+-----------+

What I want to end up with is something like this, where all possible ID-Date combinations are accounted for and field values are filled in where possible:

+----+--------+-------------+---------------+--------------+-----------+
| ID | Date   | FloorRating | CeilingRating | WindowRating | FanRating |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 1/1/18 | 9           | 6             | 4            | 7         |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 1/1/19 | 9           | 7             |              |           |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 2/2/19 |             |               | 3            | 7         |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 2/2/19 | 5           | 8             | 9            | 4         |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 3/3/19 | 4           | 9             |              |           |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 8/8/19 |             |               | 1            | 3         |
+----+--------+-------------+---------------+--------------+-----------+

I appreciate any help or tips you can provide!


texts: Database Log File Size Growth So Huge Unexpectedly

texts: My database has two log files. Their sizes are growing very fast.  My backup sizes are becoming massive. I know that when I take full backup of my database, the log files are emptied but it is not. I can't control it.How can I control log growth?


texts:
I have a MYSQL server on a linux server. I am using AES encryption functions to store data in the DB server. Commands are like this ones:

INSERT into userc (name, town) VALUES ('john',AES_ENCRYPT('nebraska', 'usa2010'));
SELECT CAST(AES_DECRYPT(town, 'usa2010') AS CHAR(50)) town_decrypt from userc;

My concern on this kind of encryption is that everything needed to access my data is travelling in clear, so a sniffer or a Debug level log are capable of capturing everything.

Is there a way of not sending the key in the command, but having it stored on a file (/home/user/key.txt) and so calling the encryption in a way similar to this:

INSERT into userc (name, town) VALUES ('john',AES_ENCRYPT('nebraska', key1));

Where key1 is the reference to the file where the key is stored?


texts:
Trying to update Packet.MSGState to P where if not R or D and it is Y in table Packet.MSGState

UPDATE Packet SET Packet.MSGState = 'P'
Where (MSGState <> 'R' and  MSGState <> 'D') and
Exists
(Select  MSGTO.Selected
From MSGTO
Where MSGTO.MSGTO = Packet.MSGTO and  MSGTO.Selected = 'Y')

Thanks Kevin


texts: How to get Super Priviledge for a database on an online server

texts:
Can someone please look at this MySQL statement and suggest to me what to do, for each time I run the code below, I get the following error:

  1227 - Access denied; you need the SUPER privilege for this operation

CREATE
   ALGORITHM=UNDEFINED
   DEFINER=`bbamas_cat`@`localhost`
   SQL SECURITY DEFINER
VIEW `pay_rep_view`  AS
select
   `pay`.`id` AS `id`,`pay`.`pay_date` AS `pay_date`,`pay`.`pay_amount` AS `pay_amount`
   ,`pol`.`pol_number` AS `pol_number`,`pol`.`pol_hold` AS `pol_hold`
   ,`pol`.`pre_amnt` AS `pre_amnt`,`pol`.`com_date` AS `com_date`
   ,`in_comp`.`id` AS `in_comp_id`,`in_comp`.`in_comp` AS `in_comp`
   ,`in_brok`.`id` AS `brok_id`,`in_brok`.`in_brok` AS `in_brok`
   ,`pay`.`pay_type` AS `pay_type`
from ((
  (`pay` join `pol` on((`pay`.`pol_id` = `pol`.`id`)))
  left join `in_comp` on((`pol`.`in_comp_id` = `in_comp`.`id`)))
  left join `in_brok` on((`pol`.`brok_id` = `in_brok`.`id`)))


texts: How do I manage two versions of a stored procedure at the same time?

texts:
Here is the situation: There are some stored procedures (3 - 5 no's) already in production. We made some changes for a fix. It also involved changes to the code that is using these stored procedures.
I am looking for a strategy that can help us achieve this:
Let the old code in production use the old version of the stored procedures. So when I deploy the newer version of the stored procedures, and the old code still running in production (no down time) it should use the old version of the stored procedure so the application does not break.
Once I deploy the new code, it should start using the newer version of the stored procedures.


texts: Incremental level 1 backup with no Incremental level 0?

texts:
I have done the following steps:

Backed up database with INCREMENTAL LEVEL 0
Delete INCREMENTAL LEVEL 0 backup without telling controlfile. So deleted it by hand. And did not make crosscheck.
Backed up database with INCREMENTAL LEVEL 1(without making crosscheck).  How surprising it may be, log showed me that INCREMENTAL LEVEL 1 was successfully  done.

Note, I have no INCREMENTAL LEVEL 0 backup, but controlfile doesn't know it. I have just INCREMENTAL LEVEL 1.

I tried to restore database with this backup but of course couldn't. It was telling me that no backup of datafile 1-5 found...

If controlfile had known about EXPIRED backups , then INCREMENTAL LEVEL 1 backup first would do INCREMENTAL LEVEL 0 and after-that INCREMENTAL LEVEL 1.

How to avoid this situation. Do I have to use chrosscheck in my backup script? Or set recovery window in RMAN?

I have Oracle 11g database.
Backup is taken by Netbackup Server. So Netbackup deletes backups itself by its retention.
RMAN retention policy is configured to REDUNDANCY 2.

Please, Advice me.


texts: MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

texts:
How to effectively add column id, name exception for trigger:

CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW()

so that date=NOW() will be affected on updating any column except id and name?


texts: Oracle 11g - stuck while creating tables

texts:
I have EC2 instance with 60GB Ram, 8 CPU and over 50GB space.
Tried to create 75K empty tables with sqlplus -s /  as sysdba @script.sql and it works fine up to 48K tables (they are created fine with all proper columns) but not sure why it hangs at 48K tables since memory is not filled up (less than 50%) and space is hard drive space is not filled either.  Tried 3 times on 3 different EC2 instances and it always hangs around 47K-48K tables (but not at the same number of tables each time).

Set actions to be saved to the logs: SPOOL /home/oracle/logs/sqlplus.log but in logs is just showing how table is created successfully but no errors at all and nothing different towards the end of logs.

How to understand why is this happening and how to fix it?


texts: Putting SQL Server databases into individual folders

texts:
We have a Dev server where we are trying out some automated partitioning scripts.

The folder is getting quite large in terms of number of files.

Is it possible to automatically have SQL Server use a sub folder for all files pertaining to each one of the databases using the database name as a sub-folder name?


texts:
I am new to working with databases and am unaware of their capacity in general.
To be specific, I want to know much much time would a particular mysql query take. I tried searching on the internet but nowhere was I able to find an estimate neither a way to find an estimate.

So my question is if there are 900k rows containing id (Numbers from 100 000 to 900 000) in ascending order in a table. How long would it take to search a row with a particular id?

I figured that if I ask someone who has worked with databases at this scale the estimation would be known to that person.


texts: Why RECOMPILE doesn't fix parameter sniffing, but local variables do?

texts:
A developer was asking me about why his procedure wasn't running, but the query was ok.

When I run the query, it was fast.But running the procedure, it was executing forever.

What I did was to add at the end of the query:

OPTION(RECOMPILE)

It doesn't help.

The procedure was like this (the slow one):

CREATE procedure [dbo].[pr_ProcedureName]
    @CodOnda int
AS
BEGIN
  set nocount on
  ...

Then I did this:

CREATE procedure [dbo].[pr_ProcedureName]
    @CodOnda int
AS
BEGIN
   set nocount on

   declare @localCodOnda int
   set @localCodOnda = @CodOnda
   ...

And BAM! Local variables. The procedure was faster than ever. I know this is something about parameter sniffing. But why the recompile didn't help?

I really would like to have the old query plan, but I don't have.

And the problem wasn't about locking or blocking. I was tracking with sp_WhoIsActive and some queries that I found in some answers here (thanks to Aaron Bertrand and Brent Ozar for them). I am using SQL Server 2008 R2.


texts: Postgres serializable snapshot isolation for jsonb

texts:
I'm trying to acquire a serializable lock on a JSONB column, but it looks like by default JSONB supports only row level lock on transactions.

Basically I want to store JSON like:

{a:"v1", b:"v2"}

Here I want to acquire lock on my json, so that when two users edit a/b concurrently for me database shouldn't block transactions. However, if both user edit the same key concurrently, I want to stop them.

My application needs properties that need to be stored and updated that's the reason I went ahead for JSONB. Not sure are there any way to achieve serializable lock on JSONB.

The keys will be fixed. They can edit more than one key. No one can delete the JSON keys according to my design, only update them.


texts:
I have a table that looks like this (d could be the day, and h1 h2 h3 the values of the single hours of the day(assuming the day has 3 hours)):

d  h1 h2 h3
1  1  2  3
2  4  5  6
3  7  8  9

and I what an output like this (one row with all the hours):

d h value
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
3 3 9

Is there a way do this with MySQL?

Edit: Having to address every single column in the syntax should be avoided, because this is supposed to also work for a lot of columns.


texts: Where does kerberos tool need running and settings configured?

texts:
I am reading this article on how to link 2 sql server instances using kerberos: https://www.sqlshack.com/how-to-link-two-sql-server-instances-with-kerberos/
I am not able to understand which computer the settings need to be made on. Is it on both instances?


texts:
I have:

a table of Characters with a character_id column;
a join table of Chats with character_id and conversation_id columns;
and a table of Conversations with a conversation_id column.

I am trying to construct a query that finds the Conversation between two characters (sender and recipient) and ONLY those two characters (other conversations may exist with sender, recipient, and other characters involved - group conversations).

I'm using Ruby on Rails.

The query involves three steps:

Find all Conversations belonging to sender.
Select from these the ones that also belong to recipient.
Select from these the one that belongs only to sender and recipient.

I can't get the query quite right. The best I've managed is below.

Where am I going wrong?

The query below gives this error: PG::SyntaxError: ERROR:  syntax error at or near "GROUP"

SELECT Conversations.id FROM
(
    (
        (
            Conversations INNER JOIN ( SELECT conversation_id FROM Chats WHERE Chats.character_id=#{sender_id} ) AS sterling
                          ON Chats.conversation_id=Conversations.id
        )
        INNER JOIN ( SELECT conversation_id FROM Chats WHERE Chats.character_id=#{recipient_id} ) AS archer
        ON Chats.conversation_id=Conversations.id
    )
    GROUP BY conversation_id
    HAVING COUNT(Chats.conversation_id)=2
);


texts: Why isn't the ASYMKEY_ID function returning the Asymmetric Key ID?

texts:
I have created database master key. Then I have created asymmetric key:

CREATE ASYMMETRIC KEY smGK_АSymmetricKey_01
WITH ALGORITHM = RSA_512

In the documentation is said, that:

  PASSWORD = 'password'

  Specifies the password with which to encrypt the
  private key. If this clause is not present, the private key will be
  encrypted with the database master key.

So, the key should be encrypted with the database master key. Then, why the following is not working:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypass'

SELECT [Email], ASYMKEY_ID('smGK_АSymmetricKey_01'), EncryptByAsymKey(AsymKey_ID('smGK_АSymmetricKey_01'), [Email])
FROM  myTable

CLOSE MASTER KEY

and returns NULL for the second and the third column.

Also, why the EncryptByAsymKey and DeCryptByAsymKey functions works with the same key ID? I think that when asymmetric key is used, I will grant to specific users to control the public key and to be able to decrypt data and other user to control the private key and to be the only one who can read it?

If this is not the case and only one group of users will be able to encrypt - decrypt the data, then as the example here, there is no logical difference between the two type of keys (only in terms of performance).

Note:

The key is created for sure:

SELECT *
FROM sys.asymmetric_keys;

I have try to create the key using PASSWORD but my code is still not working.


texts: Problem compiling view when it is referencing a table in an other view: insufficient privileges

texts:
Oracle 11g R2
Logged on: SYS / AS SYSDBA

When I try to compile or create a view that is referencing local schema tables. It works fine.

Problem does occur when I try to compile the same view referencing a table in another schema like schema.table in my query.

Oracle throws the exception ORA-01031: insufficient privileges.

Remember I am using SYS account (sysdba).


texts: Where can I found a example database for apply data quality improvement?

texts: I am doing a little database improvement project, but I need a large database example.


texts: How to make SSAS Commit Write take less time

texts:
We're using SQL Server Analysis Server 2012 (Enterprise) and have a process to update the cube during the day. This process is running pretty well until it gets to the final part of processing where it swaps the old cube out and puts the new cube in place.

If we run a trace on SQL Profiler monitoring the locks we see this happen:

<LockList>
    <Lock>
        <Type>Commit Write</Type>
        <LockStatus>Acquired</LockStatus>
        <Object>
            <ServerID>MSOLAP$LIVE</ServerID>
        </Object>
        <ObjectID>51B3ABD7-5FAA-4D1E-89B4-F35E83042762</ObjectID>
    </Lock>
    <Lock>
        <Type>Commit Write</Type>
        <LockStatus>Acquired</LockStatus>
        <Object>
            <DatabaseID>DatabaseID</DatabaseID>
        </Object>
        <ObjectID>370037F0-FEA6-42FF-B6E6-2DFE8882E364</ObjectID>
    </Lock>
</LockList>

25 seconds later, we see:

<LockList>
    <Lock>
        <Type>Write</Type>
        <LockStatus>Released</LockStatus>
        <Object>
            <DatabaseID>DatabaseID</DatabaseID>
            <DimensionID>DimensionID1</DimensionID>
        </Object>
        <ObjectID>87B8A2FD-EC63-4214-A490-A8A07EC6DD15</ObjectID>
        <Duration>603062</Duration>
    </Lock>
    .../*SNIP: all of our dimension names */...
    <Lock>
        <Type>Commit Write</Type>
        <LockStatus>Released</LockStatus>
        <Object>
            <ServerID>MSOLAP$LIVE</ServerID>
        </Object>
        <ObjectID>51B3ABD7-5FAA-4D1E-89B4-F35E83042762</ObjectID>
        <Duration>25846</Duration>
    </Lock>
    <Lock>
        <Type>Commit Write</Type>
        <LockStatus>Released</LockStatus>
        <Object>
            <DatabaseID>DatabaseID</DatabaseID>
        </Object>
        <ObjectID>370037F0-FEA6-42FF-B6E6-2DFE8882E364</ObjectID>
        <Duration>25846</Duration>
    </Lock>
</LockList>

During this time we're seeing very little CPU or IO usage through process monitor. Also while this lock is in place, all of our users who are trying to query the cube are blocked.

We have only noticed this problem since moving to SQL 2012 Enterprise (we came from SQL 2005 Standard about 3 weeks ago), although it could have been pre-existing. No other changes have been made to the process.

From what I understand from sites like these 2 (http://gvkamath.wordpress.com/2011/11/02/committimeout-and-forcecommittimeout-in-ssas/), when SSAS receives the XMLA to process, it creates a new cube without blocking the existing one, and then as the last step it "swaps" the new cube over the old cube.

Currently, it's the only process that is doing updates on the cube and is running in Transaction mode so there should be no locking on the master.vmp file.

We have tried shrinking the FORCECOMMITTIMEOUT setting down to 5 seconds. Most of our read queries though are sub 1 second so they are not killed anyway. The problem we have is the queue of these short queries that line up behind the 23530 second lock that the process task has.

What else can we try or investigate to reduce the time of the Commit Write locks on the server?


texts:
Is it possible to restrict sensitive information from users having sysdba role? Does Data redaction help us in achieving this?

I know we can restrict particular schemas to prevent from viewing from sensitive info by using redaction.. but what about sys user? Can that user still view everything?


texts: MySQL: Why is auto_increment limited to just primary keys?

texts:
I know MySQL limits auto_increment columns to primary keys. Why is this? My first thought is that it's a performance restriction, since there probably is some counter table somewhere that must be locked in order to get this value.

Why can't I have multiple auto_increment columns in the same table?

Thanks.


texts:
Can someone point me at a good resource to get a crash course in load testing?

I have a SQL Server machine that I need to load test but I haven't been thruogh the process before. We do not have any third party software for this, or money to purchase any, so that option would be out.


texts:
I have a simple join query, but for some reason query_cache won't cache it!

SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id  WHERE approve =1 AND allow_main =1 ORDER BY FIXED DESC , DATE DESC  LIMIT 7 , 7;

without the JOIN post_plus ON ( post.id = post_plus.news_id ) it's working.

query_cache is on

| query_cache_limit              | 10485760             |
| query_cache_min_res_unit       | 4096                 |
| query_cache_size               | 536870912            |
| query_cache_strip_comments     | OFF                  |
| query_cache_type               | ON                   |
| query_cache_wlock_invalidate   | OFF                  |

Server version: 5.6.12-56 Percona Server (GPL), Release rc60.4, Revision 393

both tables are innodb with utf8 charset


texts: Best way to approach building search functionality using PHP on existing mySQL Database

texts:
I am looking for advice on how to approach an upcoming project I am due to undertake. The reason I am asking is that I'm just out of college and the company I work for is currently searching for a new lead developer which makes things a little harder on me as I've got no guidance and really need some help deciding the best way to go about this.

Basically, we have a client who for the past 5 - 6 years has been using a MySQL database to drive their website. Thought the database is not that large (1.8GB) it has many tables and it is clear that whoever developed it initially did not really put a lot of thought into the structure, thus making it very difficult to maintain and improve.

Up until now the Search functionality of the site has been handled by constructing the sql to search across the entire database with the specified keywords. The client wishes to rewrite this functionality and I am looking for the best way to do so. Would I be better writing the code from scratch and piecing it together? Or is there a library that provides an easy way to index a mysql database?

The client also has a large number of filters they wish to incorporate in the search and also wish to include auto complete functionality.

I'm literally at a loss as I've no experience whatsoever with these sort of things and would really appreciate any advice on how more experienced php developers would approach this issue?

I have looked into Elastic Search and elastica but I am having trouble installing it into the project and even at that I am not entirely sure it is the best way to go as Elastic search is nosql....

Any advice is much appreciated?


texts: what do vertical lines refer to in database diagram view

texts:
source: https://msdn.microsoft.com/en-us/library/ms188251.aspx

Examples above

I understand one to one relationships, one to many, PK, FK and all of that, but

I get really confused about understanding what verical lines refer in a relational database setup

Could someone elaborate on the highlighted area?

What is #1 and #2 referring to in this diagram?

Is #1 referring to a one to many relationship for COUNTRY: TITLEID?
and #2 discount to STORE ID?

I'm having trouble visually interpreting the diagram


texts: Whats the right table relationship for these entities?

texts:
I want to store all the data for the following entities:

Users
Projects
Roles

A user can work in multiple projects and can have many roles, but only one role for a user should be allowed in each project, for example:
Jhon can work only as a developer in project #1 and can also work as a designer in project #2
I think the relationship should be:

Where the table User_Project has:

user_id
project_id
role_id

and the user_id,project_id combination is the primary key, and the role_id field is a foreign key to the roles table, where there is a row for each:
role_id  role
1        project manager
2        developer
3        designer
is that the right way to describe the relationship between the entities ??
At first i thought about a many to many relationship for each the users - projects, projects - roles and user - roles, but i dont think it describes the "only one role in each project for a user".


texts: When postgresql sequences get out of sync?

texts: For the third time it has happened to me that a sequence has been set to an initial value (zero or one, not sure) while in the table, there are around 1500 records. When it happens, new rows cannot be inserted from my application. So I would like to know possible causes for the sequence to be out of sync like that. Because I have no idea why this problem is occurring.


texts: deleting a table referenced by many foreign keys

texts:
I have a table that I want to drop and create again, because I want to add the IDENTITY property to a column, wich is not possible directly. The problem is that I can't drop it, because my column is being referenced by many foreign keys in other tables. So, what are the strategies adopted for droping a table referenced by many foreign keys from other tables ?

Thank you for help in advance :)


texts:
Is there a way I can set up a column in PostgreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" behavior that would like to be seen in the various projects that are writing to the DB.

INSERT INTO Person(name, token, priv, sc)
VALUES ('Frank Josephson', '**fake token**', 'user'
      , (SELECT COUNT(*) FROM Person WHERE name='Frank Josephson'));

Otherwise, it's just me auditing a bunch of excel sheets and applications to make sure this new directive goes through. Any way to set this up as a trigger without running into race conditions?


texts: How to properly align columns in SQLite?

texts:
I recently began learning with SQLite, and I am trying to output the data of a table into the Terminal (macOS). However, the result after I've set .mode column or .mode tab is not properly aligned when some columns have no data, such in the second entry.

For example, in column mode where Donna doesn't have age and place data:

name            surname             age         place
--------------  ------------------  ----------  ------------------
John  Smith  34          assistant manager
Donna  Patterson

and in tab mode:

name surname  age   place
John Smithonian     34 assistant manager
Donna Patterson

but the desirable result is:

name            surname             age         place
--------------  ------------------  ----------  ------------------
John            Smith               34          assistant manager
Donna           Patterson

Is there anything I am not aware of? All the examples in the book I study from depict all columns properly aligned.

The same thing happens with tab mode.

I am working through Terminal.app on a mac with the pre-installed 3.8 version of SQLite

In later tests, I discovered that if my columns are all filled with information, it is possible to have all data aligned or not. It depends on the information. When I added a Phone Number column to one of the examples, the nice arrangement was lost.


texts: I can not navigate through the objects in sql server managemente studio - lock never goes away

texts:
Whenever I try to navigate through the objects in sql server management studio object explorer I get the error message

Lock request time out period exceeded. (Microsoft SQL Server, error:
1222).

It is impossible to list columns in tables, show code of views and procedures, etc.
It's a SQL Server 2016.
How to look for the cause of this ?
There is a lot of ETL running on the server, can that be the cause ? How to pinpoint the cause ?


texts: Is it possible to make database owner read only?

texts: The database owner can revoke certain privileges from themselves such as INSERT but is it possible to revoke further privileges such as DROP for read-only operation?


texts:
I have two tables, both with identical data types, character sets, and collation explicitly specified.

CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL

For a case-sensitive join on the CLERK field, do I need to specify the collation in the join clause as well, or does the fact that it is specified at the DDL level mean that it isn't needed in the join?

FROM
    CUSTOMER S JOIN
    CLERK C ON S.CLERK = C.CLERK COLLATE latin1_bin


texts: I'm a beginner at SQL servers, and would like to ask something to the pros as I'm not 100% sure of the answer. Is it really not possible to partition a FileTable?


texts: How to script all permissions on a schema

texts: SQL management studio allows to create scripts for all db objects, however I so far couldn't find a way to correctly script a schema or user. The permissions of a user on a schema are not included in the script that is created. Did I make something wrong or is MSFT her a bit sloppy ?


texts: Can a Selective XML index help when using query()?

texts:
Say I have the following SQL query to work with

Select
myxml.query(
'
<go>
{for $each in /This/Company/Department[1]/Role/Person
return if ($each/Shoes/F[@C="9"]="657493742")
then (<ID>{$each/Shoes/F[@C="9916"]/V}</ID>)
else ()}
</go>')
FROM mytable

Is there a way to craft a Selective XML index that would help in this situation? According to the documentation it sounds like it could help with filtering the nodes, but I haven't come across any examples of dealing with a query like this.


texts: Can we expdp using JOINS in QUERY clause

texts: I have a task to export data from database using data pump. I have successfully exported database using subquery in QUERY clause. But I want to know can we expdp using joins in QUERY clause?