texts:
Somehow, it seems that SQL*Plus (at least on Windows) is unable to locate a script with a relative path when called with @@ and when the path starts with a single or double dot.

For example, under x:\some\where I have the following directory structure:

script.sql
main-dir\main-sub-dir
              call-script.sql
              script.sql

That is: two script.sql but at different locations.

The content of script.sql just under x:\some\where is simply

prompt SCRIPT root

while the other script.sql's content is

prompt SCRIPT main-dir/main-subdir

call-script.sql reads

@@script.sql
@ script.sql

expected output

If I start SQL*Plus from x:\some\where and then do a

@main-dir/main-sub-dir/call-scripts

The output will be

SCRIPT main-dir/main-subdir
SCRIPT root

This is expected, since the single @ is supposed to search paths from where SQL*Plus was started and @@ is supposed to search paths from the containing script's directory.

unexpected output

Now, if I change call-scripts.sql so:

@@./script.sql
@ ./script.sql

the double @@ seems to change it's behaviour, in that it searches paths from where SQL*Plus was started, and the output will now be

SCRIPT root
SCRIPT root

which is not what I expected.

Is this behaviour documented somewhere, and more importantly, how do I have to change call-scripts.sql so that it calls relative paths (@@../../other-dir/other-sub-dir/script) correctly?


texts: Are there any problems with deploying an SQL Server database to a production server by taking a backup?

texts:
This is a slightly loaded question in that I have already assumed that the described scenario is wrong.

A DBA is deploying an application I have written that includes an MS SQL Server 2008 database. He has asked me to take a database backup from my development machine so he can restore it to the production server, thus deploying it. This is a greenfield deployment so there is no existing data to be migrated. I was expecting to provide a DDL script, which I have diligently tested and ensured that it contains everything required. If I execute it in SSMS, the database is created in one click.

To me, using the backup facility for deployment does not seem right, but without being an expert in SQL server I can't think of a solid reason not to do it. I would have thought, for example, that there would be some 'contamination' of the database from the development machine - perhaps the computer name, directory structure or user names stored in there somewhere. Is this the case, or is backup and restore a valid deployment technique?


texts: MYSQL primary key strategy for microservices architecture

texts:
We are currently using auto-generated primary keys and we would like to switch to an approach which is more suitable for microservices applications: either we are going to use business defined primary key (a tax code for persons) or global unique identifiers.

In the past we had performance issues when using business-defined primary keys instead of autoincrement, is it still the case? Does MySQL support strong GUID so that we could have unique uuid even across databases?
Eventually, is there a better approach we are not considering?


texts:
I noticed that simply zipping my backup files reduces the space used by 70%

My idea was to, initially, backup the databases to a local server; then, using Bacula or Cobian (I don't have a budget), compress it and then ship it to our remote SAN.

Did you face similar challenge? If so, how did you overcame it?


texts: I want to find percentage change of Open Interest for every Scrip in the table (previous day's Open Interest value being the base value) in MySQL


texts: Is it impossible to force MariaDB users to SSL?

texts:
First of all, my english is really bad, so sorry for my english mistakes.
I have a MariaDB 10.2.15. I installed SSL, and its working fine, but I cant force use SSL to users.

my.cnf:

[mysqld]
ssl
ssl-ca=/etc/mariadb/ssl/ca.pem
ssl-cert=/etc/mariadb/ssl/server-cert.pem
ssl-key=/etc/mariadb/ssl/server-key.pem
ssl-cipher = AES128+EECDH:AES128+EDH

ssl variables:

+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| have_openssl        | YES                              |
| have_ssl            | YES                              |
| ssl_ca              | /etc/mariadb/ssl/ca.pem          |
| ssl_capath          |                                  |
| ssl_cert            | /etc/mariadb/ssl/server-cert.pem |
| ssl_cipher          | AES128+EECDH:AES128+EDH          |
| ssl_crl             |                                  |
| ssl_crlpath         |                                  |
| ssl_key             | /etc/mariadb/ssl/server-key.pem  |
| version_ssl_library | OpenSSL 1.0.1e-fips 11 Feb 2013  |
+---------------------+----------------------------------+

I found this variable:

require_secure_transport

and I also found this:

  MySQL-only variable determining whether client to server connections
  need to be secure.

So, can I do anything else? Cause now, the users can connect without SSL. I feel its really unnecessary without forcing.

UPDATE for clear:
SHOW GRANTS:

GRANT ALL PRIVILEGES ON *.* TO 'denes'@'%' IDENTIFIED BY PASSWORD '*SOMETHINGPASSWORD' REQUIRE SSL WITH GRANT OPTION

And I still can login to MariaDB over TCP without SSL


texts: Is there any way to get the optimization time in PostgreSQL?

texts: I'd like to see how long it takes to optimize a query. Does PostgreSQL support this feature?


texts: VMware Socket/Core allocation for SQL Server 2016

texts:
I am requesting a 16 vCPU 256GB RAM VM (Windows 2012|Host ESX 5.5) from our infrastructure team. However, my infrastructure team would like to know how the VM should be setup. What would be the ideal socket/core setup for this VM?

16 socket x 1 core |
2 socket x 8 core  |
4 socket x 4 core

Looking at this blog post it would seem 16 socket to 1 core may be the best setup: https://blogs.vmware.com/vsphere/2013/10/does-corespersocket-affect-performance.html

Can anyone offer and guidance or thoughts?


texts: Sql Server 2012 data warehousing and different versions

texts:
With Sql Server 2012 there are 3 flagship editions: Enterprise Edition, Business Intelligence, Standard.

The full comparison between the three: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

The business intelligence edition implies that the purpose of it is for data warehousing and covers what seems to be key concerns for that:

Self-Service Business Intelligence (Alerting, Power View, PowerPivot for SharePoint Server)
Advanced Corporate BI (Tabular BI Semantic Model, Advanced Analytics and Reporting, VertiPaq™ In-Memory Engine)
Advanced Data Integration (Fuzzy Grouping and Lookup, Change Data Capture, Advanced Data Mining)
Enterprise Data Management (Data Quality Services, Master Data Services)

However the enterprise edition is the only version that has:

  Data Warehousing (ColumnStore Index, Compression, Partitioning)

What functionality does this entail that is seperated between the BI and Enterprise editions?


texts: "an agent job has not been created for this subscription"

texts:
I have set up merge replication with a publisher on my SQL Server 2008 R2 box with the distributor on this box too; in Express I have created a subscriber but the subscriber won't run because of the error:

  an agent job has not been created for this subscription

How can I solve this?


texts: SQL Server Point in Time Restore Scenario

texts:
Hopefully you can help out on this as I may be up a creek!

I am working with a database using the FULL recovery model. The database has had nightly database backups but their transaction log has never been backed up (according to SQL Server Management Studio).

If I restore from one of the old database backups, would it be possible to take a backup of the current transaction log and bring the restored database forward to a point in time prior to a terrible transaction that got pushed through?


texts: When and how would MyISAM concurrent insert = always be a problem?

texts:
I wish to set concurrent_insert to always on a MyISAM log table, so that inserts wouldn't have to wait for selects to perform when there are some holes in the table.

We're talking about 10-20 inserts per second and quite rare but yet some selects on the log that could take around 10s. I don't want my 100-200 inserts wait for these selects.

My question is quite simple actually. Taken aside the hole not-always-being-filled concern, when and how would it do any harm ?

Nota Bene: I wouldn't use DELAYED INSERT as they are of the INSERT ... ON DUPLICATE KEY UPDATE form.


texts: Concerns About Guidelines for Representing Nontransferable Relationships on an Entity Relationship Diagram

texts:
I am currently enrolled in the online Oracle Academy Database Design Course, which briefly talks about the concept of nontransferable relationships. I understand the concept behind them, but the course's vague details left me with a few of questions.

Can nontransferable relationships exist between entities which are connected with a 1:1 or M:M relationship? All of the example provided by Oracle depicted 1:M relationships.
Can the diamond used to represent the nontransferable relationship be drawn on either end of the relationship, whether they are optional or mandatory? I believe they can only appear on the mandatory end of the relationship, but I want to be sure.
Similarly, can the diamond used to represent the nontransferable relationship potentially appear on BOTH ends of the relationship?

The following are a couple of examples provided by the Oracle Academy:


texts: Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?


texts: Can't get my SQL query to filter by dates like I want

texts:
I have a query where I want to exclude rows if a date column has any one of a set of dates.

I am having trouble figuring how to return the correct result sets without filtering one month at a time.

My initial attempts assumed the operator '!=' would be able to specify multiple variables, but I might be mistaken.

How do I filter the results in such a way that removes all the specified dates I do not want?

SELECT * INTO IPS_TransactionDataAll2
FROM
[IPS_TransactionDataAll1]
where Format([Start Date], 'MM/yyyy') != ('07/2016''08/2016''09/2016''10/2016''11/2016''10/2016''01/2017''02/2017''03/2017''04/2017''05/2017''06/2017''07/2017')

Pseudo-code:

Select into TableB
from TableA
Where dates not in the months 7-11/2016 and 1-7/2017


texts:
I have designed a database and came to the point of actually deciding which storage engine is better for my case.

I estimate that during the first few years I would have around 100,000 - 500,000 users. Some of the users can also have multiple posts, comments, etc. The number of users can go up to maybe a max of 10,000,000. Well I am just estimating.

I am planning to use a BIGINT for my user_id or in a certain situation I may have to go for a different route where I would store a user_id as a string like '000123456789'. I am not really decided about that.

My CRUD operation estimates are like this:
Create 15%
Read 60%
Update 20%
Delete 5%

So my focus is on Read and the Update.


texts: Postgresql query is very slow for my big table

texts:
My database version is postgresql 9.5.

create table if not exists request_log
(
    id               bigserial not null constraint app_requests_pkey  primary key,
    request_date     timestamp not null,
    ip               varchar(50),
    start_time       timestamp,
    application_name varchar(200),
    request_path     text,
    display_url      text,
    username         varchar(50)
);

I have a table that icludes incoming http request informations. The id column is primary key and index. The table has no relation.

So I have 72320081 rows in this table. And when I run the count query to get count of table, select count(id) from request_log; query takes 3-5 minutes.

The explain(analyze, buffers, format text) result for this request is:

Aggregate  (cost=3447214.71..3447214.72 rows=1 width=0) (actual time=135575.947..135575.947 rows=1 loops=1)
  Buffers: shared hit=96 read=2551303
  ->  Seq Scan on request_log  (cost=0.00..3268051.57 rows=71665257 width=0) (actual time=2.517..129032.408 rows=72320081 loops=1)
        Buffers: shared hit=96 read=2551303
Planning time: 0.067 ms
Execution time: 135575.988 ms

This is very bad performance for me. I could not get reports from table from web applications because of performance.

My server hardware sources are:

OS: Linux ubuntu server 16, On Vmware
4 core cpu
Mem 6Gb
HDD 120 Gb

I run the queries at nights, that there are no users on database, but slow. How can solve this problem?


texts: Crazy automatic float rounding in MySQL 5.7

texts:
My exact MySQL version is 5.7.29-0ubuntu0.18.04.1.
And I'm beginning to think that I'm getting crazy...

Just try these queries:

CREATE TABLE `float_bug` (
  `v` FLOAT NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO float_bug VALUES(92160596);

SELECT * FROM float_bug;

Returns 92160600.

Then I tried these:

SELECT * FROM float_bug WHERE v = 92160596;
SELECT * FROM float_bug WHERE v BETWEEN 92160596 AND 92160600;

Both return nothing!

However this

SELECT * FROM float_bug WHERE v = 92160592;

Returns 92160600.

I'm ready to throw my computer into the wall, but unfortunately that won't help because I have exactly the same behaviour on my remote server and I can't throw it into the wall physically.

Any ideas what is going on here?


texts: How to Import an Oracle dump version 5.1 into 12c?

texts:
I have .dmp files exported as version 5.1 and I need to import them on an Oracle 12c. I have no access to the original database to re-export setting the VERSION parameter.

This is the error I get:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/media/sf_Data
/DUMP_MS/mydumfile.dmp"

What can I do?


texts:
We use an external HSM device to create and store keys and use it for TDE.
Our auditors are asking questions about where the keys are store.  So obviously the key is stored in the external HSM device.

In this link, under step 5,

USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER [EKM_Prov]
WITH ALGORITHM = RSA_512,
PROVIDER_KEY_NAME = 'SQL_Server_Key' ;
GO

Does this create a copy of the key in master DB also?
In case of TDE, how often does SQL Server has to contact the EKM device?  Does it have to contact the ekm device veytime while reading
data file from disk?


texts:
I have these columns and their datatypes,
Could someone give me any advice whether they are right, and if not, suggest what is more appropriate?

Thanks


texts: MySQL: 'user'@'hostname' vs 'user'@'IPaddress'

texts:
I needed an user to be able to log in on our MySQL server 5.6 from machine foobarhost, IP 10.11.12.13.

Therefore I created a user 'joe'@'foobarhost' but he was unable to log in.  I had to create an user 'joe'@'10.11.12.13' and this time worked fine.

My question: how does MySQL handle hostnames vs IP addresses concerning users?

MySQL sorts the user table before checking for a match, using IP addresses and hostnames as the most-specific values to match to.  However, the docs do not specify what takes precedence - hostname or IP.  Does MySQL attempt a hostname resolution and, if it fails, performs a check on the IP?

What is the recommended usage for the user table: specify only IP addresses, hostnames, or both (as I did in the example above)?


texts: Count violated unique constraint caused by INSERT statement

texts:
I would like to add rows from table2 into table1 and see how many conflicts of the type "duplicate key value violates unique constraint" result from this in PostgreSQL. pkey is a primary key, so there a unique constraint on it. Unfortunately and apparently, conflict_action does not permit incrementing a counter.

INSERT INTO table1 (pkey, col1, col2)
  SELECT pkey, col1, col2 FROM table2
  ON CONFLICT ...;

So is there another (elegant) way for tackling this either by calculating or retrieving that count?


texts: How to Convert List of values datatype in oracle to equivalent or valid datatype in sql server?

texts:
I want to recreate the table below in  SQL server I have tried using SSIS but it does not know how to handle the lOV Data type how can I recreate this table in SQL server and migrate the data to it.

create Table Listing
(
 NAME                    VARCHAR2(30),
 VALUES                  NUMBER,
 LIST_OF_VALUES          LOV,
 id                      VARCHAR2(30)
);

I am new at trying to converting this type of data or data type, in general, your help would be appreciated.


texts: mysql workbench - Couldn't find option to remove DROP TABLE IF EXIST

texts:
Recently I've been looking into export options to find an option to remove:

DROP TABLE IF EXISTS and replace it with CREATE TABLE IF NOT EXISTS.

I've seen on other MySQL clients like HeidiSQL or sequelPro (for MAC) where you can tick a option to include drop table or not, but couldn't find any related thing in the MySQL Workbench.

It's very dangerous as usually when exporting and importing to exchange data with other tables, it simply DROPS tables when they exist rather then just updating them.

I've did research about the issue, and all I'm getting is an explanation about drop table method itself.

I've been using MySQL Workbench for quite sometime, and now I'm really thinking to switch to another MySQL client because of this.

Im using MySQL Workbench Community edition version 8.0.12.

Thanks in advance.


texts: Why is mysqlhotcopy slower compared to rsync?

texts:
My understanding is mysqlhotcopy works very similar to rsync.

In one of our servers backing the database file using rsync takes about 15 minutes for 50GB data.

Backing up the same data using mysqlhotcopy takes about 30 minutes.

How come mysqlhotcopy is significantly slower?


texts:
I have a table 'article' and each of these articles belongs to one of three different topics (topic_a, b and c). I also have a table for each of these topics with various fields. How can I connect these the best way?

I was thinking that, since every topic has many articles, I would add a topic_ID field to each article, but the ID would not be unique unless I use UUID. Would it make sense to use UUID in this case or is there a better way?

Another idea was to add three topic ID fields to each article and just fill the appropriate one (topic_a_ID, topic_b_ID,..) and leave the other two empty. But this also seems not right.

Any suggestions?


texts: Make a list of stations by checking next_station column value

texts:
How to list all station between selected two stations based on next_station_id

For instance, if the user selects origin A station and destination to J than I want to list all stations in ORDER that have set into the next_station_id column.

I have an intermediate level of knowledge in MySQL and have no idea how to write such a query.

SELECT r.number as route, s.name AS station, s.code AS station_code, rl.name AS line, s.line_two_id AS interchange
FROM stations AS s
         JOIN route_stations as rs
              ON rs.station_id = s.id
         JOIN routes r
              ON rs.route_id = r.id
         JOIN route_lines rl
              ON s.line_id = rl.id
ORDER BY rs.station_order ASC;

  I know the above query is just to join table but as I said I have no
  clue how to write a query to get a list of stations that have been set
  in next_station_id

route_stations table

I am also adding a diagram in case anyone wants to look the structure to refer and  suggest

Diagram


texts: MariaDB FULLTEXT search with apostrophe, and mandatory words

texts:
An apostrophe / single quote (') should be part of a word in a FULLTEXT index.

Admittedly I can't find this on the MariaDB website, but I'm assuming it should still behave like MySQL (version 5.5).

But when using "IN BOOLEAN MODE", and prefixing the word with a "+" (so it's mandatory in all rows returned), the record is not returned.

For example:

CREATE TABLE customer (
    name TINYTEXT NOT NULL,
    FULLTEXT (name)
) ENGINE = InnoDB;

INSERT INTO customer VALUES ('O''Brien');
INSERT INTO customer VALUES ('O Brien');
INSERT INTO customer VALUES ('X''Brien');
INSERT INTO customer VALUES ('Extra Amy');
INSERT INTO customer VALUES ('Extra Brian');
INSERT INTO customer VALUES ('Extra Cat');
INSERT INTO customer VALUES ('Extra Debbie');

I get these results:

SELECT * FROM customer WHERE MATCH (name) AGAINST ("O'Brien" IN BOOLEAN MODE);
  "O'Brien"
  "O Brien"
  "X'Brien"
3 rows in set (0.000 sec)

SELECT * FROM customer WHERE MATCH (name) AGAINST ("+O'Brien" IN BOOLEAN MODE);
Empty set (0.000 sec)

SELECT * FROM customer WHERE MATCH (name) AGAINST ("+Brien" IN BOOLEAN MODE);
  "O'Brien"
  "O Brien"
  "X'Brien"
3 rows in set (0.000 sec)

SELECT * FROM customer WHERE MATCH (name) AGAINST (+"O'Brien" IN BOOLEAN MODE);
  "O'Brien"
  "O Brien"
  "X'Brien"
3 rows in set (0.000 sec)

Am I missing something, like needing to quote the apostrophe?

Or is this an intentional difference between InnoDB and MyISAM?

Thanks to @rick-james, I've updated this question to include 3 "extra" records to avoid the 50% threshold limit, and I've included an example where the + is before the quoted word.


texts: How to merge two databases, both with primary keys

texts:
I have two databases, both with the same tables, but the data is out of sync.

I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a new ID.

E.G

Table users in B has a row with ID (auto increment, primary) 5, for a guy called Bob. Table users in A has a record for a guy called Dave in row ID 5.

I'd like Dave to be added to B.users but with a new ID.

I have looked at using Navicats Data Sync, but there are no advanced options to allow this, only to stop it overwriting (updating) or removing the record.

What are my options here? Is it a manual job?


texts: Find current users DDL statement running, Oracle 11g

texts:
I have created a database trigger (oracle 11.2.0.4 standard edition on windows 2008 R2) to intercept any drop or truncate statements, eg

drop table abc123;

and that will raise an exception to prevent any drop statements

As part of that trigger I would like to log the statement that is being called so I can a) see who is trying to do what that they shouldn't and b) allow certain drop and truncate commands to execute

I've tried looking in v$sql but I don't think DDL statements are run this way, eg

    select /*ignore this*/ nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' username,
        ses.sid,
        ses.machine,
        replace(sqlt.sql_text,chr(10),'') as stmt,
        ltrim(to_char(floor(ses.last_call_et/3600), '09')) || ':'|| ltrim(to_char(floor(mod(ses.last_call_et, 3600)/60), '09')) || ':'|| ltrim(to_char(mod(ses.last_call_et, 60), '09')) as runtime
    from v$session ses
        join v$sql sql on (sql.address = ses.sql_address and sql.hash_value = ses.sql_hash_value and sql.sql_text not like 'select /*ignore this*/%')
        join v$sqltext_with_newlines sqlt on (sqlt.address = ses.sql_address and sqlt.hash_value = ses.sql_hash_value)
    where ses.username = user
    order by sql.hash_value, sqlt.piece

Is there another view I can get currently executing DDL statements from?

Thanks


texts: Synchronizing two MySQL Server both serving clients after reconnect

texts:
I am not a pro in DB...here is my case.

I have 2 MySQL databases on ServerA and ServerB and clients in 2 buildings, with only network connection between them and no more outer network available.

I am requested to sync the data and use each of them as backup:

When ServerA dies, clients in A will go to ServerB
When ServerA is up, it will sync back the data from ServerB.

The problem is when the connection between ServerA and ServerB is down, ServerA and ServerB should continue serving clientsA and clientsB making data not sync.

What should I do to sync them?


texts: SSMS IntelliSense not working with sa user

texts:
In SSMS (versions 17 and 18), IntelliSense is not working with the sa user, but it is working with another user.
If I connect to a SQL database with the sa user and create a query window, IntelliSense does not work in this window.
However, if I make a second connection to the same database with another user and create a query window, IntelliSense works.
If change back to the query window with sa user, IntelliSense again does not work.


texts: Can user variables be assigned of BIT datatype?

texts:
Based on the documentation:

  User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.

As you see, user variables aren't supported BIT datatype. Right? Ok, I have this code:

BEGIN
    SELECT active INTO @active FROM users WHERE id = new.user_id;
    IF (IFNULL((@active & b'100' > 0), 0) < 1) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "You cannot vote";
    END IF;
END

Note: active column is BIT(10).

You know, my code surprisingly works as well .. Why? I mean how MySQL treats with @active variable as a BIT datatype?


texts: changing float->numeric casts from assignment to implicit, dangerous?

texts:
In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding precision.

In MySQL, round(some_float_column, 2) works as expected, returning the value of some_float_column rounded to two decimal places.  In Postgres it errors with ERROR:  function round(double precision, integer) does not exist and suggests HINT:  No function matches the given name and argument types. You might need to add explicit type casts..

As indicated by the docs...
http://www.postgresql.org/docs/9.1/static/functions-math.html
...Postgres has two round functions, round(value) which takes a double precision or a numeric, and round(value, precision) which takes a numeric only and an integer.

So, I don't understand why the two-argument form of round doesn't take a double to begin with, but whatever.  In searching I discovered two solutions to this problem.  One is to simply create my own version of round(value, precision) that takes (double, int) and wraps the existing (numeric, int) version with an explicit cast.  That certainly works, but I don't like it (my background is in Oracle which doesn't even have a true floating point type).  It seems to me that float/double ought to be implicitly castable to numeric.  And it turns out that an ASSIGNMENT cast for these types comes pre-defined.  But ASSIGNMENT doesn't work for function calls, as we see here, it would need to be IMPLICIT.  The trouble with that is that only one cast can be defined per pair of types, and the float4->numeric and float8->numeric assignment casts are required by the system and can't be dropped.  So the only way to make those casts implicit is to update pg_cast set castcontext = 'i' where castsource in (700,701) and casttarget = 1700.

Now we're into hacking the catalog, and that's a sign that this might be a bad idea.  But I don't have any hard evidence that it's bad.  Float/double values are inexact, and numeric values are exact, so it seems to me that casting from the former to the latter would be fully data-preserving and therefore logically safe.  The only potential problem I know of would be introducing ambiguity into other functions' argument patterns, but the purpose of asking this question is mainly to fish for potential problems I don't know of.

So, is it dangerous to change the float->numeric casting behavior from ASSIGNMENT to IMPLICIT?


texts: Normalization: Should common columns of two tables be pulled out into a third table?

texts:
Should I further normalize my current design to eliminate redundancies?

I have the following tables:

empl: standard user table

empl_cat: employee "category"

empl_admin_cat: administrative "category"

Both of the category tables have a many-to-one relationship back to the empl table.

The employee category represents the categorization of the employee within the company.

The administrative category represents that employee's administrative oversight (if applicable) over other employees.

The tables are defined like this (irrelevant columns have been omitted):

empl (
    empl_id int PK,
    ...
)

empl_cat (
    empl_cat_id int PK,
    empl_id int FK,
    eff_date date,
    base_id int FK,
    eqp_id int FK,
    pos_id int FK
)

empl_admin_cat (
    admin_cat_id int PK,
    empl_id int FK,
    co_id int FK,
    base_id int FK,
    eqp_id int FK,
    pos_id int FK
)

I considered using a single table with a type column, but decided against it because they logically represent two different things, and while 3 of their columns are the same (base_id, eqp_id, pos_id), they each have one additional column (eff_date and co_id).  Was this the right decision?

Would it be worthwhile creating another table that has an autoincrement Primary Key which holds all possible variations of base_id/eqp_id/pos_id, with the empl_cat and empl_admin_cat tables referencing the appropriate record in this new table instead of having their own base_id/eqp_id/pos_id columns?

Edit: The base_id, eqp_id and pos_id fields represent their "base of operations", "operating equipment", and "position". For example, SFO/777/Captain. The co_id in the empl_admin_cat represents a company.  eff_date in empl_cat is an effective date.


texts: How to format a number with optional fraction

texts:
Is it possible to format a number in Postgresql with optional fraction?  Like:

1.0 -> '1'
1.3 -> '1.3'

I looked into to_char, but I don't get much further than '1.' with to_char(1.0, 'FM9D9').

Is it possible, and how? I'm using Postgresql 9.5


texts: Linking foreign keys across multiple databases: direct, or using an intermediary table?

texts:
I want to make a part of my application reusable, and that warrants moving the corresponding tables into a separate database. So for the sake of an example, please consider the two imaginary databases in the list that follows. (More databases sharing the same logic may be added as the project grows.)

users containing tables related to user sign ups, login and e-mail history, password reset tokens etc., as well as the accounts themselves; and
blogs having tables for posts, media files, comments, etc.

Each table in the blogs database must obviously have an account_id column referring as a foreign key to users.accounts.id. (I do realise that to make it work both databases must use InnoDB and reside on the same server.)
My question is what would be a better practice:

direct reference to another database:

simply refer blogs.posts.account_id to users.accounts.id (repeat with all other blogs.* tables),
make each reference CASCADE ON DELETE; or

using an intermediary table:

create an intermediary table blogs.accounts having only one column called id; then
on one hand, refer every table inside the blogs database to that intermediary table (so blogs.posts.account_id to blogs.accounts.id, CASCADE ON DELETE); and
on the other hand, finish by referring this blogs.accounts.id to the 'upstream' users.accounts.id, make sure to CASCADE ON DELETE as well.

The latter seems like an unnecessary complication. But the only advantage I can think of is this can make the setup future proof in case we end up having to still migrate one (or some) of the databases to another server:

If we link the tables directly, after the migration the blogs database will have lots of disparate account_id columns that won't CASCADE ON DELETE
But if these intermediary tables get disconnected from the upstream users.accounts.id, their neighbouring tables in each respective database are still linked to them. This way we can continue benefitting from at least somewhat integrity and CASCADEs. In other words, if a user gets deleted, all we have to do is have a script go through each of these *.accounts connector tables and delete the id counterpart once, and CASCADE will take care of the rest of the tables inside of that database automatically.

Am I on the right track with this logic, or am I missing some other ways to handle this more effectively, and therefore reinventing the wheel?


texts: How to create custom error messages in APEX?

texts:
I would like to replace standard ORA-xxxxx messages with custom ones in Oracle Application Express. For example instead of this:

to have something like "You have left some fields blank, try again".
What is a best way to do this?


texts:
On my Ubuntu 18.04 machine using  mysql 5.7 I have installed sphinxsearch 2.2 and now I'd like to add SphinxSE as an engine so that I can connect to it without language-specific medium.
The docs on sphinix is rather old (for MySQL 5.1.x) and involves compiling mysql from source which I shy away from as it may break the existing stuff.
So I'm wondering if there is any straightforward solution for this which does not require source compilation?


texts: Are there side effects to Postgres 12's NOT MATERIALIZED directive?

texts:
I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the same between PG10 and PG12.

My next experiment was to add the NOT MATERIALIZED directive to the CTEs and the result was astounding:  it cut query times dramatically (halved them in some cases).

I read here that MATERIALIZED was the default functionality prior to PG12. And that functionality would write all the contents of the CTE into a temporary location.

So my question is mainly around NOT MATERIALIZED:

What does the NOT MATERIALIZED functionality do with the data behind the scenes in contrast to MATERIALIZED?
Are there any side effects to NOT MATERIALIZED I should be aware of before refactoring our codebase?


texts: UPDATE on column referenced in sub-query

texts:
We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following:

update CE_WORKSET_READ_ENTRY workset
set workSet.MARK = 0,
      workSet.SELECTABLE = 0
where workSet.MARK = 1 and
        (workSet.TRANSFORMED_TYPE, workSet.TRANSFORMED_KEY) in (
            select source.TRANSFORMED_TYPE, source.TRANSFORMED_KEY
            from CE_WORKSET_READ_ENTRY source
            where source.SELECTABLE = 0 and source.MARK = 1);

I expect the database to evaluate the condition on the 'old' state, which is indeed the case. However, the statement takes surprisingly long to complete as the number of updated elements grows. Why is this?

Additional infos: there are no indexes, no primary keys, it is used as a  temporary store like a queue. The table held  ~100'000 entries when the update took extremely long. Unfortunately, I can no longer reproduce the original explain plan since the data is now gone.


texts: Why do we still need VIP when using SCAN?

texts:
When we are using Single Client Access Name (SCAN), the clients connect to the database through the SCAN name in DNS or GNS like in the example below:

stackdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.stack.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stackdb)
    )
  )

but why do we still need to specify a Virtual IP address (VIP) when configuring the clusterware? where is the VIP  used?

I know that before 11.2 we used the VIP like this

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=tcp)(HOST=rac1-vip.localdomain)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=rac2-vip.localdomain)(PORT=1521)))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )


texts:
When does oracle implicitly converts date type? on what parameters does it depend?

I am running the following:

SELECT SYSDATE - '01-01-01' FROM DUAL

and got :

SELECT SYSDATE - '01-01-01' FROM DUAL
                  *
ERROR at line 1:
ORA-01722: invalid number

why Oracle not converting varchar '01-01-01' to date ?

nls_date_format I have set to 'DD-MM-RR'


texts:
This is obviously a SSCCE.

I have an inventory table that holds the number of items (numOfItems) in a warehouse at any given date (inventoryDate). Both are integers to keep things simple:

CREATE TABLE inventory(inventoryDate INTEGER, numOfItems INTEGER);
ALTER TABLE inventory ADD PRIMARY KEY (inventoryDate);

Now I have some entries:

INSERT INTO inventory(inventoryDate, numOfItems) VALUES(1,250),(2,275)

What the above says is that on time 1 there were 250 items in the warehouse and on time 2 there were 275.

Now I want to test whether, at the latest date, the number of items in the warehouse exceeded 1000:

SELECT 1000<(SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)

The above seems to work. However, on the edge case that there are no entries at all in the inventory table it's not working:

DELETE FROM inventory;
SELECT 1000<(SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)

... returns:

?column?
(null)

I would like to treat the edge case where there are no entries at all as denoting zero (0) items. I know I can always initialize the inventory table with a fake first entry with a value of zero but like I said this is an SSCCE (in my real case this table is really a view which I don't want to modify).

So I end up writing the query like this:

WITH cte AS (SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)
SELECT 1000 < (
CASE
WHEN NOT EXISTS(SELECT 1 FROM cte) THEN 0
         ELSE  (SELECT numOfItems FROM cte)
END)

The above does seem to work. But, is there a more idiomatic way? Something like a COALESCE but for an empty result set instead of null?


texts: MS SQL 2008 R2 - How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another

texts:
We currently have 3 Windows Server 2008 R2 VM identically build for Sage x3 v6.5, one for production/formation/budget database, a second one for QA (qualification) and a third one for dev & tests. This makes a total of 6 databases, Microsoft SQL Server 2008 R2 with standard licences. We would like to improve our process in order to always have fresh production data in dev, test and qualification (and potentially review, at the same time, the budget and training databases locations, which are also copies of the production scheme).

As I'm not a database specialist (currently an infrastructure engineer apprentice), let alone Microsoft SQL Server... I come to ask you for advice and best practices. Our infrastructure being rather modern, robust and spacious, we don't have any storage capacity or performance problems but, in the interest of optimization, I would like to avoid, as much as possible, having to duplicate 6 times the production VM (about 800 Go) or the production database as it is more or less the case actually...

So is there a way to use the production database from another environment but to redirect the writings of those environments to another location, specific to each environment (another shema or somewhere else...) to keep production database clean as it should be, exactly as a VM snapshot does physically (redirect on write) ? If not, I think I will have to copy the database 6 times (don't hesitate to tell me if I'm wrong)... How could I proceed in SQL 2008 R2 because it's hard to find how to copy tables (and their data) from a specific schema to another database schema on another server and to automate it. And, afterwards, is it possible to do differential copy (each night for example) to reduce the process duration.

Thanks a lot for all your answers :)

Sincerely yours,
Tigerblue77


texts: how to alter sequence in db2 stored procedure by another user

texts:
I am using DB2 LUW Version 10.5.0.5 on Linux (x86_64)
I have written a procedure to alter a sequence to restart with a certain number. The procedure takes the sequence name and the number to restart with as parameters.
The procedure got created successfully . I granted a developer connect access on the database and execute access on the procedure. But he is getting SQL -551 (permission issue for alter sequence sql) when he executes the procedure.
Create table works perfectly fine when it is executed in a procedure by anyone who even doesnt have create table access. WHy alter doesn't work then? Is there a way to allow developers to alter a sequence in a database without granting alter access on the sequence itself?


texts: Finding the last time a table was updated

texts:
The query:

SELECT
    name AS TableName,
    create_date AS CreatedDate,
    modify_date as ModifyDate
FROM sys.tables
order by ModifyDate;

...will tell me the last time a table was created and modified (from a DDL perspective). But I want to know the last time actual data was either inserted or removed from the table.  Is it possible to get this in SQL Server?


texts:
I have a Credit/Debit table (5 millions records). The application must provide an UI View to search data providing N criteria:

Type of date (required) e.g.. Creation date or due-date
Date range (required)
Status (paid, non-paid or both)
Document number
Customer ID
Type of payment (credit card, money etc)

The user must provide the first two criteria, but the other may be or not be provided. So I have many combinations.

I think I will have to create many indexes such as:

ix_search_customerID_dueDate (customerID, dueDate)
ix_search_customerID_creationDate (customerID, creationDate)

There's uncountable combinations. I use C# and Entity Framework to generate queries based on which values was provided, that's easy to do, but I don't know how to create indexes to cover all possibilities. Is it possible or should I change the UI logic?

I read this answer so based on this I believe that if I create an index (creationDate, status, document, customerID, typeOfPayment) and the user only provides for example Customer ID the index won't work.


texts: T-SQL How does CHANGETABLE Have a Parameter with the Literal Table Name

texts:
I can pass the literal table name into the built in function CHANGETABLE. I was wondering how this is done and if it is possible to do in my own stored procedures/inline functions.

In addition to that. If it is possible to pass it to a literal constant first and then pass it to the function so I can get the table ID and not have to specify the table name in two places.

Thanks in advance.


texts:
I want to manage roles and permissions. Most of designs on web look like this

tables:

Users
Roles
UserRoles
Permissions
RolePermissions

Here, what is permissions? I am thinking for such a design instead:

Users
Roles
UserRoles
Permissions

In this design, Roles is supposed to be:

   id   |     name

while permissions is supposed to be:

id  |  role_id  |  section  |  action

permissions defines which role in which section has what action control. something like this:

id  |  role_id  |  section  |  action

1   |     2     |  posts    | edit
2   |     2     |  posts    | add
3   |     2     |  posts    | delete
4   |     3     |  users    | approve
5   |     3     |  users    | edit
6   |     4     |  articles | delete
7   |     2     |  users    | givepermission

It uses two strings instead of an extra table and numbers. Also checking it on PHP seems easier.

Does this design have problem? And is it recommended by you according to your experiences?


texts: How to know the Database user Login History from begining?

texts:
I need to know one particular user's login and logout database history from beginning in SQL SERVER 2005.
Can anyone help?


texts:
Have a bit of glitches from the time I was using SQL Server back in 2006 and the many other databases I played with (and which I have not touched since then).
I need to create a materialized view to quickly get access to the data without overloading the DB but also to pull them what it was made static by the time the view was created.
SQL Server seems to support the materialized views with the Indexed views, but to be created these require among the other thing the following:

the WITH SCHEMABINDING construct
a clustered INDEX

Failing in that, my understanding is the view is not auto refreshing, which is probably what I need?
My goal is to get a view, that I can refresh whenever I need.
So far I created a Materialized view in the SQL 2005 instance I have access to with the following statement:
CREATE MATERIALIZED VIEW xys AS
SELECT 123
FROM X
WHERE ...
GROUP BY

Any memory refresher?


texts: What is the max network latency between synchronous-commit AlwaysOn Availability Groups

texts:
Is there a maximum supported network latency (ms) between two synchronous AlwaysOn replica?
I need to give this information to network administrators for configuring the link between the two nodes of the cluster.
I know that syncronization between replicas is influenced by disk speed and other things: The picture in this article explain the process involved.
The AlwaysOn instance will host the sap database (1 TB of data) and I estimate that the workload will be considerable with large transactions.


texts: Oracle 9: Convert date from mm/dd/yyyy hh:mm:ss format to iso8601 formatted datetime

texts:
Note: I only have read access to this database.

I need to convert an Oracle 9 date type from d/mm/yyyy hh:mm:ss format to the iso8601 standard. Like the example below:

4/22/2015 12:02:56 AM => 2015-04-22T04:02:56.000Z

Below is a a query that will convert an Oracle date type to a iso8601-formatted string. I need an is8601 formatted date.

SELECT sysdate,
    to_char((from_tz(to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'America/New_York') at time zone 'UTC'),'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"') "iso8601"
FROM dual

Thanks for any and all help.

EDIT: The end goal is to grab these dates and insert them as datetimes into a postgres database that powers a rails app.


texts: How to exclude NULL values inside CONCAT MySQL?

texts:
If I have this - tadd is the Address table:

CONCAT(tadd.street_number, ' ',
            tadd.street_name,', ',
            tadd.apt_number,', ',
            tadd.city,', ',
            tadd.postal_code,', ',
            tadd.country) AS 'Address'

Is there a way to exclude the apt_number if it doesn't exist?

I was thinking of:

WHERE tadd.apt_number IS NOT NULL

But it will return only those rows with apt_number, and even if something works how do I then deal with that extra comma.

If it's a duplicate please post a link in comments.


texts: Combining multiple-row data into a single row

texts:
I have a table that has columns like following

ID  Number  Value1  User1  Value2  User2  Value3  User3
--  ------  ------  -----  ------  -----  ------  ------
1   123     0       Jack
1   123                    1       TOM
1   123                                   2       Tim
1   456     0       Jones
1   456                    1       Jim
1   456                                   2       Carter

I need result like the following,

ID  Number  Value1  User1  Value2  User2  Value3  User3
--  ------  ------  -----  ------  -----  ------  ------
1   123     0       Jack   1       TOM    2       Tim
1   456     0       Jones  1       Jim    2       Carter

Can anyone please help me out on this?

The column ID remains same. The columns Value and User will go till Value6 and User6 and each row has values like one below the other. I need to combine all of them and show it as a single row.


texts: What exactly happens when optimizer_prune_level = 1?

texts:
This documentation link states that optimizer_prune_level

  Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space.

Can anyone help with the knowledge of what kind of heuristic principles are applied?

Consider this simple schema:

CREATE TABLE `college` (
  `colg_id` int(11) NOT NULL AUTO_INCREMENT,
  `colg_name` varchar(20) DEFAULT NULL,
  `colg_address` varchar(20) DEFAULT NULL,
  `avg_fees` int(11) DEFAULT NULL,
  PRIMARY KEY (`colg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4

CREATE TABLE `department` (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(20) DEFAULT NULL,
  `dept_address` varchar(20) DEFAULT NULL,
  `dept_hod` varchar(20) DEFAULT NULL,
  `colg_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`dept_id`),
  KEY `colg_id_fk` (`colg_id`),
  KEY `dept_name` (`dept_name`),
  CONSTRAINT `department_ibfk_1` FOREIGN KEY (`colg_id`) REFERENCES `college` (`colg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4

CREATE TABLE `student` (
  `stud_id` int(11) NOT NULL AUTO_INCREMENT,
  `stud_name` varchar(20) DEFAULT NULL,
  `stud_address` varchar(20) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  PRIMARY KEY (`stud_id`),
  KEY `dept_id_fk` (`dept_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1201 DEFAULT CHARSET=utf8mb4

Relation college contains 10 rows, department 100 rows and student 1100 rows.

For the following query

select * from college c, department d, student s where c.colg_id = d.colg_id and d.dept_id = s.dept_id;

-the optimizer trace is attached: trace.json.

Going off of the trace (with my perception abilities), the optimizer has even tried calculation of cost for joining college with student. A cross product join order plan is also considered amongst others. So is cross product avoidance not part of heuristics? At least for less number of joins? So heuristics change with number of joins? If yes how and any examples?


texts: "Cannot resolve table name" error when applying MySQL dump to RDS

texts:
I'm setting up replication of an external MySQL instance to RDS.

Both the external MySQL instance and RDS run MySQL 5.7.10.

I've followed the procedure, but when dumping the database with mysqldump and sending it to mysql connected to RDS, I get the following error:

  ERROR 1215 (HY000) at line 2081: Cannot add foreign key constraint

SHOW ENGINE InnoDB STATUS on RDS shows:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-03-25 01:38:56 0x2ad07ddcf700 Error in foreign key constraint of table db/Prospect:
 FOREIGN KEY (`restaurantId`) REFERENCES `Restaurant` (`id`),
  CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci:
Cannot resolve table name close to:
 (`id`),
  CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

OK, indeed the dump file recreates the tables alphabetically, so the Prospect table appears before Restaurant and Zone, which can explain the error.

But NO, because mysqldump adds SET FOREIGN_KEY_CHECKS=0 to the top of the file, so it should work anyway.

So I decided to test it against a vanilla MySQL 5.7 installation, and no surprise, the dump is imported successfully with the exact same commands.

What causes this error specifically on RDS, and how to avoid it?


texts:
In my query, I need to get the previous row with the current row and everything works fine here.

SELECT *,
       (SELECT calendar_id
       FROM   calendar AS sub
       WHERE  sub.calendar_id < calendar.calendar_id
       ORDER  BY sub.calendar_id DESC
       LIMIT  1) AS previous
FROM  calendar

But now i need to join more two tables i using inner join for what.

SELECT *,
       (SELECT calendar_id
       FROM   calendar AS sub
       WHERE  sub.calendar_id < calendar.calendar_id
       ORDER  BY sub.calendar_id DESC
       LIMIT  1) AS previous
FROM calendar
     INNER JOIN relationships
             ON calendar.relation_id = relationships.relation_id
     INNER JOIN customers
             ON customers.customer_id = relationships.customer_id
WHERE  relationships.user_id = '$user_id'
       AND Date_format(calendar.date, '%m-%Y') = '$date'
ORDER  BY calendar.date ASC

Here is my final query with inner joins, but that part about previous row now result is just mixed. How i can keep my previous row result good and have that data from other tables with inner join?


texts: B2B application: users has its own users

texts:
B2B Scenario I am trying is that:
The following Roles exists in the system:

Super Admin
Super Admin Staff
Owner (client)
Admin (owner i.e. client and creates admin to define sales counters)
Sales Counters

That means there are users who'll create their own sub users e.g.
[FLAG-A] ROLE "SUPER ADMIN" creates a ROLE "Owner" and "OWNER" creates its own sub users ROLES i.e. "ADMN" & "SALES COUNTER".
Each role obviously has its info to login to the system
I tried to design database as following:

In this diagram I am assuming that main user creates sub user that in my view many to many relationship so I have to add table between them as "USER_PERSON".
Problem is to set "WHO CREATED USER" in the system. I mean which user has created sub user as described in [FLAG-A]

Please check this diagram


texts:
For auditing purposes, we have a requirement in which we have to enable Auditing for theses events:

DBA Logins
Changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.)

Changes made by our application are stored in tables (CreatedBy, ModifiedBy, CreatedOn, ModifiedOn) and row changes are stored in XML in a specific table so we won't need to log changes made by our application.

Previously we had C2 Audit Mode temporarily enabled, but due to data volume and performance considerations and having these requirements in mind we considered it excessive and disabled it.

Enabling C2 Audit mode is fairly easy, how can i configure the database to perform this kind of logging ?

Additional Notes:

Currently our server uses SQL Server 2008 R2 Enterprise but since we have mostly data storage and reporting services we'll be downgrading to SQL Server 2008 R2 Standard .


texts: PostgreSQL logical replication slave - shouldn't it be read-only and available only for subscription updates?

texts:
I have database and table collection_city on one server. It has 21 rows. I have database and table collection_city on a second server. It also has 21 rows.

They both have this row:

tinker=# table collection_city;
 id |     name      |    alias     | postal_code | region_id
----+---------------+--------------+-------------+-----------
  2 | Obrenovac     | obrenovac    |             |         1

The id column is the primary key.

I created a publication on the first server:

CREATE PUBLICATION tinkerpub FOR ALL TABLES;

tinker=# \dRp[+]
                      Publication tinkerpub
  Owner   | All tables | Inserts | Updates | Deletes | Truncates
----------+------------+---------+---------+---------+-----------
 postgres | t          | t       | t       | t       | t
(1 row)

I created a subscription on the second server:

CREATE SUBSCRIPTION tinkersub CONNECTION 'dbname=tinker host=192.168.150.5 user=postgres password=test port=5432' PUBLICATION tinkerpub WITH (copy_data = false);

My publication-subscription process works correctly, but I read somewhere that the slave server in that case should be read only for manual updates.

But if I run an INSERT command for the table collection_city only on the subscription server, I can see that a new row is being added in the table on the subscription server, although of course it does not exist on the publication server.

Is this the correct behavior? Shouldn't the subscription server be read-only for all modifications except from the publication server?

I am of course aware that the publication server should be able to perform insert, update and delete statements on the subscription server, but is it also possible to do manual INSERT statements on the subscription server? Shouldn't it be disabled in some way?


texts:
I am learning about Database design,  I have doubt about some database design conepts
I want to know which is better design between two design
Design 1
there is two tables:
Object and Field
Object
| ID | NAME|
ID is PK
Field
| ID | OBJECT_ID | NAME
Both ID and OBJECT_ID are PK and OBJECT_ID IS FK

Design 2
there is three tables: Object, ObjectField and Field
Object
| ID | NAME |
ID is primary key
Field
| ID | NAME |
ID is primary key
ObjectField
| OBJECT_ID | FIELD_ID |
Both OBJECT_ID AND FIELD_ID is PK and FK
relation is stored in another table


texts:
I wanna create script my whole table, including all statistics and indexes.

In SSM, When I right click table and create script, it gives me script for Indexes for not for statistics.

If I right click indexes, then there is an option to generate script, but this option is disabled when I right click Statistics.

Can anyone tell me how do I generate script for statistics.

Thanks


texts: VBA @ Access on replacing values on a huge list

texts:
I have a question (that's why I'm here actually :P ) about VBA @ MS Access.

So, I have a huge database (well, for me it is huge, about 23K+ ID) on shoes. It has 5 columns: ID, Article number, size (mm),pairs (avaliable on stock) and EAC13 codes. It is updated daily. Everyday I get the update on it, on CSV (all those ID all over again) and create new tables, although I just need to update stock availability. However there is one column that I have to change in order to get reports: Sizes. I have to get 2 reports, one where I get the size of the shoe in mm and the other where the size is on European measure. For example: 245 mm is equal to a 35. Simple as that. I have a VBA that I thought it could help me to replace all those values (yes, all the 23k on the "size" column), that goes like this

Public Sub Substitui()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT * FROM BS_2704"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF
For Each fld In rst.Fields
If fld.Value = "150" Then
rst.Edit
fld.Value = "24"
rst.Update
End If

If fld.Value = "160" Then
rst.Edit
fld.Value = "25"
rst.Update
End If

(...)

If fld.Value = "325" Then
rst.Edit
fld.Value = "50"
rst.Update
End If

Next fld
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Anyway...this only changes 150 entries (more or less). The million dollar question is, is there any kind of line that I am missing here in order to get this VBA to change ALL the 23k entries?

I am a n00b on this fields, so I would appreciate some n00b therms for a start. Thanks for the patience.


texts: Primary index is being used, not the one which is on the field present in the where condition

texts:
I have 2 tables, one is category table and has below coulmns and has a primary index on cat_id column.

cat_id (primary key),  acc_id, islive

Another table is cat_brand table which has below fields and has the composite primary key on fields cat_id and brand_id and fk relation on cat_id column.

cat_id brand_id acc_id

Indexes on cat_brand are(cat_id,brand_id) which is primary index and another index on acc_id column, which I created for below SQL query.

select distinct cat_brand.brand_id
FROM  category cat
inner join  cat_brand on cat.cat_id = cat_brand.cat_id
where cat_brand.acc_id=1
  and cat.islive in ('true' )
  and cat.acc_id=1;

Using explain query shows me, that above query uses the primary index of table cat_brand which is cat_id,brand_id while where condition in the SQL query is cat_brand.acc_id=1 and cat_brand has an index on the same field, then why it doesn't use the index on acc_id field?


texts: Error: set_valued function called in context that cannot accept a set. What is it about?

texts:
I use Postgresql 9.1, with ubuntu 12.04.

Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a series generator into this plpgsql function:

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns setof record as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;
end;
$$    language plpgsql;

During execution I get the error:

ERROR: set_valued function called in context that cannot accept a set

What is wrong ? Contrary to Craig I tell the function to return setof record.

I can achieve something that work doing exactly like Craig, i.e. by defining a type create type pair_id_value as (idx bigint, value integer) and have my plpgsql function returns a setof of pair_id_value instead of a setof record.

But even with this working solution, I still don't understand why select id, generate_series(0,13) alone will return a result in two columns... and on the contrary calling the function (returns setof pair_id_value) with return query select id, generate_series(0,my_obj.value) from my_obj will return a result in only one column which field look like this "(123123,0)" "(123123,1)" "(123123,2)" (3 rows)  which are tuples obviously.

Is it a case where a temporary table must/should be created ?


texts:
What is the fastest way to add a new column to an existing table in MySQL?

I have tried ALTER TABLE tbl ADD COLUMN ..., but that takes quite a while since there are many rows out there.


texts: Tools and methodologies to keep to DBs aligned

texts:
2 DBs having schemas that represent the same semantic objects.
The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres.

Once in a while the production DB is changed (schema upgrade).

Question: what is the best practice to keep DBs of different types aligned semantically?


texts: Remove all records with duplicates in db2. (Not just the duplicate records)

texts:
How can I remove all the records with duplicates in db2. I have looked at various answers but they only remove the duplicates leaving one record from that set in the table. This is what I found already.

DELETE FROM
    (SELECT ROWNUMBER() OVER (PARTITION BY ONE, TWO, THREE) AS RN
     FROM SESSION.TEST) AS A
WHERE RN > 1;

But, I need a query that will remove all the records that contain duplicates not leaving behind one of them in the table.

A      A       1  <-- delete this
 A      A       2  <-- delete this too
 B      B       3
 C      C       4

P.S: Using RN >= 1 does not work as it will make the table empty by deleting all records.


texts: DBCC CHECKDB REPAIR_REBUILD taking too long

texts:
I'm administrating a database that became corrupt and I tried to do a REPAIR_REBUILD.

The server on this installation is a bit weak but the database isn't that big (roughly 2GB).

It's been running for 48 hours and still not completed. Is it possible ? And is there any way to check the current state other than just seeing "Executing query" ?


texts: Cassandra "Operation timed out - received only 0 responses." during write

texts:
I'm using Datastax PHP Cassandra Driver and Cassandra 2.2.5 on Windows 10. This is a single-node cluster, and the keyspace replication of

{
    'class': 'SimpleStrategy',
    'replication_factor': 1
}

This is the table I'm inserting to:

CREATE TABLE MyDocuments (
    "label" varchar,
    "fileName" varchar,
    "blob" blob,
    "createdTime" bigint,
    "lastUpdateTime" bigint,
    PRIMARY KEY("label", "fileName")
);

When I tried to insert a row with a blob that is 16419KB large, it gave me this error message Operation timed out - received only 0 responses.
The row is not inserted at all.

Inserting a row with a blob that is 16379KB worked fine.

The log files don't show anything. When the operation fails, nothing is appended to any log files inside Datastax / Cassandra folders.

After a few hours of Googling, I still have no clue as to why this is happening. I have tried increasing write_request_timeout_in_ms to 20000 in the settings (cassandra.yaml), but it does not help.

Does anyone know why a write seems to fail in this way when the size exceeds ~16MB?

Update:

I updated Cassandra to v3.7, same observation, but the error message now says Operation failed - received 0 responses and 1 failures.


texts: trying to get more rows from the table than there actually exist

texts:
I was not sure about a descriptive Title of this question so I put there what i thought to be the best sentence.

I have a table like this:

StudentID, Month, Year, Present
-------------------------------
1, 1, 2016, P
1, 12, 2016, P
1, 4, 2017, P

I want to write SQL statement, without using cursor, to return a resultset like the below one:

Required output:

1, 1, 2016, P -- <-- it is 'P' in the table
1, 2, 2016, A
1, 3, 2016, A
1, 4, 2016, A
1, 5, 2016, A
1, 6, 2016, A
1, 7, 2016, A
1, 8, 2016, A
1, 9, 2016, A
1, 10, 2016, A
1, 11, 2016, A
1, 12, 2016, P -- <-- it is 'P' in the table
1, 1, 2017, A
1, 2, 2017, A
1, 3, 2017, A
1, 4, 2017, P -- <-- it is 'P' in the table
1, 5, 2017, A
1, 6, 2017, A
1, 7, 2017, A
1, 8, 2017, A
1, 9, 2017, A
1, 10, 2017, A
1, 11, 2017, A
1, 12, 2017, A

is this possible through one SQL statement, without using cursors.

I am working on SQL Server 2000 database.


texts:
I searched the internet and PG documentation for hours and couldn't find any benchmark for it.
In MySQL 8.0 they specifically state that now the optimizer is smart and can do in-place modification of JSON column without updating the whole column. But I couldn't find any similar article about PG. jsonb is around for a long time already and it would be really expected that such common thing as updating a single field of large JSON would be optimized, but I couldn't find any notion about it. Moreover I found here an answer from 3 year ago (PostgreSQL update JSONB without jsonb_set) that proves that updating whole column as text is faster than using jsonb_set.
I'm trying to model a table with 2M rows, each will have 2 JSON fields with 3600 simple numeric fields (date: value). I will need to add 1 field to each JSON daily, meaning 4M json_sets.
Is it feasible, or I need to switch to some NoSQL like MongoDB for this feature?
Thanks


texts: Can I set different database directories in MongoDB?

texts:
In db2 we can create databases on different directories through db2 list db directory. Can we also do that on MongoDB?

I am aware of the dbPath in the config file. But do all databases created have to be stored there?


texts: Backend Table/Row Locking Structure using CTE Queries

texts:
This question is an extension of a previous question that I asked.  Table Locking When using Unions

With the same query we are using that is causing blocking the various queries used in these UNIONS are CTE queries.

For example:

CREATE TABLE #TempTable (
    [Columns]
)

WITH CTE1 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE2 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE3 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE4 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE5 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE6 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE7 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
)

INSERT INTO #TempTable
SELECT * FROM CTE1
UNION
SELECT * FROM CTE2
UNION
SELECT * FROM CTE3
UNION
SELECT * FROM CTE4
UNION
SELECT * FROM CTE5
UNION
SELECT * FROM CTE6 WHERE [Where-Condition]
UNION
SELECT * FROM CTE7

Assuming this particular scenario (and we do not have any explicit transactions declared) does the physical table locking structure change, because we are using CTE queries?

When the SELECT for CTE1 is finished as part of the UNION, are the physical tables still locked or are the locks still released?

Based on the answer from my previous question, I was under the impression that in the context of a UNION when the SELECT for one of the various SELECT statements completes the tables are released.  Is that incorrect?

We have not modified the isolation level for this transaction.  As I understand it, that means we are using READ_COMMITTED. The question is still applicable with just 2 SELECT statements. I am trying to get an understanding on how this functionality works.  The particular query I am working with uses 7, which is the only reason I specified 7.


texts: Stripping OIDs from tables in preparation for pg_upgrade

texts:
I have a postgres database in RDS, file size approaching 1TB.  We started in 2005, using ruby/activerecord/rails, and along the way have upgraded to PG 9.6
Rails migrations create tables using CREATE TABLE, never specified whether to include OIDs or not, but never used them.  So some of our oldest (and largest) tables have OIDs.  At some point postgres stopped defaulting to creating tables with OIDs, so tables created more recently don't have this issue.
We're looking to do an upgrade 9.6 -> 12, ideally using pg_upgrade.  This fails complaining about tables having OIDs.
We can do ALTER TABLE :table_name: SET WITHOUT OIDS but for our larger tables that takes several hours, locking the table, effectively taking down the database.  We would prefer to avoid the downtime if possible.
Is it expected that an ALTER TABLE :table_name: SET WITHOUT OIDS should rewrite all the rows of a table?
Is there a way to avoid this rewriting?  (Ordinary columns can be dropped without rewriting all the rows, for example.)
I tried on a toy database mucking with the metadata tables:
UPDATE pg_catalog.pg_class SET relhasoids = 'f' WHERE oid = (
  SELECT c.oid FROM pg_catalog.pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE n.nspname = 'public' AND c.relname = 'name_of_table'
);

It executed quickly and a cursory examination of the data didn't show any corruption.
But, this doesn't seem to be a documented approach.
Is this a terrible idea? Are there other approaches that don't require rewriting whole tables?


texts:
I have a query, and even using SQL Sentry, I can't eliminate the index scan.

This is the query:

SELECT TOP 30 codCliente FROM (
        SELECT t1.CodCliente, codcampo, valor, t1.chavealeat
        FROM tblCliente AS t1 WITH(NOLOCK)
            INNER JOIN tblClienteDetalhe AS t2 WITH(NOLOCK)
            ON t1.codcliente = t2.codcliente
            AND CodCampo IN(-1, 4)
        WHERE codStatus IN (0)
            AND t1.ChavePeriodo < GETDATE()
            AND t1.CodStatusLigacao = 0
            AND EXISTS
            (
                SELECT codcliente FROM tblclientedetalhe WITH(NOLOCK)
                WHERE codcampo = 3 AND valor = '2'
                    AND codcliente = t1.codcliente
            )
            AND EXISTS
            (
                SELECT codcliente FROM tblclientedetalhe WITH(NOLOCK)
                WHERE codcampo = 6
                    AND CONVERT(DATETIME, Valor) BETWEEN '2015-08-01' AND '2015-08-31'
                    AND DATEDIFF(DAY, Valor, GETDATE()) > 15
                    AND codcliente = t1.codcliente
            )
            AND NOT EXISTS
            (
                SELECT 0 FROM tblPesquisa WITH(NOLOCK)
                WHERE tblPesquisa.CodCliente = t1.CodCliente
            )
            AND EXISTS
            (
                SELECT codcliente FROM tblclientedetalhe WITH(NOLOCK)
                WHERE codcampo = 4 and valor = '202' and codcliente = t1.codcliente
            )
    ) AS Cliente
Pivot (MAX(Valor) FOR codCampo in ([4])) AS PivotTable
WHERE (((([4] = '202'))))
ORDER BY chavealeat;

This is the index I made:

create index IX_CHAVEALEAT_CODCLIENTE
on tblcliente (chavealeat,codcliente)
include (chaveperiodo,codstatus,codstatusligacao)

And, this is the analysis:

Why is the plan showing index scan? I'm covering all columns. I used option(recompile) to update the plan.

Is this index wrong?  The output is only codcliente.

I have 10-30 queries running at the time, and theres no wait_info problems.


texts:
I have one table which stores input from user, so to allow national characters I've set charset to utf8mb4.

Then I have second table, which can grow really large (100+ millions of rows) and I'm 100% sure that this table will store only ASCII characters, so I've set charset for this table to latin1 to save disk space.

Is there any downside of having different charsets among tables in one database? Is it considered bad practice?

Or is there any other way how to resolve this issue? (to keep disk occupation minimal and allow national characters)

EDIT:

output of show create table which takes user input (and has utf8mb4)

 CREATE TABLE `sensor` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `label` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(3000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` datetime(6) NOT NULL,
  `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `region` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `model_reference` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `label` (`label`),
  KEY `id` (`id`),
  KEY `sensor_ibfk_1` (`model_reference`),
 FULLTEXT KEY `fulltext_index`(`label`,`description`,`state`,`region`),
 CONSTRAINT `sensor_ibfk_1` FOREIGN KEY (`model_reference`) REFERENCES      `model` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4      COLLATE=utf8mb4_unicode_ci

output of show create table which is autogenerated (and has latin1)

CREATE TABLE `sensor_history` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `sensor_reference` int(20) DEFAULT NULL,
 `temperature` varchar(16) DEFAULT NULL,
 `pressure` varchar(16) DEFAULT NULL,
 `at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 PRIMARY KEY (`id`),
 KEY `sensor_history_ibfk_1` (`sensor_reference`),
 CONSTRAINT `sensor_history_ibfk_1` FOREIGN KEY (`sensor_reference`)     REFERENCES `sensor` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1


texts: Data migration: copy data with insert select and fill unique column

texts:
I have some tables in Postgres like this.

create table foo (
    txt text
);
insert into foo (txt) values ('a');
insert into foo (txt) values ('b');
insert into foo (txt) values ('c');

create table bar (
  txt text,
  n integer not null unique
);

I'm doing a schema migration. I would like to migrate data from foo to bar. I'd like bar to look like this.

=# select * from new;
txt | n
----+--
a   | 1
b   | 2
c   | 3

n doesn't have to be sequential, but it does have to be unique and not null.

I thought this query would work.

insert into bar (txt, n)
select txt, generate_series(1, (select count(*) from foo))
from foo;

But I think that's generating rows 1-N over and over again for each 'a', 'b', 'c', which violates the unique constraint.

SQL Fiddle: http://sqlfiddle.com/#!17/06e92c

Update

Okay, I found one hacky way to do it.

insert into bar (txt, n)
select txt, trunc(random() * 999999 + 1)
from foo;

Of course this isn't guaranteed to be unique. :/ But, if the data set is small enough, you might be able to scrape by with this.

Also, I understand why the first approach failed now. generate_series returns multiple rows, whereas with random I only return 1 row.


texts: Database existence not happened correctly

texts:
I am having problems running a SQL script to CREATE a database on SQL Server 2014.

My script fails with the following error, however I am not sure how to resolve the issue as I am a beginner with SQL Server.

CREATE DATABASE [BIDS]
  CONTAINMENT = NONE ON PRIMARY
  ( NAME = N'BIDS',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\BIDS.mdf' ,
    SIZE = 4096KB ,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024KB
  )
  LOG ON
  ( NAME = N'BIDS_log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\BIDS_log.ldf' ,
    SIZE
    ---

What should I do to resolve this error?

  Msg 1803, Level 16, State 1, Line 4
  The CREATE DATABASE statement failed. The primary file must be at least 5 MB to accommodate a copy of the model database.

  Msg 5011, Level 14, State 5, Line 11
  User does not have permission to alter database 'BIDS', the database does not exist, or the database is not in a state that allows access checks.


texts: How can I manage newly created tables permissions in the PostgreSQL?

texts:
I want to create default permissions on the schema and automatically assign these permissions to newly created tables. For this purpose, I've created a role and assign a user. Afterward, I've created default permission with the following statements. But there are no default permissions in the newly created tables. How can I manage users and roles on the newly created tables?
CREATE SCHEMA ${db_schema};

#for create read only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE ${db_name} TO readonly;
GRANT USAGE ON SCHEMA  ${db_schema} to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA ${db_schema} TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA ${db_schema} TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA ${db_schema} GRANT SELECT ON TABLES TO readonly;

#for create read write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE ${db_name} TO readwrite;
GRANT USAGE, CREATE ON SCHEMA ${db_schema} TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ${db_schema} TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA ${db_schema} GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA ${db_schema} TO readwrite;
grant readwrite to ${db_user_name}
ALTER DEFAULT PRIVILEGES IN SCHEMA ${db_schema} GRANT USAGE ON SEQUENCES TO readwrite;

# create a user and assign read-write role
create user appuser with password 'secret_password'
grant readwrite to appuser

# create a user and assign read-write role
create user reportuser with password 'secret_password'
grant readonly to reportuser

# create a table with appuser who has the read-write permission
CREATE TABLE usertestscehema.accounts (
username VARCHAR ( 50 ) UNIQUE NOT NULL
);
insert into accounts (username) values ('test');

# select table with reportuser who has the read-only permission
select * from accounts

Finally, I got the below error;

SQL Error [42501]: ERROR: permission denied for table accounts

Afterward, I examined the permissions with the following statement and I didn't see any permission related to reportuser who has my readonly user.
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='accounts'

Where am I missing about this problem?


texts: How do I configure MySQL '5.1.49-1ubuntu8' to show multibyte characters?

texts:
I am using MySQL version 5.1.49 and I have now enabled UTF8 character encoding. The default character-set for MySQL is latin1. How can I change it show UTF8 characters?

Even when I query a table using Workbench, I get 'NULL' in the name section which I want, should display multibyte characters.


texts:
I created a linked server to MySQL database server on my SQL Server database server. I tested the connection. It succeeded successfully for MySQL connection. I can see the databases on MySQL instance via my linked server but when i try to run a query with OPENROWSET, it gives me an error message like below. I have searched it on google many times but i couldn't solve my issue. What is the problem? Why do i get this message and how to solve it?

  The OLE DB provider "MSDASQL" for linked server "(null)" reported an
  error. The provider reported an unexpected catastrophic failure. Msg
  7330, Level 16, State 2, Line 25 Cannot fetch a row from OLE DB
  provider "MSDASQL" for linked server "(null)".


texts: MySQL:- Join two SELECT queries including join and aliases also

texts:
I have two queries and both are working fine individually, but I want to join both the queries and display the output in a single grid.

Query1

SELECT t1.*, t2.*
FROM item_master t1
INNER JOIN
(
SELECT item_master_id,
   SUM(received_quantity) AS received_quantity,
   SUM(ordered_quantity)  AS ordered_quantity,
   SUM(unit_cost)  AS unit_cost
FROM material_line_item
GROUP BY item_master_id) t2
ON t1.id = t2.item_master_id

In this query1 I am using two tables to fetch records

Query2

SELECT item_master_id
,SUM(open_quantity) open_quantity
FROM closed_stock
GROUP BY item_master_id

And in query2 from a single table

In all the above 3 tables 3 columns are there received_quantity ordered_quantity and open_quantity so I have to show all this 3 quantity columns in a single query by joining this above two queries.

So far what I tried

SELECT * FROM (SELECT t1.*, t2.*
FROM item_master t1
INNER JOIN
(
SELECT item_master_id,
   SUM(received_quantity) AS received_quantity,
   SUM(ordered_quantity)  AS ordered_quantity,
   SUM(unit_cost)  AS unit_cost
FROM material_line_item
GROUP BY item_master_id) t2
ON t1.id = t2.item_master_id)

join

(SELECT item_master_id
,SUM(available_quantity) open_quantity
FROM closed_stock
GROUP BY item_master_id)


texts: mysql emits BINLOG rows even though binlog_format=STATEMENT

texts:
We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default)

According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in our binlogs, as the first statement in each binlog.

Why is that?


texts: Need to count records and group count by date on Oracle

texts:
I have a table like the following

   ID                created    sent    type
-----------------------------------------------------
0001463583000051783  31-JUL-12  1   270
0081289563000051788  01-AUG-12  1   270
0081289563000051792  01-AUG-12  1   270
0081289563000051791  01-AUG-12  1   270
0081289563000051806  01-AUG-12  1   270
0001421999000051824  06-AUG-12  1   270
0001421999000051826  06-AUG-12  1   270
0001464485000051828  06-AUG-12  1   270
0082162128000051862  09-AUG-12  2   278
0082162128000051861  09-AUG-12  2   278
0022409222082910259  09-AUG-12  3   278

I would like to have the following for output

created     Count
---------------------
31-JUL-12   1
01-AUG-12   4
06-AUG-12   3
09-AUG-12   3

How hard would it be to accomplish this using SQL Developer on Oracle 10g?

I have tried several queries to generate such a table and in the end it does not group the count by date, just gives me a '1' for the count when we average 5000-10000 transactions daily. I'm probably overcomplicating it. But I would like something simple where I can pull the amount of transactions on a daily basis within a date range.


texts: MySQL general query log performance effects

texts:
One of our customers who has a somewhat problematic web application would like us to log all the queries for a period of 24 hours.

I wouldn't know how much data it will actually write.

The amount queries for any 24-hour period would be somewhere around 5M.

I could either allow a couple of GB of ramdisk, or mount an NFS share located on an otherwise unused JBOD.

Questions

What would be the effect if the destination where the log is written would get filled?
Would performance of the mysqld in any way (other than the general log write) be affected if the (dedicated) NFS share will start to perform slow due to heavy I/O?

Thanks in advance,


texts:
While the future of MySQL is unknown, I haven't had time to keep up with the known effects that Oracle has had on MySQL since it was bought from SUN.

Have there been changes of note, either by Oracle (or the MySQL community) since the deal became public?


texts: Transactional Replication - Snapshot metrics

texts:
I have an application that is using Transactional Replication. We are using snapshots to initialize the subscriptions. All publishers and subscribers are using SQL Server 2008.

Due to the way our deployment works, I end up having to reinitialize the subscribers every time we do a build. This is time consuming and annoys the business, so I have to make sure that our developers are aware of the impact their changes will have on the size of the snapshots and the time it will take to apply them. I also want to measure the impact of settings changes that I make on the publications and agents (bcp threads, batch size, etc).

The metrics I want to capture are:

The size of the snapshot generated for each publication.
The time it took to generate the snapshot.
The time it took to apply the snapshot at the subscriber.

Is it possible to get this information by querying distribution?  I realize that I could get snapshot size by looking at the filessystem after it is generated, but I would prefer to avoid that if possible.


texts: Print Column Names for Synonym for Table in Another Database

texts:
I am utilizing one database A. How do I print out the columns names for synonym for table in another database B?

create table DatabaseB.dbo.Customer
(
     CustomerId int primary key,
     CustomerName varchar(100),
     ZipCode varchar(9)
)

Answer if table was in Current Database A:

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM sys.columns c
where c.object_id = object_id('Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

How to Print columns for table if in Database B?

use Database A;
go;

create synonym dbo.Customer for DatabaseB.dbo.Customer

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM sys.columns c
where c.object_id = object_id('dbo.Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

I want have it use synonym in current database, (which already points to other database B). Purpose: To find columns for Customer, without specifying Database B


texts:
I have oracle 11.2.0.4 installed on Red Hat Linux 6.

I have an RMAN full backup from database that size is  20TB . This backup is created on ASM disks with different diskgroup from DATA.

I want to restore this backup to a new host .
For some reasons , I should plug these disks to a new host and then restore it, I have a question :

How can I add these ASM disks to new host ?

I have drop this diskgroup from target host and then with                        " oracleasm deletedisk " , unplugged them from OS and then unplugged these disks from target host and the plugging this disk to new hosts that have oracle 11.2.0.4 and Red Hat Linux 6 .

When I run /etc/init.d/oracleasm scandisks , scandisk is ok and then when execute oracleasm listdisks , these disks are not on listed disks .

Can any one help me ?

Thanks a lot


texts: How to use IN clause in sql server so that it selects everything if nothing is provided for its list argument?

texts:
Assume I have a sql query like this

SELECT * FROM TABLE where country in ('USA', 'UK')

This query works as expected that is it selects all rows where country column is either 'USA' or 'UK'. Now however, if I run this query

SELECT * FROM TABLE where country IN ()

its a syntax error. So I change it to run something like this

SELECT * FROM TABLE where country IN ('')

My goal here is to use jdbcTemplate of Spring framework to dynamically run this sql query. However I am not sure what my inputs are going to be. So I want to be able to pass the countries like 'USA' or 'UK' and get only rows related to those OR pass nothing and get ALL rows. So how do I get ALL rows if nothing is passed into IN clause?


texts:
Let's assume I have got a table with these data:

I want to order it by createdAt column DESC, and id column DESC

select id, createdAt from post order by createdAt desc, id desc

Now it looks like this:

I want to paginate it with 2 items per page and don't want to use offset, just limit:

select id, createdAt from post order by createdAt desc, id desc limit 2

To get the next 2 items i use this query:

SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2

I can go on like this. Get the last item's createdAt and id then use it for next page query.

But i'm trying to formulate the previous page query for almost two days and can't figure out a way yet.

Here is what i tried already:

Get the first item from current result (instead of last) use it's id and createdAt field and reverse the conditions in query (createdAt >=, id >, created at >). But this query always gives me the first two results (which is normal because the rows providing this conditions are the first two).

I'm already out of ideas. I need help. Thanks.


texts: if database is not shrinked does it affect backup size?

texts:
Ok my question is simple.

Lets say that when i click database and hover over shrink it says it can be reduced 1 GB space.

Now when i do a backup of this database, does this space also getting added to the backup file or backup process is somewhat special and it already shrinks it ?

SQL server 2012


texts: How to find out why this query is in waiting state?

texts:
I have a query that has been several minutes in waiting and is not running at all.

In the SQL Developer monitor session view it shows that query has been waiting for 667 seconds?

How can I find out why this query is in waiting state?


texts: CREATE DATABASE vs. CREATE ANY DATABASE permissions

texts:
In Microsoft SQL Server, what is the difference between the CREATE DATABASE and CREATE ANY DATABASE permissions?

I am unable to find an authoritative answer. The best I can infer is that either (a) CREATE ANY implies I can create the database to be owned by another user, whereas with CREATE I can not, or (b) the original permission in the Sybase/early SQL Server days was CREATE ANY and CREATE is aliased for ANSI conformance.


texts: How to add generated column to db2 table?

texts:
I've got a table with the following:
Columns:
---
URL [nullable, e.g. "https://dba.stackexchange.com/"]
APPFK [nullable, e.g. 654654]
LINKTYPE ["Page", "Link", or "App"]

Check Constraints:
---
(URL IS NULL AND LINKTYPE <> "Link") OR (URL IS NOT NULL AND LINKTYPE = "Link")
(APPFK IS NULL AND LINKTYPE <> "App") OR (APPFK IS NOT NULL AND LINKTYPE = "App")

Since LinkType is calculable based on the nullability of URL and APPFK, I figured this was a perfect opportunity for a computed/generated/virtual column.
So, I tried to run:
alter table MYLIB.MYTABLE
add column testcol VARCHAR(4) generated always as
(
    CASE
        WHEN URL IS NOT NULL THEN 'LINK'
        WHEN APPFK IS NOT NULL THEN 'APP'
        ELSE 'PAGE'
    END
)

Annnd DB2 spat this back at me:

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token ( was not valid. Valid tokens: IDENTITY. Cause . . . . . :   A syntax error was detected at token (.  Token ( is not a valid token.  A partial list of valid tokens is IDENTITY.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token (. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

I tried running the first example in this DB2 documentation, but it gave me the same error.
What's going wrong, here? Am I doing something wrong? Is the documentation wrong? Is it just because our DB2 is outdated (we're running Version 7, Revision 1), and if so is there a workaround?


texts:
How to tell if a project needs a NoSQL database solution?

I think that SQL limits define it. If the project needs bigger database or bigger fields then it needs NoSQL.

Am I right?

I looked into NoSQL database limits. They are below:

For an example I'm using MongoDB assuming that DynamoDB has similar numbers.

Mongo can be installed on a number of computers/nodes. PostgreSQL does not provide built-in tool for sharding, however citus is around.

MongoDB supports databases up to 64 terabytes and document size is 16 megabytes.

MySQL has a database limit of 256 terabytes, 64TB the maximum size for a table and record limit of 4 gigabytes.

PostgreSQL has no limit on database (4 terabytes does exist somewhere for testing) and it has a limit of 1 gigabytes for the size of any one field in a table and again 64TB the maximum size for a table.

Is there anything else that is relevant for decision making process?


texts:
I have a Index Maintenance job for msdb database to rebuild those fragmented index in msdb database.

But I still found some backup related table in msdb database (e.g. backupmediafamily, backupset, backupfile) has high fragmantation.

After further checked found out it was skipped by the Ola Index maintenance command.

My question is how I can make ola defragment the index rather than I write another set of index maintenance just for msdb ?


texts: Downgrade SQL Server Enterprise Edition to SQL Server Standard Edition

texts:
There is a requirement in our project where they want to evaluate if it is possible to downgrade SQL Server from Enterprise to Standard edition and if a server can be decommissioned.

How can I identify which servers are suitable for downgrade?


texts: Compare Microsoft SQL server performance between virtual and dedicated server

texts:
I have a dedicated windows 2008 r2 server at the moment and I am thinking about migrating to a virtual server because of the costs. Now I know that SQL should be on a dedicated server, but the flexibility and costs are a great advantage for our situation.
I would like to compare both servers. I know it is hard because one is a production server with actual load and the other is a test server with one user.

Both run Microsoft SQL Server 2008.
dedicated has 12 GB ram, virtual has 12 GB ram
dedicated has a dual quad core CPU and virtual has 4 CPU's

both have the exact same database (I restored a backup from production on the virtual test)

I executed a heavy select query with a heavy view and made the Microsoft SQL Server Management Studio include the Client statistics.
That displays the Total execution time

Somehow the virtual shows a zero there now (since we added 2 virtual CPU's) but in the bottom right of the management studio it shows that the virtual server took 3 seconds and the dedicated server(production) took 2 seconds for the query to complete.

How can I make a good comparison between the virtual and production?

Edit: I have been told that the paravirtual setting in VMWare makes it better to compare the performance of the two instances.


texts: Sybase ASE to Microsoft SQL Server Replication

texts:
I am currently working on several projects to migrate legacy applications from a Sybase ASE (12.5 and 15.0) to a Microsoft SQL Server 2014 platform.

There will be a need to replicate data in both directions while the old and new applications are being developed.

I was looking for advice regarding how others have approached this problem.

Some ideas we're looking into:

Sybase Replication Server (Expensive)
Third party Apps (Not sure if it will be stable?)
Custom Stored Procedures in Sybase for Update, Insert and Delete and configure those to be called when the data comes in. (Reliability and difficulty of supporting)


texts: Very slow simple PostgreSQL query on RDS

texts:
I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram).

This is across a table of 4,152,928 rows..

select sum(some_field) c
from pages
where pages.some_id=123
and pages.first_action_at > '2014-01-01 00:00:00 +1000'

Total runtime: 45031 ms.
Locally, I have around 1.1million rows, and the same query takes about 450ms..

Here's the query plan, from explain:

Aggregate  (cost=475640.59..475640.60 rows=1 width=4)
   ->  Seq Scan on pages  (cost=0.00..475266.07 rows=149809 width=4)
         Filter: ((first_action_at > '2014-01-01 00:00:00'::timestamp without time zone)
                AND (some_id = 447))

Here's the response from explain analyze:

 Aggregate  (cost=475641.74..475641.76 rows=1 width=4) (actual time=42419.717..42419.718 rows=1 loops=1)
   ->  Seq Scan on pages  (cost=0.00..475267.22 rows=149810 width=4) (actual time=0.013..42265.908 rows=141559 loops=1)
    Filter: ((first_action_at > '2014-01-01 00:00:00'::timestamp without time zone) AND (some_id = 447))
    Rows Removed by Filter: 4011369

Total runtime: 42419.772 ms

For reference, 141559 rows are part of the sum().

The current indexes I have are:

:some_id
:some_id, :first_action_at

work_mem was previously set to 1 mb (RDS default). I've just changed this to 18 mb.

Edit: Seems to be resolved by upping work_mem as well as added the second index above, speed is now around 800 ms.


texts: How innodb alter table inplace algorithm inner work?

texts:
From the mysql doc  , it says :

  ALTER TABLE operations are processed using one of the following
  algorithms:

  COPY: Operations are performed on a copy of the original table, and
  table data is copied from the original table to the new table row by
  row. Concurrent DML is not permitted.

  INPLACE: Operations avoid copying table data but may rebuild the table
  in place. Typically, concurrent DML is supported for InnoDB tables by
  a DML log that is applied after table alterations are committed.

It just says mysql can dml when modify column , but how innodb alter table inplace algorithm inner work ?


texts: Creating SQL Server Performance Baseline Monitoring

texts:
In order to get an overview and compareable data my current task is to create a performance baseline to get some figures about the different productive SQL Server instances.

My thoughts are:

I want to use several DMVs
I want to include a profiler trace (incl. Exec. plans)
I want to include perfmon data

So what I try to achieve is a general performance monitoring startable and stoppable (also scheduable) that returns:

All information required to identify the success of ongoing performance optimization tasks
Couple of aggregated, simple figures that help to visualize the long-term progress .. esp. for management ;-)
Re-executable execution plans within profiler trace to compare individual queue changes and improvements by index optimization tasks

I found couple of information describing the creation of performance baselines. Most of them are either very complicated or focus only on one of the desired performance indicators (mostly perfmon data).

The most matching sample / description was the following: Creating a Performance Baseline for SQL Server

The question is:

Does anyone have experience creating this kind of performance monitor in a quickly doable manner?