texts: How to denormalize many-to-many relation without nested select?

texts:
I have the following schema:

house has many students, student has one house
gender has many students, student has one gender
game has many students. student has many games

For instance, consider

create table sex (type text);
insert into sex values ("wizard");
insert into sex values ("witch");

create table house (nature text);
insert into house values ("Gryffindor");
insert into house values ("Hufflepuff");
insert into house values ("Ravenclaw");
insert into house values ("Slytherin");

create table students (
  sid integer primary key autoincrement,
  name text,
  fk_type integer,
  fk_house integer,
  constraint valid_sex foreign key (fk_type) references sex (ROWID),
  constraint valid_grade foreign key (fk_house) references house (ROWID)
);
insert into students (name, fk_house, fk_type) values ("Harry", 1, 1);
insert into students (name, fk_house, fk_type) values ("Hermione", 1, 2);
insert into students (name, fk_house, fk_type) values ("Nymphadora", 2, 2);
insert into students (name, fk_house, fk_type) values ("Cedric", 2, 1);
insert into students (name, fk_house, fk_type) values ("Bridget", 2, 2);
insert into students (name, fk_house, fk_type) values ("Lupin", 2, 1);
insert into students (name, fk_house, fk_type) values ("Salazar", 4, 2);
insert into students (name, fk_house, fk_type) values ("Yurika", 4, 2);

create table games (game text);
insert into games values ("Quidditch");
insert into games values ("Aingingien");

create table likes (
  fk_student integer,
  fk_game integer,
  constraint strictM2M UNIQUE (fk_game, fk_student),
  constraint valid_student foreign key (fk_student) references students (sid),
  constraint valid_game foreign key (fk_game) references games (ROWID)
);

insert into likes values (1, 1);
insert into likes values (2, 1);
insert into likes values (2, 2);
insert into likes values (3, 1);
insert into likes values (4, 1);
insert into likes values (5, 1);
insert into likes values (5, 2);
insert into likes values (6, 1);
insert into likes values (6, 2);
insert into likes values (7, 1);
insert into likes values (8, 1);
insert into likes values (8, 2);

Now, I want to create a view that shows student roll number, name, gender, house, and games liked. I wrote a join query

select
  s.sid, s.name, xy.type, h.nature, l.fk_game
from
  students s
  inner join sex xy on s.fk_type = xy.ROWID
  inner join house h on s.fk_house = h.ROWID
  inner join likes l on l.fk_student = s.sid
order by
  l.fk_game;

But I want to see game's real names not ROWIDs, of course I can replace l.fk_game by (select game from games where ROWID = l.fk_game) but is it possible to get the same result without nested select?


texts:
I have a query which selects a max value over all the values of in a datetime column (dtimesent).

select dtimesent, max(ResponseTime) AS maxtime
FROM allrequests
where Request like '%Requestsa%'
group by dtimesent

I need to get the value of the request colmn for each max response time in each group by.
Is there any easy way to do this?  There can be multiple rows per dtimesent value so I can't just include the request column in the group by,

Many thanks,


texts: How to use transactions with SQL Server DDL?

texts:
I have a login table into which all inserts are done by a single stored procedure.

CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(1, 1) NOT NULL,
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log PRIMARY KEY CLUSTERED  (LogRefnr)
)
go

Create procedure DBO.LogInsert ( @Query varchar(255), @time datetime, @duration int, @SessinID int) as
begin
    Insert into LogTable ( LogRefnr, LogQuery, logQueryDuration, LogSessionID)
    Values  (@Query, @time, @duration, @SessinID);
end;
GO

Currently there are about 45500000 rows in that table and I want to direct logging to a different table.

My idea is to use the following script

begin Transaction

exec sp_rename LogTable, LogTableOld;

CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(46000000, 1) NOT NULL,            -- greater than select max(LogRefnr) from LogTableOld
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log2 PRIMARY KEY CLUSTERED  (LogRefnr);
)
go

sp_recompile LogTable;
go

Commit;

Does this work and has minimal impact to other procedures calling LogInsert?


texts:
I am looking at some implementations of Cassandra and HBase for medium-sized data sets (~1M resources) to be exposed to clients as graphs (via e.g. Tinkerpop). I would also like to store binaries in the same data stores. While it seems like both systems support storing large binaries one way o another (HBase via HDFS) I wonder what the performance implications would be for using these versus flat file storage. Are these systems designed to store binaries at scale, or are they more targeted at metadata storage? I am talking about 100s of Tb of binary data.

Thanks

.s


texts: SQL Server Fulltext search against big amount of search terms over some period of time

texts:
I've got a task where I must do a fulltext search in SQL Server 2008 against big amount of search terms (30-60 search terms) combined with OR. Some search terms may have partial match (asterisks at the end). So the fulltext query is something like:

'"term one" OR "term two" OR "term*" OR .... OR "term last"'

The query is pretty straightforward, I just select primary keys (which are integers) into a temporary table (for further processing):

insert into @processed_rules(rule_id, story_id)
select @rule_id, Stories.id
from Stories
inner join CONTAINSTABLE(Stories, (header, body), @formatted_keywords) as s
   on s.[KEY] > @oldest_id AND s.[KEY] = Stories.id

The main problems is a performance. The table contains several millions of records, each containing 2 fields which are subject to search: "header" with content of a size ~600-700 bytes and body with content of a size of several kilobytes.

The search shown above executes in ~1.5 seconds. I need to search against several search term sets in my stored procedure, so the overall time becomes something like ~10 seconds for me, which is unacceptable.

Despite the fact that the table contains records for past several years, I KNOW that I need results only from past 1-2 weeks maximum.

As you can see in the query I am trying to limit the output with some recent date/time range (s.[KEY] > @oldest_id where @oldest_id is a previously calculated record id for some moment in the past), but it doesn't seem to help much. Select TOP N also doesn't affect the execution time. As far as I understand that is because fulltext search first produces results for the whole table no matter what constraints are set in the query and only after that filters it against given constraints.

So I have several questions.

1) Is any way to restrict fulltext search only to recent weeks' records, in order to boost the search performance? I was thinking about partitioning table, indexed views and stuff like that but unfortunately didn't find in the Internet a recipe how to use them for my problem. Any advice in this direction?

2) The search for several search terms works pretty quickly for me (matter of several milliseconds), is it normal that when I give 30-60 search terms combined with OR the performance degrades so much?

P.S. The server has a lot of memory and disk space installed and it's multiprocessor, it has very high performance itself so it's hardly the bottleneck.


texts:
I am trying to set MySQL server on my Windows 7 64 bit machine. I have downloaded MySQL server and MySQLWorkbench.

I have started mysql service by command

C:\> net start mysql

as suggested here and checked that mysqld.exe process is running. Now when I do New Server Instance in Workbench, it is asking for the password. I never entered or set the password

My Question

Why New Server Instance in Workbench is asking for the password?


texts: How to specify with a parameter a field to extract from a RECORD variable

texts:
Here's a table:

CREATE TABLE t_heights (id INT, height REAL);
INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06);

Reading through the table rows with a FOR...LOOP and a RECORD type variable like this works:

CREATE OR REPLACE FUNCTION fnct_row_by_row (input_table regclass)
RETURNS VOID
LANGUAGE plpgsql
AS $$

DECLARE
    current_row RECORD;

BEGIN
FOR current_row IN EXECUTE 'SELECT * FROM '||input_table
        LOOP
            RAISE NOTICE 'field value: %',current_row.height; -- *<<< hardcoded field name*
        END LOOP;
END;
$$
;

Results:

SELECT fnct_row_by_row ('t_heights') ;
NOTICE:  field value: 53.63
NOTICE:  field value: 45.19
NOTICE:  field value: 47.06

However, the field extracted from the RECORD variable needs to have its name hardcoded.

How to specify dynamically the field to be extracted?

The following generates an error:

CREATE OR REPLACE FUNCTION fnct_row_by_row2 (input_table regclass, input_field_name TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$

DECLARE
    current_row RECORD;

BEGIN

FOR current_row IN EXECUTE 'SELECT * FROM '||input_table
        LOOP
            RAISE NOTICE 'specified field: %',current_row.input_field_name; -- *<<< field name from parameter*
        END LOOP;
END;
$$
;

Execution:

SELECT fnct_row_by_row2 ('t_heights','height') ;
ERROR:  record "current_row" has no field "input_field_name"
CONTEXTE : SQL statement "SELECT current_row.input_field_name"
PL/pgSQL function fnct_row_by_row2(regclass,text) line 10 at RAISE


texts: Case of irrecoverable error in transaction

texts:
According to an exam question,which may lead to an irrecoverable error:

1.A transaction writes a data item after it is read by an uncommitted transaction.
2.A transaction  a data item after it is read by an uncommitted transaction.
3.A transaction reads a data item after it is written by a committed transaction.
4.A transaction reads a data item after it is written by an uncommitted transaction.

The answer is given as the 4th option.I was thinking that the 3rd option should be the answer because the transaction has been committed so nothing can be done while rollback can be used in 4th.It depends on the transaction which is reading.


texts: Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results?

texts:
I have a database, call it maps, which contains 2 schemas, a & b. In each schema I have a number of tables: a.t1, a.t2, b.t1, b.t2 (plus others). The column sets on each of these tables is different, but there are a number of columns in common.

I have defined a materialised view, a.mv, which brings in the common columns of the 4 tables listed, including a geometry column (which represents a geographic outline).

I want to backup the current contents of the view so that I can restore it on another server. To do this, I use this pg_dump command:

pg_dump -h hostname -U username -t a.mv -f mv.sql maps

What I get as a result is the SQL to define the table, but no data. The view definitely has data in it, because I can select from it in PgAdmin (it was created multiple days ago and the underlying tables haven't been changed since)

I can dump the underlying tables, including data, with (eg)

pg_dump -h hostname -U username -t a.t1 -f t1.sql maps

but not the view. From the limited matches I've found with googling this, what I'm trying should work, but I'm wondering if the presence of a geometry column in the dump is causing the issue (or this might be a complete red herring). FWIW, the total data in the view is fairly substantial - probably around 1GB. However, I've dumped all the underlying tables in schema a successfully (including the 2 tables referenced by the view, and others), which was larger (1.5GB)

Any ideas what the issue could be here? On the face of it, what I'm trying to do should work, but just doesn't and with no indicated errors.


texts: MySQL error 1593 on replication between 5.0.92 and 5.5.42

texts:
I'm migrating a server to new hardware and trying to set up replication to keep the site current until we switch DNS. The intention is to enable two way replication to make sure that the servers stay in sync during the changeover but I'm concentrating on getting old (MySQL 5.0.92) to new (5.5.42) working at the moment. Both servers have unique IDs but on starting the slave I'm getting

Fatal error: The slave I/O thread stops because master and slave have  equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). The log position given is the start point from SHOW MASTER STATUS on the master.

There are several search results for the issue but the error seems to come up most for MySQL 5.6, which doesn't quite match, and there are even a couple of bug reports. I've tried stopping the slave on the master as suggested in one,I've purged the logs on the slave, the binlogs have the correct server ids in them.

My suspicion is that it's down to some kind of version mismatch and that it hasn't been reported because no-one is ever stupid enough to try replication between two such disparate versions but if anyone has any clues it would be much appreciated. In the meantime I'm going to work with pt-table-sync but it's not ideal.


texts: Test-Automation: Diff Data in Oracle Database - what are best practices?

texts:
We're re-writing a COBOL application in Java. We want to make sure, the applications behave the same, by validating our unit tests produce the same DB changes.

How can we easily diff the data in the DB to a given master-DB (e.g. the Database in which the COBOL program has written data). We want to do this in an automated way. Bonus: Is it possible to exclude several columns like date columns in a comparison?


texts:
Most of our automatically generated files contain integers, decimals, date, time, and sometimes varchar. This is instrumentation data. Since we will be uploading those files directly to a SQL Server database, we were trying to decide what is the most efficient file format for automatic generation from our instrumentation.

What is the most efficient file format for uploading data to a SQL Server database? I thought it would be Text format but when I did an experiment with CSV, CSV tends to take less space and is also easier for users to analyze.

Data file size would vary from 50K to 250K. Only a few range in MBs.

I am working on creating an application that would upload data into the db. We use LabView for our instrumentation data, so that is where we want the standardized file format.

I am leaning towards CSV. I do not have much experience with XML, so I might consider it that if the advantages outweigh those of CSV by a lot. Readability is my concern only in terms of quality control. There might be cases where the user would want to change or fix some data before they upload. Speed is also important but with such small file sizes, I am not too worried about it.


texts: migration of oracle xe 10 to xe 11 fails with wrong paths used in the import

texts:
I'm following the documentation to upgrade an OracleXE 10.2 database to OracleXE 11.2. The export succeeded without any problem, the import (on another machine with XE 11) fails though. The log file shows Oracle trying to put files in non-existant paths, and some paths still referring to XE 10:

;;;
Import: Release 11.2.0.2.0 - Production on Vr Mrt 20 13:26:58 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
ORA-39060: table(s) dropped because of conflict with master table
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/<replaced asterisks for stackexchange formatting> full=Y directory=DUMP_DIR dumpfile=DB10G.dmp logfile=expdpDB10G1.log
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file 'C:\OPT\ORACLEXE\ORADATA\XE\UNDO.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
Failing sql is:
CREATE UNDO TABLESPACE "UNDO" DATAFILE 'C:\OPT\ORACLEXE\ORADATA\XE\UNDO.DBF' SIZE 52428800 AUTOEXTEND ON NEXT 5242880 MAXSIZE 524288000 BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file 'C:\OPT\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\CRPTBL01.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

My OracleXE 11 is installed like this (by specifying c:\opt in the install wizard):

c:\opt\oraclexe\app\oracle
c:\opt\oraclexe\app\oracle\admin
c:\opt\oraclexe\app\oracle\diag
c:\opt\oraclexe\app\oracle\fast_recovery_area
c:\opt\oraclexe\app\oracle\oradata
c:\opt\oraclexe\app\oracle\product

So not only is the import tool trying to create files in a wrong dir:

ORA-01119: error in creating database file C:\OPT\ORACLEXE\ORADATA\XE\UNDO.DBF'

it is also refering to paths containing 10 versioning:

ORA-01119: error in creating database file 'C:\OPT\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\CRPTBL01.DBF'

I have no idea where Oracle is getting these paths from?


texts:
Happy new year everyone.  I'm hoping for some general guidance in the following situation...
I have an application that has been running for about 10 years.  The datastore is in mysql, (now on AWS Aurora).
Some of the tables that are in one-to-many relations are starting to have a larger number of rows:
Records (~1.4million rows)
        |
        V
    (1 to many)
        |
        V
SubRecords (~10million rows)
        |
        V
    (1 to many)
        |
        V
SubSubRecords (~22million rows)

There is not a lot of actual data being stored in these rows (ie subSubRecords is about 5gb in total), and the queries I run are very straightforwards, using indexed keys with no joins.  For example...
SELECT ... FROM Records WHERE id = ?;
SELECT ... FROM SubRecords WHERE recordId = ?;
SELECT ... FROM SubSubRecords WHERE subRecordId = ?;

So far, everything continues to be highly performant.
However, I'm starting to worry about how this design will hold up over time.  While it took 10 years to get to 22 million rows in SubSubRecords, the db is growing a lot faster now.  I wouldn't be surprised to see that table climb to 100million rows over the next 5 years, which feels like a lot.  And I'm not sure at what point it will become a problem.
I realize this is a rather broad question, and is situation-dependent.  But what what types of solutions are generally recommended in these cases?

Setup partitions? (The tables use foreign keys to enforce integrity
and my understanding is that this is incompatible with partitions.)

Convert the data in subRecords and subSubRecords to a json payload
and store that directly in a json column in the main records table?
(Same amount of data, but less rows, if it matters.)

Move to an entirely different db?  (Mongo?  I know nothing about it
but have heard is better at scaling in certain situations.)

Ignore it until it becomes an issue? :D

Any suggestions / pearls of wisdom from those who have wrestled with
similar problems is welcome.  Thanks (in advance) for your help!
Addendum:
As requested, here is the CREATE TABLE syntax for the above tables...
CREATE TABLE records (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    typeId TINYINT(1) UNSIGNED NOT NULL,
    userId INT UNSIGNED NOT NULL,
    updated TIMESTAMP DEFAULT NOW() NOT NULL,
    savename VARCHAR(100) NOT NULL,
    title VARCHAR(100) NOT NULL,
    instructions TEXT NOT NULL,
    FULLTEXT ftRecords(savename, title),
    PRIMARY KEY(id),
    FOREIGN KEY(typeId) REFERENCES recordTypes(id),
    FOREIGN KEY(userId) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET=utf8;

CREATE TABLE subRecords (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    recordId INT UNSIGNED NOT NULL,
    thumbnailId INT UNSIGNED NULL,
    sortOrder SMALLINT NOT NULL,
    enabled TINYINT(1) DEFAULT 0 NOT NULL,
    title VARCHAR(100) NOT NULL,
    instructions TEXT NOT NULL,
    parameters VARCHAR(500) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(recordId) REFERENCES records(id) ON DELETE CASCADE,
    FOREIGN KEY(thumbnailId) REFERENCES thumbnails(id) ON DELETE SET NULL
) ENGINE=InnoDB CHARACTER SET=utf8;

CREATE TABLE subSubRecords (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    subRecordId INT UNSIGNED NOT NULL,
    thumbnailId INT UNSIGNED NULL,
    sortOrder SMALLINT NOT NULL,
    caption VARCHAR(200) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(subRecordId) REFERENCES subRecords(id) ON DELETE CASCADE,
    FOREIGN KEY(thumbnailId) REFERENCES thumbnails(id) ON DELETE SET NULL
) ENGINE=InnoDB CHARACTER SET=utf8;


texts: Is it possible to pass parameters to pt-table-checksum using an options file?

texts:
We're using Percona's pt-table-checksum for MySQL replication integrity check.
Is it possible to pass some or all of the options using some sort of configuration file?

For instance, can we write the list of tables we want checked in a file?
I understand the manual does not talk about such a file: http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html


texts:
I installed Percona Server 5.6.12-rc60.4, when I want create table such as :

CREATE TABLE IF NOT EXISTS `mydb`.`table` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = XtraDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

this error occurred:

 #1286 - Unknown storage engine 'XtraDB'

How can I create a table with XtraDB engine?


texts: SQL Server 2008 - is "NT AUTHORITY\ANONYMOUS LOGON" login "mandatory"?

texts:
My spider senses are tingling when I found this "NT AUTHORITY\ANONYMOUS LOGON" account on a SQL Server logins. I tried to research the matter and I even have a book about SQL Server management. Also all the web searches I've done seem to relate only to login problems. The account is not on the local SQL express installation that I have, so I don't really know if someone added it there and if so, why.

Anyways my take on this is as follows: This account seems to allow login for every people that are in my domain. This just seems wrong and I want to shut it down. My concern is, that if I shut down this account, will applications start to fail login ? Is user/password login considered "anonymous" in this regard ? In other words, do other than AD-accounts need this for the login to work ?

I wish I could just test it, but it could do some damage in production environment.


texts: psql: Connection refused. pqAdmin: the database system is starting up

texts:
Windows Server 2012
Postgres 9.6

I made a replication with the master server. On the slave server service postgres is running, but in log files slave's server "FATAL:  the database system is starting up" message will continue.

when I try from the command line run the command

psql.exe -U postgres`

writes the following error:

  psql: ... Connection refused (0x0000274D/10061) ...

My postgresql.conf configuration is on the slave server:

listen_addresses = 'ip slave adress'
hot_standby = on
wal_receiver_status_interval = 0
hot_standby_feedback = on

My recovery.conf configuration is on the slave server:

standby_mode = 'on'
primary_conninfo = 'host=ip master server port=5432 user=postgres'
primary_slot_name = 'slot_1'
trigger_file = 'D:\Postgres Data\Start_master'

Could you please to fix this issue? I don't understand what could be the problem.
I've made shutdown the server, the problem is not resolved.


texts: View the output of Adam Machanic's sp_WhoIsActive

texts:
I'm attempting to look at the XML output by Adam Mechanic's sp_WhoIsActive stored procedure.

When I use a typical XML editor, all I see is the raw XML.  I'd like to be able to view the graphical execution plan the way it's shown in SQL Server Management Studio when you run a query including the actual execution plan.

I'm looking at the sample actual query plan demonstrated by Brent Ozar and want to see that style of plan on my own queries.


texts: What happens when you change a heap into a clustered table in SQL Server?

texts:
As I understand, a heap is an unordered object. When you insert a record, SQL Server use IAM page to get the pages that belong to this heap, and use PFS page to find a particular one which has enough space to accommodate this record and insert into it.
When you create a clustered index on it, it becomes a clustered table and the clustered index itself becomes the table. But as the clustered index and the original heap are two different structures, does SQL Server create a new structure (the clustered index) and moves everything from the help to the new structure and then drops the heap?
There are a lot of stuffs that can be defined on a table, like triggers, constrains, permissions etc. If my assumption is true, that means SQL Server also moves all these stuff to the new structure. I didn't find any related information in the documentation. Is my understanding correct?


texts: What will be with MySQL cluster if network problems between nodes?

texts:
I have 2 datacenters, each DC has MGT, SQL and Data nodes.

How will MySQL NDB Cluster work if there are network problems between 2 DC?

In this case 2 DC will work well, but they don't know anything about other node.

Who ll be the main and who ll be the reserve, how will they decide it?

Is there a quorum server for MySQL Cluster?


texts:
I see in the documentation that the database backup tools are divided into four categories: hot backups, cold backups, physical backups and logical backups.

I understand the the most important difference between cold and hot backup is that the latter can be done while the database is operating and receiving read&write queries (and the result will be consistent and atomic).

But then, how do I know if mysqldump has this "hot" aspect or "cold" aspect? The documentation just places it in the logical backup category and is not 100% clear about this (yes, it seems to mention that it uses the same snapshot of the DB at the beginning of the mysqldump procedure, which suggests it's definitely a HOT backup tool, but just wanted to double check here).

The tables use InnoDB engine.


texts: SQL+ Select highest 5 number of entries and display in ascending order

texts:
so far i am able to select the highest 5 entries by putting them in descending order

SELECT LAST_NAME, COMMISSION_PCT

FROM EMPLOYEES
WHERE JOBID = 'SALES' AND ROWNUM <= 5
ORDER BY COMMISSION DESC
/

This works fine for selecting the highest entries but how would i put them in ascending order? (WITHOUT CHANGING THE OUTPUT TO THE 5 lOWEST VALUES)
thanks


texts: Storing repetetive file metadata in databse

texts:
I have a situation where I want to store data like the following.

Path: String
isDirectory: Bool
isSymLink: Bool
linksTo: File
lastmodifiedData: Long
size: Long,
permissions: String

I will be storing this kind of data for many differet directory structures that very often have the same file metadata.

I do not want to have many repeated entries in the database, but I also do not want to have a 5 column Primary key.

I was considering a table, lets call it FILE_BASE

Path: String
isDirectory: Bool
isSymLink: Bool
linksTo: File

and another called FILE_INSTANCE

directory-root:String
fileBase:            // should this be an ID, or a multi-column FK
lastmodifiedData: Long
size: Long,
permissions: String

I am trying to understand the best way to go about creating a key for the first table that can be referenced in the second table.

If I use a composite key, then basically all of the columns in the first table are part of the primary key.

If I use surrogate keys, then I need to do a query before every insert to determine if the file(s) base that i want to add already exists in the table, and recover the surrogate ID. If I am dealing with thousands of files per insert, then this could get cumbersome quickly.

I could generate the ID's myself using some sort of UUID algorithm on a concatenation of the table columns, but that could potentially generate some pretty big ID's for many of the file bases, depending on their path in the file system.

I feel like this is just a trade-off between space and time complexity. I also feel like I am overthinking this, as I do not have a lot of database experience. Luckily this is just a test project that will likely get thrown away anyway, but I could use some input, or suggestions on reading so that I can understand what a good implementation would look like.

Is there something obvious that I am missing here?

--Edited: Added directory root identifier to second table.


texts: Increasing the decimal precision abruptly increased the size of table

texts:
I have a table like:

CREATE TABLE grid_rows(
    [grid_row_id] [int] NOT NULL,
    [grid_column_id] [smallint] NOT NULL,
    [decimal_val] [decimal](18, 6) NULL,
    [datetime_val] [datetime] NULL,
    [integer_val] [int] NULL,
    [string_val] [varchar](1024) NULL
)

This table has some  1,037,560 rows

exec sp_spaceused "grid_rows" gives:
rows         reserved    data
1,037,560    461,768KB  302,648KB`

After changing the precision from (18, 6) to (24,6) i.e
ALTER TABLE grid_rows ALTER COLUMN decimal_val decimal(24, 6)

exec sp_spaceused "grid_rows" gives:
rows         reserved     data
1,037,560    641,352KB  560,832KB

The space allocated by decimal(18,6) is 9 bytes and that of (24, 6) is 13 bytes. MSDN reference

The reserved space has increased by around 179,584 KB and data space by 260,000KB. Shouldn't it be increased by 1,037,560 * 4/1024 = 4052 KB


texts:
I need some enlightenment on a MySQL query issue:

I recently found out that a select query is taking differently long depending on how large ibdata1 is (at least that's how it seems to me).

To be specific: When I empty the whole table that is being fetched and import only a few entries, the fetch time is how it's supposed to be (0.03 sec). After importing more and more data, the query becomes more and more slow. What's strange is the fact that after deleting entries from the table, it still is running slowly. After deleting ibdata1 and importing all entries, the query suddenly works fine again (0.03 sec).

It probably helps to know that the table is quite big, but not in regards of entries (only 14k), but storage size (all in all the table size is about 2.3 GB because there is a BLOB field). I guess this has something to do with it, since it lets ibdata1 grow pretty fast.

All tables are InnoDB. Any suggestions on why this happens and how to prevent it? I don't want to delete ibdata1 everytime I need to reimport all entries of this table (which sometimes happens because I'm working in a DEV environment).

Here are some more infos. I changed some names (table, field) to not give away too much:

my.cnf:

[client]
port            = 3306
socket          = /home/user/mysql/mysqld.sock

[mysqld]
port            = 3306
#bind-address   = 0.0.0.0
socket          = /home/user/mysql/mysqld.sock
datadir         = /home/user/mysql/data
log-error       = /home/user/mysql/logs/mysqld.err
#skip-locking
key_buffer = 16M
max_allowed_packet = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names=1

show create table:

 CREATE TABLE `table_name` (
  `ID_0` varchar(255) NOT NULL,
  `VERSION` bigint(20) NOT NULL,
  `PROTOCOL` longblob,
  `START_TIME` datetime NOT NULL,
  `END_TIME` datetime default NULL,
  `STATUS` varchar(255) NOT NULL,
  `COMMENT` varchar(255) default NULL,
  `ID_1` varchar(255) NOT NULL,
  `ID_2` varchar(255) NOT NULL,
  `ID_3` varchar(255) default NULL,
  PRIMARY KEY  (`ID_0`),
  KEY `FK635C17D0AE6C617C` (`ID_3`),
  KEY `FK635C17D022EA191B` (`ID_1`),
  KEY `FK635C17D0109EAE92` (`ID_2`),
  CONSTRAINT `FK635C17D0109EAE92` FOREIGN KEY (`ID_2`) REFERENCES `table_2` (`ID_2`),
  CONSTRAINT `FK635C17D022EA191B` FOREIGN KEY (`ID_1`) REFERENCES `table_1` (`ID_1`),
  CONSTRAINT `FK635C17D0AE6C617C` FOREIGN KEY (`ID_3`) REFERENCES `table_3` (`ID_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

show table status:

| Name                     | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment                                                                          |
+--------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+
| table_name | InnoDB |      10 | Compact    | 110937 |          23361 |  2591604736 |               0 |      2162688 |         0 |           NULL | 2015-12-30 03:18:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 91136 kB; (`ID_3`) REFER `table_3`(` |


texts: SQL 2016 AlwaysOn Database Health Level Detection

texts:
I am researching SQL Server 2016 and AlwaysOn.

One of the new features which sounds awesome on paper is "Database Health Level Detection". From my understanding if a database in a AlwaysOn Group enters a state other than ONLINE than the databases would fail over to the secondary replica.

This got me pondering a few things and I can not find an answer yet and do not have our test lab setup yet either.

If I restore a database, the state of the database will be RESTORING; will that trigger a failover event?

Also with AlwaysOn there is another feature called "Automatic Page Repair".  If "Automatic Page Repair" fails, my understanding is that the database enters a Suspended state, which would make me expect the databases to failover as well.

So, is "Database Level Health Detection" only recommended if your cluster is only being used for one application?  For example, one of our clusters is going to host about 50 different databases used by our Intranet; if one of those databases needs to be restored or has corruption, I would not want the other 49 to have a fail over event and a small outage, or would that not be an issue?


texts: Linked Server transaction stopped by MSDTC

texts:
We've recently installed the newest Microsoft OLE DB Driver for SQL Server (msoledbsql) as it now supports multi-subnet failover capabilities (detailed here: - https://blogs.msdn.microsoft.com/sqlnativeclient/2018/03/30/released-microsoft-ole-db-driver-for-sql-server/)

However we are seeing an issue with queries using linked servers.

When we run a simple query through linked server "test": -

select * from test.master.sys.databases;

The error returned is: -

Msg 8522, Level 16, State 3, Line 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

I've checked the settings for MSDTC on both servers (source and target) and they seem to be correct.

I've seen online that this can be fixed by explicitly opening a distributed transaction:-

BEGIN DISTRIBUTED TRAN
select * from test.master.sys.databases;
ROLLBACK

But that errors out with: -

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Has anyone seen this before? Any help or advice would be greatly appreciated.


texts: Postgres date_trunc quarter with a custom start month

texts:
I'm trying to create quarterly average for player scores, however the default behaviour of postgres date_trunc('quarter', source) is that it starts first quarter with YYYY-01-01.
Is there any way possible or workaround I can do to say that the first month of first quarter is for instance September? So instead of the traditional: Q1: 1-3, Q2: 4-6, Q3: 7-9, Q4: 10-12
I want to be able to specify which month is the start of Q1, so if I say September it should become: Q1: 9-11, Q2: 12-2, Q3: 3-5, Q4: 6-8
Here is how I make a standard quarterly score average with default quarter.
SELECT id,
       name,
       date_trunc('quarter', date) AS date,
       AVG(rank) AS rank,
       AVG(score) as score,
       country,
       device
FROM player_daily_score
GROUP BY id, name, 3, country, device
ORDER BY 3 desc;

I'm open for all suggestions to make this work.


texts: Grant dbcreator only for databases matching prefix

texts:
In Microsoft SQL Server 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.

Is it possible to do this at the database level using a built-in feature or a stored procedure?


texts:
I was told from a SQL-DBA that there is a huge gap in performance between these statements, both statements are called from a stored procedure:

-- Procedure that select from table directly
Select * From [Table] Where condition

-- Procedure that select from a view
Select * From [View] Where condition

The SQL-DBA told me that selection from table is faster because when you select from a View, all data will be selected first then filtered, but from table it will apply filtration directly. I am a developer and I don't have much knowledge in database so I was hoping for some expert to advice me in this matter.

Upon answering this question, I think I will be able to know weather using Views is a best practice or not.


texts: MySQL high performance difference between two similar databases

texts:
I'm currently upgrading from TecDoc 2015 Quarter 2 database to TecDoc 2015 Quarter 4 database.

Q2 Database:
240,838,107 rows    InnoDB  utf8_general_ci 23.2 GiB

Q4 Database:
265,508,380 rows    InnoDB  utf8_general_ci 23.8 GiB

Indexes and table structures are identical.

The main difference lies in running query below:

SELECT DISTINCT ART_ID AS AID, ART_ARTICLE_NR AS ARTICLE, SUP_BRAND AS BRAND,
        SUP_ID AS BID, DES_TEXTS.TEX_TEXT AS TD_NAME
FROM LINK_GA_STR
    INNER JOIN LINK_LA_TYP ON LAT_TYP_ID = 243 AND
    LAT_GA_ID = LGS_GA_ID
    INNER JOIN LINK_ART ON LA_ID = LAT_LA_ID
    INNER JOIN ARTICLES ON ART_ID = LA_ART_ID
    INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ART_COMPLETE_DES_ID AND
    DESIGNATIONS.DES_LNG_ID = 35
    INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
    INNER JOIN SUPPLIERS ON SUP_ID = ART_SUP_ID
WHERE LGS_STR_ID = 42189;

Q2 results: Returned 833 rows, 0.749 seconds.

Q4 results: Return 836 rows, 3.72 seconds.

As you see, there is huge difference. Same queries with different parameters on the Q4 database can take upto 15 seconds.

I've tried tweaking some setttings in my.cnf, but nothing seems to help.

EXPLAINS:

Quarter 2

Quarter 4

SERVER:
GCC: n1-standard (vCPUs: 1, RAM: 3.75 GB) with 150GB persistent disk


texts:
There is MariaDB 10.1.20 (also checked in MySQL CE 5.7 and Percona Server 5.7)

1) Create table:

CREATE TABLE test1 (
     id INTEGER NOT NULL DEFAULT 0,
     flag enum('y','n') NOT NULL DEFAULT 'n',
     KEY(id), KEY(flag)
) ENGINE=InnoDB;

2) All index_merge_xx seems enabled:

MariaDB [db1]> show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,
index_merge_intersection=on,index_merge_sort_intersection=on,
engine_condition_pushdown=off,index_condition_pushdown=on,
derived_merge=on,derived_with_keys=on,firstmatch=on,
loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowi    d_merge=on,partial_match_table_scan=on,
subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,
outer_join_with_cache=on,semijoin_with_cache=on,
join_cache_incremental=on,join_cache_hashed=on,
join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,
extended_keys=on,exists_to_in=on,orderby_uses_equalities=off
1 row in set (0.01 sec)

3) Do query:

MariaDB [db1]> EXPLAIN SELECT * FROM test1 WHERE id = 1 AND flag = 'n';
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|    1 | SIMPLE      | test1 | ref  | id,flag       | id   | 4       | const |    1 | Using where |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

In more detailed form:

MariaDB [db1]> EXPLAIN FORMAT=JSON SELECT * FROM test1 WHERE id = 1 AND flag = 'n'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "test1",
      "access_type": "ref",
      "possible_keys": ["id", "flag"],
      "key": "id",
      "key_length": "4",
      "used_key_parts": ["id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "(test1.flag = 'n')"
    }
  }
}
1 row in set (0.00 sec)

Why SELECT ignores the second index and uses the first only?


texts: How to display all the dates in the date-range having group by dates?

texts:
Suppose I have a table called “products”:

Productive product-name   product-quantity  created-date
111            ABC          10               1/10/2018
222            XYZ          20               1/10/2018
333            PQR          30               1/11/2018
444            MNC          40               1/15/2018
555            DEF          50               1/11/2018

If I run this query:

Select convert(date,createddate,101), count(product-quantity) from products
Where created-date > 1/9/2018 and created-date < 1/16/1018
group by convert(date,createddate,101)

Which will give me output such as

created-date.  product-quantity
1/10/2018            30
1/11/2018            80
1/15/2018            40

I want to achieve:

created-date.  product-quantity
1/10/2018            30
1/11/2018            80
1/12/2018            00
1/13/2018            00
1/14/2018            00
1/15/2018            40

How can I do it?


texts: How to simulate a situation when non-empty Transaction Log file will be added to the SQL Server Full Database backup file

texts:
I created a test database "StoredData" with a single table:
CREATE DATABASE StoredData
 ON  PRIMARY
( NAME = N'StoredData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StoredData.mdf')
 LOG ON
( NAME = N'StoredData_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StoredData_log.ldf')
GO

ALTER DATABASE [StoredData] SET RECOVERY FULL

GO

USE StoredData
GO

CREATE TABLE dbo.Users
(
    Id INT NOT NULL,
    Name NVARCHAR(50) NOT NULL
)

Then I inserted 3 records into that table:
INSERT INTO dbo.Users (Id, Name)
VALUES (1, 'John'),
       (2, 'Giovanni'),
       (3, 'Anna')

After that I run this query to have some active transactions:
DECLARE @i INT = 0;

WHILE @i < 1000
BEGIN

    IF(@i%10 = 0) BEGIN TRAN

    INSERT INTO dbo.Users
    SELECT TOP 10 * FROM dbo.Users

    WAITFOR DELAY '00:00:01';

    IF(@i%10 = 9) COMMIT

    SET @i = @i+1
END

During the last query execution I'm trying to do full database backup:
BACKUP DATABASE StoredData
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\StoredData_FullRecovery_Uncompressed.bak'
WITH FORMAT, NO_COMPRESSION

Then I'm looking into the backup file content:
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\StoredData_FullRecovery_Uncompressed.bak'

And I'm getting zero log file inside backup:
LogicalName:       StoredData_log
BackupSizeInBytes: 0


texts: 'text search dictionary "unaccent" does not exist' entries in postgres log, supposedly during automatic analyze

texts:
I have many such entries in the postgresql main log, ever since upgrading to Postgres 10:

2018-03-28 08:51:00.281 CEST [97547] ERROR:  text search dictionary "unaccent" does not exist
2018-03-28 08:51:00.281 CEST [97547] CONTEXT:  automatic analyze of table "dbname.public.periodical"

I do use unaccent in many indexes in all tables (by making unaccent immutable (I know it is not the recommended way, but it is so historically)).

If I manually run vacuumdb dbname --table periodical or VACUUM periodical or VACUUM ANALYZE periodical, it does not generate such an error (neither on the command line, nor in the log).

Does anyone have a tip how I could stop these messages and what their real cause may be?

PostgreSQL version: 10.3 (Debian 10.3-1.pgdg80+1)


texts: Unique index on columns which are allowed to be NULL

texts:
I'm having a little problem right now, we use this table in our database:

CREATE TABLE offer (
   offer_id   serial PRIMARY KEY
 , product_id int NOT NULL REFERENCES product
 , price_old  numeric(10,2);
 , price      numeric(10,2);
 , price_alt  text          -- overrules price if present
 , valid_from timestamp NOT NULL
 , valid_to   timestamp     -- optional
 --   more attributes of the offer
 , CONSTRAINT some_kind_of_price_required
      CHECK (price IS NOT NULL OR price_alt IS NOT NULL)
);

And we use a unique index which doesn't work:

ALTER TABLE offers ADD CONSTRAINT offer_unique_index
UNIQUE(product_id, price_old, price, price_alt, valid_from, valid_to);

It doesn't work because price_old, price and price_alt can be NULL.... It's possible that only price is filled, only price and price_old or only price_alt...

I don't know a good way to fix it. Probably multiple Unique indexes, but I cannot figure out right know how many and how to construct them. Can anybody help me out?


texts: How to get goup concat of count value using sub query

texts:
I'm trying to get group concat  of count value
here table below I want this structure:

| y    | m          | monthName                               | RowCount  | model |
| 2019 | 2,6,8,9,11 | February,June,August,September,November | 2,1,1,1,1 | Magnum |
| 2019 | 5          | May                                     | 2         | Stella |

But I got this structure :

| y    | m          | monthName                               | RowCount  | model |
| 2019 | 2,6,8,9,11 | February,June,August,September,November | 6         | Magnum |
| 2019 | 5          | May                                     | 2         | Stella |

Here my Query :

    set @p_dealer = 0;
    set @p_employee = 0;
    select YEAR(cast(c.entryDate as date)) AS y,
        group_concat(distinct MONTH(cast(c.entryDate as date))) AS m,
        group_concat(distinct MONTHNAME(cast(c.entryDate as date))) as monthName,
        COUNT(1) as RowCount,
        (select st.sModel from tbl_stock st where st.sId=c.sId limit 1) as model
        from tbl_customer c
        where YEAR(cast(c.entryDate as date))=2019 -- YEAR(cast(now() as date))
        and ifnull((case @p_dealer when 0 then @p_dealer else c.did end),0)=@p_dealer
        and ifnull((case @p_employee when 0 then @p_employee else c.eid end),0)=@p_employee
        GROUP BY model;

My table information is:
stock table (tbl_stock)

and customer table (tbl_customer)


texts: Why does SQL Server 2005 only use an IS lock in this scenario?

texts:
I am attempting to examine the locking behind different queries but with the same output to avoid deadlocking situations.

I have the following code:

SELECT MIN(ID),
    TextData
FROM
    PKKEYTESTER
GROUP BY
    TextData
ORDER BY
    TextData DESC

The locking for the code is as follows:

IS Lock:Acquired
IS Lock:Acquired
IS Lock:Released
IS Lock:Released

At no point does it get a S lock.

So why does it intend to get a shared lock but actually doesn't?

SQL To create and insert base data into table:

CREATE TABLE [dbo].[PKKEYTESTER](
    [ID] [int] NOT NULL,
    [Textdata] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO PKKEYTESTER
VALUES (1, 'A')

INSERT INTO PKKEYTESTER
VALUES (2, 'B')


texts: What is the Maintenance DB asked by pgAdmin?

texts:
pgAdmin dialog for adding new database connections asks for a "Maintenance DB".
In order to be able to connect, I set it to the database I want to connect (and also, I have the rights to connect).

So, why is it named "Maintenance DB" instead of "DB" or "Database"?.


texts: Join Fact table to SCD Type 2 ... how to write query? (SQL Server)

texts:
I can't seem to find this simple fact anywhere.

I have a fact table like such in SQL server.

fact_picked
Emp_Name     Date     Apples_Picked
John        May 1 17    100

And a type 2 dimension table like this

dim_company
Emp_Name   Company     Effective_Since
John      Blue_Apples      June 1 2015
John      Apple_N_Stuff    Jan 1 2016
John      Da_Big_Apple     March 17 2017
John      Big_Tech         October 20 2017

How would I join the fact to the dimension table so I know which "company" picked the 100 apples?

In this case, logically, given the data, it's 'Da_Big_Apple' ... since John began working there in March 17 until Oct 2017, in which his apple picking task took place.

How do I do a join of these tables though? (assuming thousands of records).

I just get stuck. I know I should do something like

Select fp.emp_name, fp.date, fp.apples_picked
from fact_picked fp
left join dim_company dc
on fp.emp_name = dc.emp_name
and fp.date > dc.effective_since .... ???

I guess I'm not really sure. I guess I can use a view to convert the type 2 to a type 4 (with an end date in the table). Then it's simpler. Make sure the fact date is greater than the start date, yet before the end date. But is that the most elegant solution?


texts: Find Source & Details of APL Heap Table Insert

texts:
I have been running system monitor(sysmon) on Sybase ASE 15.7(SP139) on HP-UX(Itanium) with below command:

sp_sysmon "00:10:00"
go

Observed that number of inserts are very high in any time of a day, unable to find the source of it and the details e.g.- table name, database name, program name etc. below is sample output from sysmon in the early morning when there is no load:

Transaction Profile
-------------------

  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
    Committed Xacts                  10.7           n/a        6390     n/a

  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
  Inserts
    Fully Logged
      APL Heap Table              58665.7        5508.5    35199448     100.0 %
      APL Clustered Table             0.0           0.0           7       0.0 %
      Data Only Lock Table           13.2           1.2        7918       0.0 %
      Fast Bulk Insert                0.0           0.0           0       0.0 %
      Fast Log Bulk Insert            0.0           0.0           0       0.0 %
    Minimally Logged
      APL Heap Table                  0.0           0.0           0       0.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ----------
  Total Rows Inserted             58679.0        5509.8    35207373     100.0 %

It looks like inserts are happening in tempdb however these many inserts are a bit bothering, Appreciate to have expert advice on this.


texts: Can't uninstall Microsoft SQL Server 2016 RC3

texts:
Microsoft SQL Server 2016 RC3 was installed on our server (not by me) Windows Web Server 2008 R2 64bit.  Visual Studio 2015 Community was also installed.  When I went to work on the server I could not connect to the MS SQL Server, so I went to see if the service was running and the only MS SQL service in the services manager was:

  SQL Server VSS Writer Provides the interface to backup/restore
  Microsoft SQL server through the Windows VSS infrastructure.

None of the core services were installed.  When I looked in the start menu all the Microsoft SQL Server 2016 RC3 programs were installed.  When I ran the Installed SQL Features Discovery Report, it showed MS SQL 2016 installed, but no instances.

So I uninstalled VS, and attempted to uninstall MS SQL Server.  I uninstalled the main program and all the components and then attempted to uninstall Microsoft SQL Server 2016 RC3 Setup, but it pops up the following message:

  Warning 26003. Microsoft SOL Server 2016 Ro3 Setup Support Files
  cannot be uninstalled because the following products are installed:
  SOL Server 2016 Ro3 Batch Parser SOL Server 2016 Ro3 SOL Diagnostics
  SOL Server 2016 Ro3 XE vent SOL Server 2016 Ro3 Shared Management
  objects

  Extensions SOL Server 2016 Ro3 SOL Diagnostics SOL Server 2016 Ro3
  Batch Parser SOL Server 2016 Ro3 XE vent SOL Server 2016 Ro3 Shared
  Management objects

Yet none of these show in the Add/Remove Programs. (I refreshed it several times)

I then ran the Microsoft SQL Server 2016 Release Candidate 3 (RC3) Setup Discovery ReportMicrosoft SQL Server 2016 Release Candidate 3 (RC3) Setup Discovery Report, and it just has a single line with None in all the boxes.

I then attempted to reinstall Microsoft SQL Server 2016 Release Candidate 3, by running setup from the downloaded ISO, and Installation/New SQL Server stand-alone installation or add features to an existing installation/

I am trying to install the evaluation version and that is what was previously installed, when I get to the Feature Selection of the wizard it shows the following:

  Shared Features/Reporting Services - SharePoint Shared
  Features/Reporting Services Add-in for SharePoint Products

So none of the core services are available to install.

The ISO I am installing from is: SQLServer2016RC3-x64-ENU.iso, downloaded from Microsoft, and I have used the exact same ISO to install on a Windows 10 machine with VS 2015 installed with no problems and did not have this issue.

How can I resolve this and get Microsoft SQL Server 2016 Release Candidate 3 installed and running?


texts: Drawing an entity-relationship diagram for a movie sales business domain

texts:
I want to keep track of movies that exist in different formats (for example, DVD, Bluray, etc.). The price for each format differs from the others as well as the quantity of each format, so I came up with the entity-relationship diagram (ERD) shown below:

Is this correct from a design perspective? Does this implies redundancy? I don't understand how will this be retained in a table when implemented in a relational database.

Would it be better to do it like in the diagram that follows:

Additional information

Here's more descriptive information about what I want to store at this point of the design:

I want to store information about sales.
For each movie that exists in the company I need to keep format, price and stock quantity.
I will also need to maintain customer information with a unique id, name, surname, address, movies that he/she has already bought and his/her credit card number.
Finally, I will have a basket that temporary keeps items (let's suppose that other items exist apart from movies) that the customer wants to buy.


texts: Transform a varchar field with #-delimiters into multiple rows

texts:
I am working with a database where some data (integer values representing selected options of a dropdown menue) is saved as a string (varchar) inside only one table column. In products_table (let's say), there is a product_id column mapped to a selected-options column. In the latter, data is saved as #3#9#15# where 3, 9 and 15 are IDs of dropdown menue options that have been selected by a user. There is another table (let's say option_table) where each of these options with IDs 1 to 15 are given more attributes (like a label and a weight).
For statistical reasons, I want to create a MySQL view with the columns selected option and number of products (the number of products where this option has been selected). To get there, I need the single values from #3#9#15#, that is, explode the string somewhere on the way.
Now I read that this form of saving data is not encouraged, which is why there is no built-in explode/split function for strings in MySQL. But I cannot change the data format, which has been determined inside a huge software. I didn't make this design choice, but I have to somehow work with it.
Working with the built-in function substring_index() is no choice because the number of selected options saved in this field varies, from 0 to 15. Also, the maximum number is not fix, as new options may be created from time to time.
I tried writing an own procedure that will take one number from #3#9#15# (for example, by repeatedly trimming the # delimiters) and create a new row with it. The procedure should access the value with a SELECT statement, create a new view with CREATE OR REPLACE VIEW, and then insert a new row with only the first option (3) option with INSERT INTO `viewname`(`product_id`,`option`) VALUES(@productID, SUBSTR(@options,2,1)); where @options == #3#9#15# for example. I tried writing the procedure for only the first option at the beginning, but it already failed at this stage.
Other ideas I had included trying to convert the string @options into a SET datatype, so I can use find_in_set() function. I also read this question and this article but found them very hard to understand.
Having only basic knowledge of MySQL, is there some way for me to create this view? I would be very thankful for any hint in which direction I should go on researching.
Edit: I have server version 10.3.28 - MariaDB Server. Do you need more information on this?
Edit2: Due to an (ongoing) bug in phpMyAdmin, I could not use SQL WITH statement (see here).
Edit3: It turned out that the delimiter used by the software was not actually a # sign, but was only displayed by phpMyAdmin as such. It was instead the ASCII control character 0001 or "^A".


texts: Server requested authentication method unknown to the client [mysql_old_password]

texts:
Warning: PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password]
SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

I get the error above while trying to connect to my MySQL database server on Hostgator, using a PHP script on my local server. I understand it's a password hashing issue usually between pre MySQL 4.1 and MySQL 4.1+.

I've changed my local server to use the old_password following the instruction I found here.

After changing it to 16 bit hash, I still can not connect, and the error message remains the same. And more so, I can no longer login to phpmyadmin. I'm using XAMPP on windows. Please any suggestions on how to solve these problems?


texts: Inserting rows from one multimillion rows table to another

texts:
I have a transactional (OLTP) database with tables that have about 100 million rows. I have on another server a data warehouse with similar size tables. I would like to update the data warehouse close to real time.
Data is entered into the transactional database at a rate of thousands of rows within minutes by thousands of users.
I wonder if using a trigger to insert, possibly into a staging database on the same server where the transactional database resides is a good option that will not hit performance of the transactional database.
Or, should I use an ETL process?
Is using CSV files a preferred (faster, less load on the source database) approach, compared to inserting into database tables?
Any other ideas?
Thank you very much.


texts:
I was testing SQL Server indexes and found very strange behavior. Here is my code:

DROP TABLE IF EXISTS  dbo._Test
DROP TABLE IF EXISTS  dbo._Newtest
GO
CREATE TABLE _Test(
ID INT NOT NULL,
UserSystemID INT NOT NULL,
Age INT
)
GO
INSERT INTO dbo._Test
        ( ID, UserSystemID, Age )

SELECT TOP 10000000 ABS(CHECKSUM(NEWID())) % 5000000, ABS(CHECKSUM(NEWID())) % 2, ABS(CHECKSUM(NEWID())) % 100
FROM sys.all_columns
CROSS JOIN sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c

; WITH cte AS (
SELECT ID, UserSystemID,  age, ROW_NUMBER() OVER(PARTITION BY ID, UserSystemID ORDER BY GETDATE()) rn
FROM dbo._Test
)

SELECT cte.ID ,
       cte.UserSystemID ,
       cte.Age
INTO _newTest
FROM cte
WHERE cte.rn = 1

CREATE UNIQUE NONCLUSTERED INDEX  IX_test ON dbo._NewTest(ID, UserSystemID) INCLUDE(age)
GO
ALTER TABLE dbo._NewTest ADD CONSTRAINT PK_NewTest PRIMARY KEY CLUSTERED(UserSystemID, ID)
GO

At this point, I have two indexes on the same table and on the same columns. First one is nonclustered and the second one is clustered. The Id column is more selective (about 5000000 unique values) and UserSystemID is not (two unique values).

Then I run the following query to test which index is used:

SELECT id,  UserSystemID, age
FROM _NewTest
WHERE id = 1502945
AND UserSystemID = 1

It seeks the clustered index. You can see the plan here.

The question is why SQL Server prefers the clustered index instead of the unique nonclustered index.

My leading column of clustered index is much less selective than the other unique nonclustered index. So I expect that the performance must be worse with clustered index but in practice it is not.


texts: update trigger insert only if value is different

texts:
i have some gps modules that send data to my database. On update of the last package sent i want to compare new values with old values and insert into a new table only those values that are diferent.

CREATE TRIGGER upd_check AFTER UPDATE ON coordonates
    FOR EACH ROW
    BEGIN
       IF (OLD.lattitude <> NEW.lattitude ) or (OLD.longitude<> NEW.longitude)
       OR (OLD.battery_lvl <> NEW.battery_lvl) or (OLD.status <> NEW.status)
        THEN
         INSERT INTO LogTable(ID, lattitude,longitude, battery_lvl,status)
         VALUES (NEW.ID, NEW.lattitude,NEW.longitude,NEW.battery_lvl,NEW.status);
       END IF;
    END;

but this is not good because i want to insert those new values only if they are different from old value, right now i insert all of them ...


texts: Migrating SQL Server 2008 R2 to new server

texts:
I need to migrate an instance of SQL Server 2008 R2 (20+ databases) to a new server this weekend. The new server is exactly the same as the old server in all respects, including SQL Server version numbers. The only difference is that the new server has a lot more CPU power, RAM and harddrive space.

I've already successfully performed a test migration using my automated backups, but I only restored the user databases. This meant that I had to do a lot of work afterwords to recreate logins, jobs, maintenance plans, settings etc. I have all of this scripted and backed up, but its a lot of extra work that I'd like to skip in order to save time during the actual live migration. I only have a couple of hours to get it done. I need more time for testing and configuring the systems using the databases.

One of the guys here pointed out that as all the SQL Server version numbers are the same, I could just shutdown both instances of SQL Server and then copy all the *.mdf (data) and *.ldf (log) files to the new server, including those for the system databases, and then just start SQL Server again.

My questions:
- Is this wise / recommended?
- Will it work?
- Any foreseeable problems?

I'm going to go do a test of this theory now, but I'd like to know what you guys think.


texts: Why am I connecting securely to MySQL when I don't have client certificates?

texts:
I don't understand the MySQL documentation for encrypted connections. The server is configured with certificates, and unless I am misreading it, the documentation says that the client must have certificates as well.

But, if I start the client with 'mysql --ssl' then \s reports that the connection is SSL encrypted using DHE-RSA-AES256-GCM-SHA384 even though I haven't used any certificates with the client. /etc/my.cnf.d/client.cnf does not have any certificates in the [client] section, and I don't have a .my.cnf

How is that possible that I am connected with SSL? Is it not really encrypted?


texts: Delete duplicate with same ID PHP/PDO/MYSQL

texts:
I'm new to databases and I need your help I'm working on a website and I have few problems. I'm using PDO.
There is a user table, with unique ID, name and password. User enters his username and password and is put to the next page where he enters time, date and a third parameter. Those 3 parameters are put into anoter database with user's ID (ID is set with PHP's session). This table has no primary key, since user can insert multiple entries.
Sounds great, but... I want to delete duplicates, or even better prevent user to insert a duplicate.
I tried with this:

$alter = "ALTER IGNORE TABLE data ADD UNIQUE INDEX (ring_time, ring_date, bell_mode)";
$query = $db->prepare($alter);
$query->execute();

try {
    $insert_s = "INSERT INTO data(id, ring_time, ring_date, bell_mode) VALUES(?, ?, ?, ?)";

    $query = $db->prepare($insert_s);

    $query->execute(array($_SESSION['id'], $_POST['ring_time'], $_POST['ring_date'], $_POST['mode']));
} catch(PDOException $e) {
    echo "
Already entered.
"
}

This works somehow right, but if different user inserts same values, it catches the error, even though his ID is different. I'm pretty sure that $alter statement is wrong.
The other thing is: I want to delete every entry which date (the table's date entry is 'date' type) is past today's day. I tried with:

DELETE FROM data WHERE CURDATE() > ring_date";

but with no success, it deletes stuff, but not the right way.


texts: (Ola Hallengren) Deleting log backups older than last FULL backup

texts:
When I deploy Ola Hallengren's backup script into my servers, I always set the @CleanupTime parameter for log backups to zero. This way, every time the log backup job runs, it checks for log backup files older than the last full backup and deletes them. However, this is also true for full COPY_ONLY backups! The log backup job should only check for the last non-COPY_ONLY full backup in order to delete old log backup files.

Just wondering if I'm the only that has run into this, or if what I'm suggesting makes sense. If I need to take a full copy only backup in the middle day to IDK refresh a TEST database, that copy only backup shouldn't affect the regular daily backup sequence. Let me know your thoughts, please.


texts: what's the optimal partition setup for a linux db server?

texts:
My host wants to know how I want to partition my server before installing CentOS. I need to be able to do use xtrabackup to create quick backups from 20-50GB databases. I know I need to use a LVM, but I'm not sure how I would forward that to my host?

I currently have 1TB of usable space in RAID-10, how should I partition the array? this is only going to be a db server, only mysql and OS will be on this server.


texts: Divide a PostgreSQL database into schemas

texts:
My Postgres database will be focused on multidisciplinary organizations. The project axis is: a person could belongs to diverses disciplines (ex: arts, sports) for the same organization, and this organization needs an unique central Web app.

My first intent was modeling only one database but the size was enormous and the relations so complex and redundant, then my second and current approach is as follows:

People_Schema containing general tables like tbl_person, tbl_city, tbl_ocupation, etc.
Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
Sports_Schema containing tables like tbl_sport, tbl_team, tbl_team_member(team_id FK, person_id FK), etc.
And among others schemas in future growing.

The question is: Does this last approach is a good idea? or Does will be a hell for join queries between schemas or others painful implications that I have not seen?, considering multi-tenant expectatives, RESTfull APIs, DreamFactory Backend as a Service and Amazon EC2 Server.

Edit:
My approach was inspired by this statement taken from official Postgres docs: If the projects or users are interrelated and should be able to use each other's resources, they should be put in the same database but possibly into separate schemas.


texts:
I have a case to create podcast playlist based on category sorts. For example, I want to join podcast data and category data based on the table below.

> SELECT * FROM category_sorts;

+----+-------------+------+
| id | category_id | sort |
+----+-------------+------+
|  1 |           2 |    1 |
|  2 |           1 |    2 |
|  3 |           1 |    3 |
|  4 |           2 |    4 |
+----+-------------+------+

Column category_id related to categories table.

> SELECT * FROM categories;

+----+--------------+-----------+
| id | title        | status    |
+----+--------------+-----------+
|  1 | Saga         | published |
|  2 | Asia Calling | published |
+----+--------------+-----------+

And here some sample data from podcasts table with a related category.

> SELECT * FROM podcasts;

+----+-------------+------------------------+---------------------+
| id | category_id | title                  | published_at        |
+----+-------------+------------------------+---------------------+
|  1 |           1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 |
|  3 |           2 | Berita Asia!           | 2018-05-01 22:52:15 |
|  4 |           2 | Asia Later!            | 2018-05-01 23:47:13 |
|  5 |           1 | Saga terbaru           | 2018-05-02 00:06:32 |
+----+-------------+------------------------+---------------------+

Using the provided query below, I got duplicate podcasts in the same category sorts.

SELECT
        p.id, p.title AS podcast_title, p.published_at,
        c.title AS category_title,
        cs.sort
    FROM podcasts as p
    INNER JOIN categories as c ON c.id = p.category_id
    INNER JOIN category_sorts AS cs on cs.category_id = c.id
    GROUP BY cs.sort
    ORDER BY cs.sort ASC, p.published_at DESC

Results:

+----+------------------------+---------------------+----------------+------+
| id | podcast_title          | published_at        | category_title | sort |
+----+------------------------+---------------------+----------------+------+
|  3 | Berita Asia!           | 2018-05-01 22:52:15 | Asia Calling   |    1 |
|  1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 | Saga           |    2 |
|  1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 | Saga           |    3 |
|  3 | Berita Asia!           | 2018-05-01 22:52:15 | Asia Calling   |    4 |
+----+------------------------+---------------------+----------------+------+

What I expect?

I want to order playlist based on sort column and latest podcasts indicated by published_at column. Order using sort column is OK, but I can't order it by published_at date from podcasts.

Can someone give me a clue how to do with it? Any link or source will be appreciated.

Here is example full table structure and data from SQL Fiddle if you want to play with it.

Thank you.


texts: Mongodb: Does not seem to respect capped size limit

texts:
Running mongodb on win2012

Mongo db version v2.6.4

Our collection is created with this param:

capped:true, size:500000000000    // 500 gb

But the data dir, that only holds this one collection, has grown to 720gb.

There is no replication in use at all on this mongo server.

What can I do to keep the data dir at ~ 500 gb?

This db does get massive amounts of inserts at times, but we also need to control the max size (and let it discard old data).

Is it possible that the capped size does not include indexes?

Or is there other overhead I should account for? (and how?

Or?

db stats

> db.stats
function (scale){
    return this.runCommand( { dbstats : 1 , scale : scale } );
}
> db.stats()
{
        "db" : "logging",
        "collections" : 3,
        "objects" : 289637086,
        "avgObjSize" : 2076.3166847770317,
        "dataSize" : 601378314192,
        "storageSize" : 606012620432,
        "numExtents" : 305,
        "indexes" : 12,
        "indexSize" : 147827443456,
        "fileSize" : 776943435776,
        "nsSizeMB" : 16,
        "dataFileVersion" : {
                "major" : 4,
                "minor" : 5
        },
        "extentFreeList" : {
                "num" : 0,
                "totalSize" : 0
        },
        "ok" : 1
}
>

The command we use to create the collection:

db.createCollection("LogItem", { capped:true, size:500000000000 })

So the collection we write to is created with the capped cmd. and that is where all the space is

I can't do db.collection.stats() because out of disk space...

Thank you!


texts: MySQL - Simple update is very slow and causing high load average with large amount of server

texts:
Simple update is very slow  and causing high load average with large amount of server :

table name :

CREATE TABLE IF NOT EXISTS `LISTE_PROMOTION3` (
  `id_list_prom` int(11) NOT NULL AUTO_INCREMENT,
  `id_hotel` int(11) NOT NULL,
  `id_marche` int(11) NOT NULL,
  `lib_promo` varchar(250) NOT NULL,
  `etat_promo` enum('T','F') NOT NULL DEFAULT 'T',
  `type_promo` varchar(250) NOT NULL,
  `debut_vente` date NOT NULL,
  `fin_vente` date NOT NULL,
  `debut_sejour` date NOT NULL,
  `fin_sejour` date NOT NULL,
  `min_sty` int(11) NOT NULL,
  `max_sty` int(11) NOT NULL,
  `free_night` int(11) NOT NULL,
  `type_free_night` varchar(250) NOT NULL,
  `priorite_promo` int(11) NOT NULL,
  `typ_prom` varchar(250) NOT NULL,
  `pourcent_red_lpd` float NOT NULL,
  `pourcent_red_dp` float NOT NULL,
  `pourcent_red_dpplus` float NOT NULL,
  `pourcent_red_pc` float NOT NULL,
  `pourcent_red_pcplus` float NOT NULL,
  `pourcent_red_aisoft` float NOT NULL,
  `pourcent_red_allin` float NOT NULL,
  `pourcent_red_ultraai` float NOT NULL,
  `montant_red_lpd` float NOT NULL,
  `montant_red_dp` float NOT NULL,
  `montant_red_dpplus` float NOT NULL,
  `montant_red_pc` float NOT NULL,
  `montant_red_pcplus` float NOT NULL,
  `montant_red_aisoft` float NOT NULL,
  `montant_red_allin` float NOT NULL,
  `montant_red_ultraai` float NOT NULL,
  `id_agent` int(11) NOT NULL,
  `date_insertion` datetime NOT NULL,
  `date_modif` datetime NOT NULL,
  `id_agent_modif` int(11) NOT NULL,
  `typ_app_prom` varchar(250) NOT NULL,
  `sur_tarif` varchar(250) NOT NULL,
  `pourcent_red_ls` float NOT NULL,
  `montant_red_ls` float NOT NULL,
  PRIMARY KEY (`id_list_prom`),
  KEY `id_hotel` (`id_hotel`),
  KEY `etat_promo` (`etat_promo`),
  KEY `fin_vente` (`fin_vente`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9480 ;

The table contains 9480 rows.

Query:

UPDATE `LISTE_PROMOTION` SET `etat_promo` = 'T' WHERE `id_list_prom` = 9460;

This query update 1 row and takes around 25 seconds to complete and causing high load average with large amount of server.


texts: Choosing the right blocksize for Oracle Database tablespaces with lobs

texts: We are currently switching from an older character set to Unicode using impdp/expdp on Oracle Database 19c. We had to changed several data types. We changed tables with LOBs from basicfile to securefile and found that the tablespace holding this lobs only uses an 8K blocksize. The table containes several TBs with pdfs around 2-3mb. Does it makes sense to increase the Blocksize of the tablespace holding the LOBs from 8k to 32k?


texts: Should database be denormalized if I need an index across multiple tables

texts:
I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like:

SELECT * FROM song s
JOIN album a ON a.id = s.album_id
WHERE LOWER(CONCAT_WS(' ', album.name, song.name) = LOWER('Meteora Breaking The Habit')

I wish to create an index on the above type of search terms but it looks like I can't have an index across multiple tables. In this case, would it make sense to de-normalize the table and put the song & album name in a single table or is there a better approach?

Reference Table:

CREATE TABLE album (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE song (
   id SERIAL PRIMARY KEY,
   name text NOT NULL,
   album_id integer NOT NULL,
   CONSTRAINT album_id_fk FOREIGN KEY (album_id) REFERENCES album (id)
);

P.S. For the sake of understanding, I'm ignoring the fact that query can be song name first & album name later. Also ignoring that query can contain band name too. The query sent by the client is of the form album.name + ' ' + song.name.


texts: How can I check if BULK INSERT is running on a table?

texts:
A BULK INSERT command may take a long time to run.
I have a program that receives multiple files and pushes them to the DB using the BULK INSERT command to a specific table.
After a file is uploaded, the program deletes it and moves to the next.
The BULK INSERT command may continue to run on the DB after the program stopped. so a file may be loaded to the DB, or still in process when my program starts.
So, my questions are:

Can I tell if a BULK INSERT command is still running for a specific table?
Can I tell if a file has been already loaded to the DB?

I can get the running query by running:
select t.text
from sys.dm_exec_requests r
cross_apply sys.dm_exec_sql_text(r.sql_handle) t
where command= 'BULK INSERT'

but how can I know on which table it runs?


texts: Run stored procedures in other schemas from stored procedure

texts:
I have a current process: a large procedure in one schema.
There is a plan to have A, B1, B2, B3 schemas (B-schemas are identical, could be B-n schemas) in Oracle 12c database.
Schemas B1, B2, B3 have synonyms to A.
Schema A stores all the connections information to other schemas.

Is it possible to run a stored procedure in A and call the stored procedure in other B-schemas (dynamically determine schemas to run and do it in parallel).

There is also .Net part and it is better to put this logic there and make calls to the B-schemas stored procedure in different threads?


texts: Different table size on sp_spaceused and standard report "Disk Usage by Top Tables"

texts:
I ran sp_spaceused and Disk Usage by Top Tables standard report for a table.

The results for sp_spaceused are:

name rows    reserved    data    index_size  unused
SomeTable  <1301755>    <7691344 KB>    <3931672 KB>    <3673840 KB>    <85832 KB>

However Disk Usage by Top Tables report shows:

Table Name  # Records   Reserved (KB)       Data (KB)   Indexes (KB)    Unused (KB)
SomeTable   1.301.755   4.340.216       3.931.672   324.776 83.768

Record count is the same but there is a big gap between the two in terms of space used. sp_spaceused shows 7.691.344 KB as reserved while the report shows 4.340.216 KB. Which one is correct?


texts: Database migration Oracle to PostgreSQL, domain indexes

texts:
I am using aws schema conversion tool for migration database oracle to PostgreSQL. I found an error which is not support in PostgreSQL. PostgreSQL doesn't support domain indexes. anyone know what is the solution of domain index in PostgreSQL?

CREATE INDEX CTX_IDX_UFA_EMAIL
  ON USR_ACCOUNT(EMAIL)
  INDEXTYPE IS CTXSYS.CTXCAT;

When I create this type of index, a table and a trigger are automatically created in the database. My question is PostgreSQL does not support this type of index. What is the alternative way to convert this type of index in PostgreSQL database that work like Oracle database?

Oracle docs: Using Extensible Indexing.

And more about the "Context" CTXCAT index type.


texts: Trigger to update a materialized view once after relevant transaction?

texts:
I'm using PostgreSQL 9.6.

I have a materialized view to support full-text search across two tables, which I'll call posts and tags. The tables are infrequently updated and frequently searched.

I'm trying to find a good strategy to run REFRESH MATERIALIZED VIEW post_search.

The ideal solution would be a trigger like this: at the end of (or after) any transaction that modifies posts, tags and/or posts_tags (join table), refresh the materialized view exactly once.

How could I accomplish this?


texts: I want to Move some Mysql table to InnoDB but i don't know what is the best my.cnf conf

texts:
This is my server specification

my.cnf:

[mysqld]

innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=50000
default-storage-engine=MyISAM
myisam_sort_buffer_size=256M
table_open_cache = 600
thread_cache_size = 4
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

join_buffer_size = 256k
tmp_table_size = 20M
max_connections = 600

slow-query-log = 1
slow-query-log-file = /output/mysql-slow.log
long_query_time = 1
datadir=/disk3/mysql

Server Ram

             total       used       free     shared    buffers     cached
Mem:           62G        61G       1.5G       1.8G       2.0G        53G
-/+ buffers/cache:       5.6G        57G
Swap:         1.9G       230M       1.7G

CPU

model name      : Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz × 2
cpu MHz         : 2000.022
cache size      : 15360 KB

I have a very big MyIsam Table , each user has status record in it, I select insert join to it. Here is the table :

CREATE TABLE `status_userid` (
             `id` int(10) NOT NULL AUTO_INCREMENT,
             `md5id` char(32) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `ptype` char(1) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `media_id` varchar(50) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `media_url` varchar(100) COLLATE utf8_swedish_ci NOT NULL,
             `media_photo` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
             `user_id` varchar(50) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `user_name` varchar(100) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
             `reason` text COLLATE utf8_swedish_ci,
             PRIMARY KEY (`id`),
             KEY `md5id_mediaid` (`md5id`,`media_id`),
             KEY `user_id_md5id` (`user_id`,`md5id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

from mysql.slow.log:

Count: 1165  Time=11.17s (13008s)  Lock=0.00s (0s)  Rows=548.3 (638790), myinstar_ranker[myinstar_ranker]@localhost
  SELECT
  s1.id as order_by,
  s1.media_id,
  s1.media_photo,
  s1.user_id,
  s1.user_name,
  s1.date
  FROM `status_427` s1
  LEFT JOIN
  (SELECT md5id, user_id, ptype FROM `status_427`  WHERE md5id = 'S') s2
  ON
  s1.user_id = s2.user_id AND s2.ptype = 'S'
  WHERE
  s1.md5id = 'S'
  AND s2.md5id IS NULL
  AND s1.date  < NOW() - INTERVAL N DAY
  AND s1.ptype = 'S'

Count: 2247  Time=8.09s (18172s)  Lock=0.00s (1s)  Rows=1.0 (2247), myinstar_ranker[myinstar_ranker]@localhost
  SELECT
  count(N) as counter
  FROM `status_421` s1
  LEFT JOIN
  (SELECT md5id, user_id, ptype FROM `status_421`  WHERE md5id = 'S') s2
  ON
  s1.user_id = s2.user_id AND s2.ptype = 'S'
  WHERE
  s1.md5id = 'S'
  AND s2.md5id IS NULL
  AND s1.date  < NOW() - INTERVAL N DAY
  AND s1.ptype = 'S'

I manage to create separate table for each user so I don't end with a huge table if it damaged I lost everything. but this table is very big one, 8M record and counting, that's is why I decided to split it.

Mysql directory on SSD drive.

I have a huge mysql using, and the server top is:

Tasks: 781 total,   5 running, 775 sleeping,   1 stopped,   0 zombie
Cpu(s): 41.1%us, 10.1%sy,  0.0%ni, 48.5%id,  0.0%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:  65784692k total, 64190512k used,  1594180k free,  2141220k buffers
Swap:  1999868k total,   236436k used,  1763432k free, 56162120k cached

    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 459105 mysql     20   0 12.4g 286m 6588 S 564.2  0.4 137:35.08 mysqld

So, does innoDb may help to reduce mysql Load?
What is the best inndoDb my.cnf setting, I was told innoDb is very sensitive and can be damaged so fast, and even more it create a huge log file.

I am not Mysql expert, I know php and some of mysql, but not expert in tuning it.

What are the best setting I can use to reduce server load?


texts: why my MySQL binary log file size is so small?

texts:
I have a lot of small binary log files , each of them just only 126 Bytes

-rw-rw----. 1 mysql mysql  126 Jan  6 16:13 mysql-bin.000001
-rw-rw----. 1 mysql mysql  126 Jan  6 16:16 mysql-bin.000002
-rw-rw----. 1 mysql mysql  150 Jan  7 10:34 mysql-bin.000003
-rw-rw----. 1 mysql mysql  126 Jan  8 09:57 mysql-bin.000004
-rw-rw----. 1 mysql mysql  126 Jan  8 11:08 mysql-bin.000005
-rw-rw----. 1 mysql mysql  107 Jan  8 11:09 mysql-bin.000006
-rw-rw----. 1 mysql mysql  126 Jan  8 13:21 mysql-bin.000007
-rw-rw----. 1 mysql mysql  342 Jan  8 16:25 mysql-bin.index

how to let them grow bigger? and my "binlog_cache_size" is more than 126 Byte

mysql> show variables like 'binlog_cache_size';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)

can anybody tell me why(⊙ˍ⊙) ? thank you very much :)

mysql logging


texts: How to use imdpd to import data file into different schema including index from different tablespace as well

texts:
The original data pump file is generated by the schema which living in two tablespaces(OLD_SCHEMA_DAT and OLD_SCHEMA_IDX), if import back to the original tablespace, everything is fine, but when I use following command:

impdp OLD_USER/OLD_PASS dumpfile=whole.package.dmp directory=DATA_PUMP_DIR
REMAP_SCHEMA=ORIGINAL_SCHEMA:NEW_SCHEMA

It works but with 200 errors stating that

ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-00959: tablespace 'OLD_SCHEMA_IDX' does not exist

it looks for the original index tablespace instead of the new one, and even I tried:

impdp OLD_USER/OLD_PASS dumpfile=whole.package.dmp directory=DATA_PUMP_DIR
REMAP_TABLESPACE=OLD_SCHEMA_DAT:NEW_SCHEMA_DAT
REMAP_TABLESPACE=OLD_SCHEMA_IDX:NEW_SCHEMA_IDX

Still did not resolve the problem, then what to do? I am on 11g.


texts:
Below query never completes and runs for over 14 hours:

SELECT
U.Age, U.CreationDate, U.DisplayName,
P.AcceptedAnswerId, P.ClosedDate, p .Title, P.ViewCount,
C.CreationDate,C.Score ,C.UserId
FROM dbo.Users As U
LEFT JOIN dbo.Posts As P on U.id = P.id
LEFT JOIN dbo.Comments As C on U.id =C.id
WHERE
(
U.Location = 'India'
OR U.Location ='United Kingdom'
)
AND U.LastAccessDate>= '04/02/2018'
AND U.LastAccessDate< '06/02/2018'
AND P.LastActivityDate>= '04/02/2018'
AND P.LastActivityDate< '06/02/2018'
AND U.LastAccessDate>= '04/02/2018'
AND U.LastAccessDate< '06/02/2018'
AND U.Age>10
AND U.Age is NOT NULL

How can I improve and speed it up ?


texts: my root account has been deleted for unknown reason, how to recover?

texts:
i have a program called heidiSQL on my computer that enables me to edit data inside my database with querys. recently tried to create a new user through this program while signed in as the root account. i used the following code

  create user 'john91'@'localhost' identified by 'google';
  grant all privileges on *.* to 'john91'@'localhost;
  flush privileges;

i then closed the HeidiSQL program and opened it again to sign in with the new user 'john91', it worked fine, but what struck me was that i couldn't sign in to my root account anymore.

i then open the command line and sign in with the user 'john91' (which works) and checked all my user in the server by using this code:

  select host,user from mysql.user;

i found that 'john91' was the only user in the system. i was able to create a new root account by using this code and while signed in with 'john91' user:

  create user 'root'@'127.0.0.1' identified as 'google';

this worked fine but i was unable to grant this root account any privileges, so when i used:

  grant all privileges on *.* to 'root'@'127.0.0.1';
  ERROR 1045 (28000): access denied for user 'john91'@'localhost' (using
  password: YES)

so is this the right way to recreate my root account, if yes, what am i doing wrong, if no, how should i do it?

Update, i need to create 'root'@'localhost' MySQL is not letting me do that through the user 'john91', how can it be done?


texts:
Let be a relation R(NameFile, Size, Directory, DateCreation,HourCreation,Login,AutAccess,DateAccess,TypeAcces

F={NF,D→S;
   NF, D →DC;
   NF, D→HC;
   L,NF,D→A;
   L,NF,D,DA,HA→TA;
}

  Part of the instances are given in the following array:

  1. What are the minimal keys of R? Why?

  2. In which normal type is R? Why?

I only know that:

a key is minimal when every attributes that appears in no functional
dependency is in every minimal keys
Every attributes that appears only on the left of the FD is in every
keys.
Every attributes that appears only on the right of the DF isn't in
any key.

Therefore we should have K1={NF,R,L,DA,HA} as the only minimal key.

What type of Normal Form is relation R?

I know that

something is 1NF if forall attributes of R, there is an atomic
value

Therefore, it is at least 1NF,

something is 2NF iif:

The scheme is at least 1NF
Forall An attribute, not in any key, A doesn't depends to part of the key. that is to say not in any functional dependency.

A scheme of relation R is 3NF iif

The scheme is 2NF
Not exists a transitive functional dependency

But I don't know what to do from there... I don't even understand the two last definitions


texts:
I am designing erd for campground and campsite.
In general, a campground has many campsites in it. that lead to one to many relationships. but sometimes campsite has no campground, it appears as the single entity with no parent. then what relationship should be used for these?


texts: Oracle out-of-place upgrade on same host: impdp issues

texts:
I am testing an out-of-place Oracle upgrade from 11.2.0.2 to 11.2.0.3 on the same host. From what I've read, expdp and impdp is the way to go. My problem is that since I am importing to another instance on the same host, it's trying to recreate the same directory structures. The beginning of the import log looks like this:

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@NEWINSTANCE full=Y directory=bak_dir

dumpfile=dbexp.dmp
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file 'D:\ORACLE\ORADATA\DB1\TBLSP_73_DATA.DBF'
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
Failing sql is:
CREATE TABLESPACE "TBLSP_73_DATA" DATAFILE 'D:\ORACLE\ORADATA\DB1\TBLSP_73_DATA.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT

It appears that it's attempting to use the same paths as the source database. How can I get the datapump import to point to a new path like D:\ORACLE\ORADATA\DB2...


texts: How to structure a MongoDB database to allow for multiple users to use the same account with restrictions based on roles?

texts:
Ok, firstly I'm very new to MongoDB, and newish to DB development/management as a whole. I've done a unit using Oracle through uni, but apart from just manipulating fields through the phpmyadmin interface for wordpress plugins/sites, that's about it.

I want to model a (fairly complex?) user system. Here's an excerpt from the brief relating to it.

Users should be able to log in with their email and password, create a new account, invite other users and set different access rights. This way multiple users can share the same account with different levels of authority (ready-only, editing rights, etc.)

The backend needs a section where the business owner can set global restrictions on areas/sections of the app for different groups of users.

I've done a bit of research into structuring a MongoDB database and understand there should be a balance between embedding data into documents, and referencing other documents so that in the end you require fewer queries than if you embedded everything or referenced everything.

Here are my initial thoughts.

Side note - I don't know how to properly document a MongoDB schema so sorry if this doesn't actually make sense. I'll try explain it as best I can.

The diamond connections are for embedded objects inside documents, while the straight connections show references. (i.e AccountUsers are embedded in an Account while a Role is referenced by a User and AccountUser). I should probably update this to show directions and relation. Anyway...

I believe this setup allows both Users on an application level, and users on an account level to have separate roles that can be retrieved later. Global restrictions could be set by the business owner for all users of a certain role, and the admin of an account can set account wide restrictions based on setting a user's account role.

The roles themselves are arrays of objects containing a boolean and a reference to a SitePermission. The idea being that the SitePermissions will be setup by the dev team, with one SitePermission for each section of the app, from which the business owner or account owners can create their own roles.

Does this setup seem like a good way to go about creating a database that suits the brief? Like I said, I'm really new to MongoDB so I could have this whole thing completely wrong by treating it similarly to an RDBMS like Oracle. I basically just need to know if I'm doing this right? Thanks!


texts: How do I name the thing that is done in the FROM part of the query?

texts:
So, I have recently started studying data bases in Uni, MySQL.
I am practicing some queries with different stuff that the professor gave us.
One of the questions i have found is as follows:
Show the Name and Wage of the employee supervisor and the average wage of the employees that he is supervising. Arrange the data by name and the average wage in an ascending order.
(I'm working on the newest version of the MySQL Workbench with the accompanying local host server)
There is only one table, the employee table. With a foreign key supervisor ID that references the employee IDs which are primary keys.
This is the solution to the query:
SELECT SUP.name, SUP.wage, AVG(EMP.wage) AS AVG_WAGE
FROM employee EMP, employee SUP
WHERE SUP.employee_ID = EMP.supervisor_ID
GROUP BY SUP.name
ORDER BY AVG(EMP.wage) ASC;

I'm wondering, what is the name of the thing that he used here where he like... split the employee table into 2 virtual tables for the sake of the query?
I didn't know this is possible at first so I was doing this:
SELECT name, wage, AVG(wage) AS AVG_WAGE
FROM employee
WHERE emp_ID IN (SELECT sup_ID FROM employee)
GROUP BY name, wage, sup_ID IN (SELECT emp_ID FROM employee)
ORDER BY AVG(wage);

This shows the supervisors and their wages but doesn't show the average wage of the employees that each supervisor supervises and just displays the wage of the supervisors again with a bunch of zeroes at the end.
Thank you!
I'm sorry if I'm not very clear in my wording since I'm not studying databases in English and English is not my first language. Thus it isn't easy to find info on google about a bit specific things like this where I don't know how to word questions.


texts: I have a SQL Server placed in DMZ and I want to give access to internal users (and VPN clients) to use SSMS to remotely connect to it via port 1433 so server will accept the connection only form defined IP sources over this port, is it a safe approach for a production server?


texts: How to get the name of the current database from within PostgreSQL?

texts:
Using \c <database_name> in PostgreSQL will connect to the named database.

How can the name of the current database be determined?

Entering:

my_db> current_database();

produces:

ERROR:  syntax error at or near "current_database"
LINE 1: current_database();


texts: SQL Server restore database with active connections

texts:
I am trying to run this SQL script on a SQL Server instance to restore a test database to a newer copy of the live database.

RESTORE FILELISTONLY
   FROM disk = '\\staging_server\path\to\db_backup.bak'
-- Restore the files for
RESTORE DATABASE Test_DB
   FROM disk = '\\staging_server\path\to\db_backup.bak'
   WITH replace,
   MOVE 'Test_DB' TO 'D:\R2_BIN\Test_DB.mdf',
   MOVE 'Test_DB_log' TO 'D:\R2_BIN\Test_DB_log.ldf',
   stats = 5
GO

However, on running the script I receive the following error.

  Msg 3101, Level 16, State 1, Line 5
  Exclusive access could not be obtained because the database is in use.
  Msg 3013, Level 16, State 1, Line 5
  RESTORE DATABASE is terminating abnormally.

Is there some way I can force this operation to go through, and kill any connections it needs to to get it done? I tried to close down any applications that may connections to the DB, but some of the connections I simply cannot identify. There are several from my username and yet I only have one instance of SQL Server Management Studio open.

Anyway, is there some way to kill all connections to a specific database? There are quite a few databases running on this instance, so I don't want to just shutdown and restart the instance all together if I can help it.


texts: How to structure Tables with changing status without duplicating data

texts:
I have a table called Inquiry
it will contain many - Services, Commodities & Invoices.

Then later, You can change it to an Appointment which will contain the same Services, Commodities & Invoices of its Inquiry parent.

The problem is how do i model the relationship without duplicating data for Services, Commodities & Invoices that is already present in Inquiry table?

NOTE: Appointment could be created without creating an Inquiry.

Below is what i currently have in mind but i think its not flawless.