texts: Viewing tables and data from a tablespace that was attached from another database

texts:
I'm using a database Oracle 11g.

I have a DBF file of an old tablespace from a different database that I want to connect to the existing database.

I've created a tablespace with REUSE caluse, and it was created successfully

  SQL>  create tablespace TEST datafile 'C:\DATA.DBF' reuse;

How can I view the tables/data within that tablespace?

Was that the right approach ?

Or should I attached that tablespace in a different way ?

Thanks in advance for your answers,

Roni


texts: Need help with indexes on 2 slow wordpress query


texts: What happens when I try to store more 8019 bytes in a SQL Server table row?

texts:
I read -

total fixed and variable length data are still limited to 8019 bytes total

But my data can possibly be more than this. What happens if it's more. Does it still store the data correctly?

Also can someone tell me how I can store for example over 4000 bytes of text data in a column. Is that possible?


texts: Sum Interval of Dates within same column

texts:
How do you best sum the differences of a range of dates in the same column between interleaving rows? I have a Datetime column and want to calculate the difference between Rows. I want the difference in Seconds. This questions is not about how to get a difference between 2 timestamps, but is more focused on how to most efficiently calculate between rows on the same table. In my case each row has a datetime eventype that links 2 rows together logically.

Details Related to how to group the eventtypes of start and end. ( Andriy M's Question)
Starts and Ends "should" be consecutive. If a Start does not have a subsequent end it should be left out of the sum. Moving to the next Start to see if it has a end. Only consecutive Start - End Pairs should be added to the sum of the total seconds.

Working in postgresql 9.x...

Example data in table;

eventtype, eventdate
START, 2015-01-01 14:00
END, 2015-01-01 14:25
START, 2015-01-01 14:30
END, 2015-01-01 14:43
START, 2015-01-01 14:45
END, 2015-01-01 14:49
START, 2015-01-01 14:52
END, 2015-01-01 14:55

Note,
All Start and End Dates will be sequential.

Here's my first attempt. Seems to be working.

SELECT
-- starts.*
SUM(EXTRACT(EPOCH FROM (eventdate_next - eventdate))) AS duration_seconds
FROM
(
    WITH x AS (
        SELECT *, dense_rank() OVER (ORDER BY eventdate) AS rnk
        FROM   table
        AND eventdate > '2015-01-01 00:00:00.00'
        AND eventdate < '2016-01-01 23:59:59.59'
        )
    SELECT x.eventdate, x.eventtype, y.eventdate AS eventdate_next,  y.eventtype AS eventtype_next
    FROM   x
    LEFT   JOIN (SELECT DISTINCT eventdate, eventtype, rnk FROM x) y ON y.rnk = (x.rnk + 1)
    ORDER  BY x.eventdate
) starts
WHERE
eventtype = 'START'
GROUP BY eventtype

My first attempt is based on a great example from stackoverflow
Postgres 9.1 - Getting the next value

Note; You can comment the GROUP BY and the SUM and un-comment the starts.* to get a record for each individual duration going into the sum.


texts: Change database file path with SQL not running

texts:
I've an urgent problem.

Due to some heavy delete queries, my database transaction log has grown and it has filled the disk (126GB). I've found out that backup plan wasn't working during last month.

Now, the database is recovering.

  Recovery of database 'ABCD' (5) is 1% complete (approximately 45395 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

I've added a new disk to the server and I would like to move the .ldf file and then restarting the recovery of database.

Is it possible?


texts: INET6_ATON returns NULL for input parameter

texts:
I'm making a MySQL Stored Procedure which takes a string representation of an IPv4 or v6 address and converts it to binary using INET6_ATON. I know that my version of MySQL supports this function because I can run SELECT HEX(INET6_ATON("::ffff:127.0.0.1")); and it returns the correct representation.

However, when I try to put this in to a Stored Procedure and use an input parameter, it always returns NULL no matter what I put in to it.

Here's a simplified version I made using a Stored Function instead:

DROP FUNCTION IF EXISTS `addrToBinary`;
DELIMITER ;;
CREATE FUNCTION `addrToBinary` (`in_remote_ip` varchar(45)) RETURNS varchar(45)
DETERMINISTIC
BEGIN
    RETURN HEX(INET6_ATON(in_remote_ip));
END;;
DELIMITER ;

SELECT
    addrToBinary("::ffff:127.0.0.1"), -- NULL
    HEX(INET6_ATON("::ffff:127.0.0.1"));    -- 00000000000000000000FFFF7F000001

Any ideas on how I can get this function to work the same inside a Stored Procedure/Function as it does typing it in manually?

UPDATE: I've just found out that I'm in fact running MariaDB (5.5.5-10.0.17-MariaDB). Here's a screenshot of the issues when I run it using Adminer:


texts: I got this error when am trying view dba_tables Ora 03113 end of file on communication channel

texts:
I got this error when am trying to view DBA_tables:

  ORA 03113 end of file on communication channel
  Process I'd: 9244. Serial number 1431


texts:
I see this conversion a lot when trying to convert this to a legitimate time:

SELECT DATEADD (ms, -1 * ((s.cpu_ticks) - r.[timestamp]), GETDATE())
FROM sys.dm_os_ring_buffers r
                CROSS JOIN sys.dm_os_sys_info s

I get the error:

  Arithmetic overflow error converting expression to data type int.

What's the correct formula for converting this?

Answer:ms_ticks


texts: ERD Design issue - general/central projects table

texts:
I have multiple different “project manager” apps as: Work Projects app manager, Home Projects app manager,...

Each app has its own DB i.e separate ER diagram.
Each app has it's own [projects] table(since they are all some kind of project management apps).

I want to add General Projects Manager app that will keep track of all specific projects.

One General Project can contain multiple specific projects.

1st app: Work Projects

tbl.work_projects

id_work_projects
work_project_name
status_compleated
date_started
…

2nd app: Home Projects

tbl.home_projects

id_home_projects
home_projects_name
status_compleated
date_started
…

3rd app: ...etc.

I need general project manager app, that will keep track of all projects, as their: names, statuses, dates, etc.

How to design the general/central “all_projects” table and how to relate it to all this [work/home_projects] tables?

Additional information

Project tables are mostly different (just few common columns), and each of those [work/home]Project tables represents an entity type that is part of a separate ERD (i.e. separate app).

I read Q & A about a supertype-subtype structure and it helped. My case is nonexclusive but JOINS (and VIEWS) seems to stay the same. One problem is that primary keys of subtype tables are already set, and supertype tbl.General_Projects should now be created. I guess that subtype tables primary keys should be updated (with Cascade).


texts: Will I be notified that BACKUP encountered a CHECKSUM error if I use CONTINUE_AFTER_ERROR?

texts:
I am in the process of adding the WITH CHECKSUM flag on our daily SQL backups, as part of an effort to better ensure data integrity.

I definitely want to know if a checksum error is ever encountered, but I also don't want my job to stop dead in the water in the middle of the night; I want it to finish backing up the "bad" database, then continue backing up the other databases on the server.

If I use BACKUP WITH CHECKSUM, CONTINUE_AFTER_ERROR, will it still throw the appropriate error (SEV 22 or Error 825 or whatever) that will trigger my associated alert? Or does CONTINUE_AFTER_ERROR suppress this altogether, and I'd only know about the problem if I parse the job step output?

I would simply test it, but I don't have a database with known CHECKSUM inconsistencies.


texts: Why am i getting "missing right parenthesis" error when i try to LOG ERRORS when loading from an external table?

texts:
I've successfully created an error logging table
BEGIN
    DBMS_ERRLOG.create_error_log(
    dml_table_name  => 'enzyme',
    skip_unsupported => TRUE);
END;
/

desc ERR$_ENZYME;

Name            Null? Type
--------------- ----- --------------
ORA_ERR_NUMBER$       NUMBER
ORA_ERR_MESG$         VARCHAR2(2000)
ORA_ERR_ROWID$        UROWID
ORA_ERR_OPTYP$        VARCHAR2(2)
ORA_ERR_TAG$          VARCHAR2(2000)
ENZ_NAME              VARCHAR2(4000)

But i get an error when I try to run this query:
insert /*+ ignore_row_on_dupkey_index ( enzyme ( enz_name ) ) */
into enzyme
SELECT enz_name FROM EXTERNAL ((
  construct_id NUMBER(10),
  n_term VARCHAR2 (50),
  enz_name VARCHAR2 (3),
  c_term VARCHAR2 (50),
  cpp VARCHAR2 (50),
  mutations VARCHAR2 (50),
  mw_kda NUMBER (7, 3))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        skip 1
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    LOG ERRORS INTO ERR$_ENZYME ('INSERT') REJECT LIMIT UNLIMITED) ext
    where not exists (
        select * from enzyme e
        where e.enz_name = ext.enz_name
    );

Error at Command Line : 79 Column : 5
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:
*Action:

Line 79 is the LOG ERRORS INTO line.
If i delete the LOG ERRORS INTO ERR$_ENZYME ('INSERT') part, this command functions perfectly.


texts: mysql multiple unique values per primary key

texts:
I have a table that stores multiple phone numbers. While the same number can exist in the table I want to ensure that a user can't have the same number twice.

**Valid**

id0001 : 5555550000
id0001 : 5555550001
id0002 : 5555550000

**Invalid**

id0001 : 5555550000
id0001 : 5555550000
id0002 : 5555550001

Not sure the best approach.  Should I make a function and apply it as a constraint?

Should I change the structure of the table?

CREATE TABLE `callback` (
    `f_unti_id` INT(10) NOT NULL,
    `phone_number` varchar(10) NOT NULL
);

ALTER TABLE `callback` ADD CONSTRAINT `callback_fk0` FOREIGN KEY (`f_unti_id`) REFERENCES `unit`(`unit_id`);


texts: Partitioning - when to create new scheme?

texts:
I have the following partition setup on a table.

I'm wanting to create a new partition that begins on 2019-01-01.

Is the the correct way of doing it, and can it be run now so it's ready to go for 2019?

ALTER DATABASE mydatabase ADD FILEGROUP [Year7FileGroup]
GO
ALTER DATABASE mydatabase
  ADD FILE
  (NAME = N'data_year7',
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\data_year73.ndf',
  SIZE = 5000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Year7FileGroup]
GO
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year7FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20190101');


texts:
I have two tables (along with a nonclustered index) that can be created with the below commands:

CREATE TABLE GroupTable
(
  GroupKey int NOT NULL PRIMARY KEY,
  RecordCount int NOT NULL,
  GroupScore float NOT NULL
);

CREATE TABLE RecordTable
(
  RecordKey varchar(10) NOT NULL,
  GroupKey int NOT NULL,
  PRIMARY KEY(RecordKey, GroupKey)
);

CREATE UNIQUE INDEX ixGroupRecord ON RecordTable(GroupKey, RecordKey);

While technically my tables are slightly different and I am joining on a few other tables, this is a suitable proxy for my situation.

I would like to select all GroupKeys that are not subsets of another GroupKey.
For a given superset, I would like to grab the maximum GroupScore of all its subsets (including itself).
In the instance when a GroupKey contains the same exact RecordKeys as another GroupKey(s), then only one of those GroupKeys is grabbed (it doesn't matter which one).
Any GroupKey that has the same exact RecordKeys as another GroupKey(s) will also have the same GroupScore.
Non-related GroupKeys can have the same score as well.

The following is an example to illustrate what I am asking:

              GroupTable                          RecordTable

GroupKey    RecordCount   GroupScore         RecordKey    GroupKey
------------------------------------         ---------------------
  1              3            6.2                A          1
  29             2            9.8                A          29
  95             3            6.2                A          95
  192            4            7.1                A          192
                                                 B          1
                                                 B          29
                                                 B          95
                                                 B          192
                                                 C          1
                                                 C          95
                                                 D          192
                                                 E          192

I would like the output to be the following:

GroupKey    RecordCount    GroupScore
-------------------------------------
  1              3             9.8
  192            4             9.8

GroupTable has around 75M rows, and RecordTable has around 115M rows; however, after the joins and WHERE predicate, there tends to be around 20k rows on a given day.

I apologize if this question is trivial, but for some reason I'm really struggling with it.


texts:
I am catching all errors on my Sql Server, I got errors:

  Changed database context to 'MyDbName'.
  Changed language setting to us_english.

I haven't found any clear explanation of problem and severity of this error. Is it critical?


texts: How to write a query which recursively returns all foreign key references to a column?

texts:
I need to obtain a list of foreign keys which refer to a given column, as well as all foreign keys which refer to those keys, and so on. Order does not matter. I have

SELECT
    s.name,
    t.name,
    c.name,
    fk.name
FROM
    sys.foreign_key_columns fkc
    INNER JOIN sys.foreign_keys fk
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c
        ON c.object_id = fkc.parent_object_id
        AND c.column_id = fkc.parent_column_id
    INNER JOIN sys.tables t
        ON t.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas s
        ON s.schema_id = t.schema_id
WHERE
    fkc.referenced_object_id = OBJECT_ID(N'dbo.MyTable')
    AND fkc.referenced_column_id = (
        SELECT TOP 1
            column_id
        FROM
            sys.columns
        WHERE
            name = 'MyColumn'
            AND object_id = OBJECT_ID(N'dbo.MyTable')
    )

However, this just returns the foreign keys which immediately reference the column, not the entire tree of foreign keys referencing this column.


texts:
We are having to redo a publication/subscription and while running the snapshot agent it stalls at 48% with the message "The process is running and is waiting on a response from the server."  Eventually it times out.  I increased the timeout, but even after 4 hours it's still stuck at the same place.

I can't figure out what it's 'waiting on' or how to get it to progress further.  I've tried deleting and recreating the publication from scratch but same results.  Anyone have ideas on how I might troubleshoot it to figure out what the issue is?


texts:
i have a mysql database where i need to take a backup of the log database.

the problem is the log database i over 200gb and contains over 280 millions of rows, i need right now to remove a kind of data but before i can do it i need to take a backup.

i have trying mysqldump function but the problem is here its lock everything and block the website there running right now, and i have trying to do it over ssh tunnel the problems is.

1) the database its contain over 200gb data
2) the internet line its between 20-40mbit internet in download
3) the web server's its pretty slow and i have calculate its take over 4-5 hours thats mean the store is down in thats time.

are there a easy and smart method to take a remotely backup of so larges database before i remove something in the log table?


texts: SQL Server - materialised (indexed) view not working as expected

texts:
I've created a materialised view in SQL Server, but when selecting from it I get the same performances and the same execution plan than when I select from the same query not "materialised".

The view is simple:

CREATE VIEW TestView WITH SCHEMABINDING
AS
SELECT TAB_A.C1, TAB_A.C2, TAB_A.C3
FROM TAB1
INNER JOIN TAB2 ON TAB1.C2 = TAB2.C2
INNER JOIN TAB3 ON TAB2.C1 = TAB3.C1;

CREATE UNIQUE CLUSTERED INDEX IX_TestView
    ON TestView(C1, C2, C3);

Now, when I do something like

SELECT C1 FROM TestView

I'd expect to see in the plan only an access to the view and nothing else, instead it accesses the underlying tables like the view does not even exists.
I get exactly the same plan and the same execution time, too.

What am I doing wrong? If SQL Server cannot "materialise" the view, I'd expect to at least get an error when creating it.


texts: selective undoing of commited transactions in PostgreSQL

texts:
I have inherited a small (<1M rows) PostgreSQL DB which contains complex business rules split across several tables.  Updates are very light, reads not so heavy, but the data is extremely valuable.

These rules are updated by a variety of complex, legacy, overlapping systems, all of which are non-trivial to modify.  There are even some updates by humans using SQL templates.

And we really have had "panic why did you delete that customer's route?  how will we undo this?" moments.

So is there an easy way to version these tables?  To be able to manually review and decide to undo a particular transaction, and review which subsequent transactions rely upon it and so on?

I believe MSSQL and Oracle has versioning built-in.  Does PostgreSQL support any similar 'change data capture' system?

I hesitate to roll my own trigger-based scripts.  I can see that I can put the txid_current() into a 'shadow' table, but not how to record if a transaction commits or rolls back, for example.  TableAudit/TableLog, for example, seems to be on the table-only level.  If I had to roll my own, how would I do that and what aspects would I need to remember?

Are there tools or scripts for doing multi-table transaction tracking in PostgreSQL, and tools for recovery and undoing them?


texts:
Is there a way to replicate Postgres Schemas in AWS into one location for reporting purposes?

I have 3 postgres databases (up to date with current version) running in AWS that I would like to replicate into one location to build an Operation Data Store (ODS).  I require the ability to query cross databases - I've primarily only used MSSQL - and I believe that you can only cross query schemas.

Or do I have to put this into another amazon product to be able to achieve this result?


texts:
I have large amount of data in a MySQL database.

There are many databases schemas in the database.

How I can migrate from MySQL to HSQLDB?

Is there some tool for this?

I've exported the data manually.  How can I export only schema.sql?


texts: Can one part of a multi-column primary key be used across tables?

texts:
I want to use SQLite as file format for my app, a reporting program.
Below is an idea of the kind of schema I'll use (just the first few tables).
There will be one report per file and all tables are children of that. So far so good.

But I want to be able to merge files, eg. to store 100 reports in one file. I've read that with a little effort, I can use GUIDs in SQLite to avoid dealing with key conflicts when merging databases. Sounds good, but I need a GUID for every record in every table right? That's a lot of GUIDs for my statistics table which might be large!
I then read about multi-column primary keys.
Is there any way I can create just one GUID for a report, and have every child of the report incorporate that GUID into its key to make every record's key unique?
Is there a better overall approach?


texts: How can I create a notification method when someone adds a user/login to have ‘sa’ access?

texts: How can I create a notification method when someone adds a user/login to have ‘sa’ access on SQL Server 2005/2008/2008R2? Thanks for your help.


texts:
I have a problem with creating a new server in PgAdmin 4. I have server with the port 5432 as standard. Now I am trying to create a new server with port 5433. When I try to create the server I get the error:

I don't now how to fix. The server is running on "localhost", but I don't now how to connect the TCP/IP on port 5433.

Thx for you help.


texts:
I have a pesId (peopleId) column at pessoas (people) where is stored data from medics and patient. Also I have a table that stores medical consult information like Date, patient id, medic id and diagnostic.

I got to select the conDate, Patient Name (from table 'ppl'), Medic Name (also from 'ppl') and Diagnostic but I am having trouble for hours doing this. I am very noob at MySQL but I am trying to learn. May anyone help me with this logic problem?

I tried many methods.

select conDat, pa.pesId = pacientes.pacId, me.pesId = medicos.medId, conDiag, case when null then 'Indefinido' end
    from consultas
    inner join pessoas as pa on pa.pesId = pacientes.pacId
    inner join pessoas as me on me.pesId = medicos.medId;

Thank you very much

This is my creating tables code:

create table consultas (

conId int primary key auto_increment,
conDat date not null,
pacId int not null,
medId int not null,
conDiag varchar (30) );

create table pessoas (

pesId int primary key auto_increment,
pesNome varchar (50) not null);

create table pacientes (

pacId int primary key default 1,
pacTel varchar (20) not null,
pacAtiv bool);

create table medicos (

medId int primary key default 1,
medCPF varchar (20) not null,
medAtiv bool);


texts: I want to recreate the whole privilege system in my database, and that includes the slave user on the master. It means i have to stop the slave, run a set master command again and than start it. My problem is that im afraid that the slave might be in the middle of reading a binlog and then when i'll start the slave with the new account (with a new binlog and master position ofcourse) there will be a data gap between the slave and the master. Someone knows maybe if i have to worry about this case and how to avoid it? Thank you very much!


texts: Dynamic SQL UNPIVOT and putting results into existing table

texts:
I know that there are a few questions out there on Dynamic SQL and Pivoting, but I'm unable to figure out the issue that I came across since I'm not familiar with Dynamic SQL/Pivoting.

So, I have the data as follows that contains the weekly sales.

The numbers you see as columns refer the weeks of a year, and the columns are populated once the data is updated, and the week numbers don't go beyond 53, which means we know how many columns are going to be returned.

I can use static unpivot to handle this one, but the reason why I'm using Dynamic SQL to pull this off is to work with something new that I'm not familiar with, and trying to learn more about it, and the other reason is to get some knowledge that I can use dynamic sql on our monthly sales data, since I don't know how many columns are going to be returned and how can I put the unknown number of columns returned of the dynamic sql into a local table.

I've made some research and tried to unpivot the data to normalize with the following query, and it seems to be working.

-- create the dynamic date columns string
declare @cols AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(column_name)
                      FROM (
                            -- get columns as a list
                            select  TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
                            from    INFORMATION_SCHEMA.COLUMNS
                            where   TABLE_NAME = 'WEEKLY_SALES'
                            and     ORDINAL_POSITION not in (1,2,3,4,5)
                            ) cols
                      ORDER BY ORDINAL_POSITION
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
                      )

-- Unpivot using dynamic sql
declare @sqlStr nvarchar(max)
set @sqlStr = N'
            select AREAS,COMPANY,PRODUCT,MARKET,SUBTERRITORIES, Col as WEEK, UNITS
            from(
                select AREAS,COMPANY,PRODUCT,MARKET,SUBTERRITORIES, ' + @cols + '
                from WEEKLY_SALES
            ) as cp
            unpivot
            (
                UNITS for Col in (' + @cols + ')
            ) as up'

exec sp_executesql @sqlStr

Now, I'm looking for a way to put the result of the above query into a local table. Could you please help me in doing this? The thing I'm going to do is, to truncate the table every week and insert the data with the updated weekly sales.

I've created a table where I will put the results into, and performed the following query, but it returns the error messages mentioned below.

   DECLARE
   @queryUpdate AS NVARCHAR(MAX),
   @queryInsert AS NVARCHAR(MAX);

SET @queryInsert = 'INSERT INTO dbo.WEEKLY_SALES_ (AREAS, COMPANY, PRODUCT, MARKET, SUBTERRITORIES, ' + @cols + ')
                SELECT src.AREAS, src.COMPANY, src.PRODUCT, src.MARKET, src.SUBTERRITORIES ' + REPLACE(@cols, '[', 'src.[') + '
                FROM ('
             + @sqlStr
             + ') as src
                LEFT JOIN dbo.WEEKLY_SALES_ as dest
                   ON src.COMPANY = dest.COMPANY
                  AND src.AREAS = dest.AREAS
                  AND src.PRODUCT = dest.PRODUCT
                  AND src.MARKET = dest.MARKET
                  AND src.SUBTERRITORIES = dest.SUBTERRITORIES
                WHERE dest.COMPANY IS NULL;';

PRINT (@queryInsert);
EXECUTE (@queryInsert);

  Msg 102, Level 15, State 1, Line 2
  Incorrect syntax near '.'.
  Msg 156, Level 15, State 1, Line 12
  Incorrect syntax near the keyword 'as'.

Is there anything else am I missing?


texts:
Using SQL*PLUS, I cannot use the backspace button to erase content during a line command.

When I hit backspace, instead of erasing it writes ^H

Is there a way to configure backspace so it works as intended ?


texts:
I have a knotty (at least for me) problem to solve.

  Normalize, with respect to Boyce Codd NF, the relational scheme:

  E(A, B, C, D, E, F)

  by assuming that (A, B, C) is the unique candidate key and that the following additional functional dependencies hold:

  A,B → D
  C,D → E
  E → A

Actually what I did is:

E1(ABCEF), E2(ABD), E3(ABCDF), E4(CDE)

then I added E5(ABC) and E6(EA) getting the final result:

E1(ABC), E2(ABD), E3(CDE), E4(EA).

Unfortunately, what I wrote is wrong... Can anyone help me?


texts: I am a newcomer to database administration. Can someone briefly explain to me the difference between commands exp and expdp?


texts: How database users see their objects in sql server

texts:
I have a large SQL Server 2008 database that has many tables, stored procedures, and functions. This database also has several users.

How can I make it so that each user, after connecting to the database via Management Studio, can only see his/her related objects in the object browser BUT can execute functions, stored procedures and select tables owned by other users?


texts: How to turn off SSL for Postgres for remote connections?

texts:
After changing the pg_hba.conf file and setting it to 0.0.0.0/0 and editing the postgresql.conf file to listen_addresses='*', Postgres is still not accepting connections from a remote system.

I think turning ssl off will do the work?


texts: Should Crystal Report be allowed for certain employees for production database?

texts:
I am talking about production database. There are couple of people who use crystal on daily basis for various types of reports. Some use it to just peak at the data, other generate reports of it. Should this be allowed from security/performance/other point of view? There is no http access to our database/application, everything is internal on intranet.

What is the correct procedure here? The employee in current scenario have the password for the database. Should it be changed to Windows Authentication? Any suggestions.


texts: How to calculate AWS RDS Data Transfer Pricing

texts:
I'm looking into the possibility of switching my database to Amazon's RDS offering.

At the moment it is just MySQL installed on a normal LEMP server along with everything else the application needs to run.

While the pricing for RDS instances is clear, I can see there is pricing for data transfer out to the internet of $0.090 per GB.

Presumably this includes query results.

But I have no idea how many bytes my query results are, or how many I might use in a day. Therefore I have no idea how much switching to RDS might actually cost.

Is there any way to calculate this?

This may very well be a stupid question but I'd hate to be stung by a huge fee because I didn't fully understand the costs involved.


texts: After mysql_upgrade to 5.7, optimizer not using index on large IN clause

texts:
I have a similar issue to some other people have described.

In MySQL 5.6.23-72.1-log, a query with a large number of values in an IN clause uses an index, and takes 10 minutes to run;
In 5.7.19-17 the same query does not use an index, and takes at least 2 (sometimes more than 4) hours.

I've tried

set session eq_range_index_dive_limit=4294967295; no luck.
set @@global.max_seeks_for_key=100; still no luck.

This happened after I upgraded some of the servers.

No issues were reported by mysql_upgrade.

As I noted, I've seen other questions, but none of the proposed answers there have resolved the situation for me.

Thanks very much to Rolando for his answers so far. I went ahead and tested with this in the [mysqld] section of my.cnf

optimizer_switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=off,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=off,derived_merge=off

Alas the indexes are still not being picked up.
I tried with all the newly added options off:

duplicateweedout=off
condition_fanout_filter=off
derived_merge=off

I even tried with SET optimizer_switch='block_nested_loop=off'
because I was getting 'Using where; Using join buffer (Block Nested Loop)' and 'Using temporary; Using filesort' in my explain plan.

Still scratching my head. Thanks, The bottom line is the upgrade has caused a lot of queries to not use indexes and its a pain still. The database is almost 2TB but queries run fine in last few masters that are on 5.6


texts:
While running the insert script in sql*plus, space is removing. Can you please help us is there any plus command available in Oracle to not remove space at end of the line.

Ex: After the text 'Ameerpet,' we have space in the insert script. I have run this command through sql*plus it was success but in output space got removed.

insert into emp values(1,'
Mithrivanam, Ameerpet,
Hyderabad, Telangana, 500018
');

Output:

Mithrivanam, Ameerpet,
Hyderabad, Telangana, 500018


texts: innobackupex: What causes Slave_open_temp_tables did not become zero after waiting 15 minutes?

texts:
As you might know, in order to assure a consistent replication state, the slave should be backup only when there are no open temporary tables:

http://dev.mysql.com/doc/refman/5.5/en/replication-features-temptables.html
http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/replication_ibk.html

What I am getting is:

innobackupex:: Slave open temp tables: 10
innobackupex:: Starting slave SQL thread, waiting 3 seconds, then checking Slave_open_temp_tables again (299 attempts remaining)..
.
>> log scanned up to (543379918280)
>> log scanned up to (543382186733)
innobackupex:: Slave open temp tables: 13
innobackupex:: Starting slave SQL thread, waiting 3 seconds, then checking Slave_open_temp_tables again (298 attempts remaining)...
>> log scanned up to (543383410142)
>> log scanned up to (543384217254)
innobackupex:: Slave open temp tables: 16
innobackupex:: Starting slave SQL thread, waiting 3 seconds, then checking Slave_open_temp_tables again (297 attempts remaining)..
...

innobackupex:: Starting slave SQL thread, waiting 3 seconds, then checking Slave_open_temp_tables again (1 attempts remaining)...
>> log scanned up to (543458889721)
>> log scanned up to (543459141720)
innobackupex:: Slave open temp tables: 16
innobackupex:: Starting slave SQL thread, waiting 3 seconds, then checking Slave_open_temp_tables again (0 attempts remaining)...
>> log scanned up to (543459141720)
innobackupex:: Slave open temp tables: 16
innobackupex: Error: Slave_open_temp_tables did not become zero after waiting 900 seconds at /usr/bin/innobackupex line 379.

So the question is:

what cause the slave SQL thread keeps openning the temporary tables?
Long running transaction, ...?
Is there any way to know what the MySQL is doing with temporary
tables?


texts:
Any view serializable schedule that is not conflict serializable must contain a blind write.

(Source: Wikipedia)

Why is this statement true? I referred some links like this but am still not able to visualize the correctness of the above statement.


texts: SQL Server - Separating data, log, and TempDB files on a SAN

texts:
I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) from the SAN administrator, but given the new vendor's recommendation, is there any point in creating separate LUNs? Or would I see the same performance if everything was in one LUN since it would span all disks anyway?

Great article here, but doesn't quite address my exact situation:
http://www.brentozar.com/archive/2008/08/sql-server-on-a-san-dedicated-or-shared-drives/


texts: Group fields obtained after join as attributes of the main entity object

texts:
I work with Postgres 10.

I have 2 entities Item and Image. An Item can have multiple Image(s) so I have a Foreign key from Image to Product.

I have the following query:

SELECT name, i.image, i.type FROM item AS t
 INNER JOIN image AS i on t.id = i.item_id

By default I obtain records by Image(s), meaning the same Item can appear multiple times.

I want in the results the image to be a property of an Item object:

item.images and loop over it

It is possible to do that at the database level ?


texts: Which is the most efficient way to run a particular select query?

texts:
If I run the following code:

select PolicyNumber, MAX(decpageid) as decpageid, Risk
from StatRiskDecpages
where PolicyNumber = 'AR-0000301132-04'
group by PolicyNumber, Risk

I get the following results:

PolicyNumber    decpageid   Risk
AR-0000301132-04    41        1
AR-0000301132-04    41        2
AR-0000301132-04    37        3

All I really want to retrieve though is the policynumber and the maximum decpageid (which in this case would be 41) along with the Risk numbers (Which should be 1 and 2)

The query is also returning decpageid 37 even though it is not the maximum decpageid for the policynumber because it has a different risk.

The results I would like returned are:

PolicyNumber    DecpageID   Risk
AR-0000301132-04    41       1
AR-0000301132-04    41       2

I have figured out 2 different queries I can use to return my desired results but I don't think they are the most efficient. The queries I came up with are:

select PolicyNumber,  MAX(decpageid) as decpageid, Risk
from StatRiskDecpages
where
       PolicyNumber = 'AR-0000301132-04'
   and decpageid = (
      select MAX(decpageid)
      from StatRiskDecpages
      where PolicyNumber = 'AR-0000301132-04'
   )
;

This returns the desired results but I don't want to have to specify the policy number more then once in the query. Is there a way to call the policynumber into the sub query from the outer query?

The other query I came up with was:

select  t1.PolicyNumber,t2.DecpageID, t2.Risk
from (
   select PolicyNumber, MAX(decpageid) as decpageid
   from StatRiskDecpages
   where PolicyNumber = 'AR-0000301132-04'
   group by PolicyNumber
) as t1
   left join StatRiskDecpages as t2
      on  t1.PolicyNumber = t2.PolicyNumber
      and t1.decpageid = t2.DecpageID
;

I like this query because I only have to specify the policynumber 1 time and I can also expand the query so I can return the info for multiple policynumbers.

What I need to know is if this is the most efficient way of writing the query? It seems a little redundant. I might be wrong but I think there might be a better more efficient way of writing the query. Any suggestions?


texts: Is group replication available in Mysql community?

texts:
Group replication for Mysql server seems to be a great functionnality.
It is available from version 5.7.17 of MySql Enterprise and Cluster CGE edition.

But is it available for Mysql community ? and starting which version ?

Thanks


texts: replace values of table from values of another table where both tables don't have same no of rows

texts:
I have two tables at the moment.

Table1
   +-----+----+
     id    val
     1     a
     2     b
     3     c
     4     d
     5     e
   +-----+----+

Table2
 +----+-----+
  id    val
  1     aa
  2     bb
  3     cc
 +----+-----+

I want a resultant table to look like this replacing values in table1 from table2 like so and keeping the renaming intact

Resultant table
   +-----+-----+
     id    val
     1     aa
     2     bb
     3     cc
     4     d
     5     e
   +-----+-----+

I've tried the following code

UPDATE Table1
   SET Table1.val = Table2.val
   where Table1.id = Table2.id

but i get an error that

table2.id cannot be bound

Is there any other solution ? Bear in mind that both tables do not have same length


texts: Backing up multiple instance System Databases with distributed Maintenance Plan

texts:
I am running maintenance plans on a server, which is running several instances of SQL Server 2104.

I have set up a nightly maintenance plan to run backups for all user databases, with the backups set to create their own sub-directory.

This works absolutely fine with the current setup- with all backups residing in their own specific locations.

However, I was curious as to what would happen if databases on multiple instances shared the same database name - is there any way of adding an extra level in the maintenance plan to include the instance name?


texts: How to split a SUM column based on another BIT column?

texts:
MySQL v5.7
Having this table:

id   | sprId    | load    | calc
-----|----------|---------|---------
   1 |        1 |       1 |       7
   2 |        2 |       1 |       1
   3 |        2 |       1 |       4
   4 |        2 |       0 |      11
   5 |        3 |       1 |       2
   6 |        3 |       0 |      85
   7 |        4 |       1 |       1
   8 |        4 |       1 |      13

load is BIT.
How to split calc column sum based on the load column value, and then group by sprId.

I'm trying to get results like

 sprId    | sum_calc_load0  | sum_calc_load1
----------|-----------------|-----------------
        1 |               0 |               7
        2 |              11 |               5
        3 |              85 |               2
        4 |               0 |              14

Right now I'm doing two queries, and works, but I know for sure it can be done with subqueries, something I don't know that much.


texts: mysql_insert_id() on galera mysql cluster

texts:
I have percona mysql galera cluster. It inherited a MyISAM database.
In order for the galera to work it had to alter to innodb. Replication is working so far so good. However I have a php application which calls for mysql_isert_id() after every insert.

In a few words :
1. A big table with many inserts flying in.
2. On every insert (in the old MyISAM db) they lock the table, do the insert query, call mysql_insert_id(), and after that unlock table.

for the replication (innodb), lock table doesn't count anymore. My question is how, can we be sure about the id that mysql_insert_id() returns when there are hundreds of ofther inserts comming in a matter of seconds?

last_insert_id() and mysql_insert_id() return the same value, but as far as I understand  (last_insert) reserves ID before the actual insert(?), and mysql_insert_id do the insert and after that calls for an ID ?

I'm lost in the documentation....


texts: Are PostgreSQL cursor bound to connection?

texts:
I would like to confirm a point with PostgreSQL cursor.

I have to implement a select factory for huge result set query (more than 1Go of small-weight rows). Thus I have learn about cursor and I execute my query the following way:

I create a cursor:

DECLARE HashedCursorName CURSOR FOR SELECT * FROM BigResultSetQuery;

I repetitively fetch small amount of resultset:

FETCH 1000 FROM HashedCursorName;

When resultset is exhausted I close the cursor:

CLOSE HashedCursorName;

I need to address concurrency, I mean, many users must be able to use the the same query stored in API procedure simultaneously. When a user execute a stored query, he receives a new connection. I first have mangled my cursor name in API because I thought that if all select query are called StaticCursorName I would have a problem with two user tempting to create the same cursor or reading the same. Everything works as expected.

Then I tried to change the cursor name to a static name, and it still work. I am able to run multiple same query simultaneously. Then I wonder, is cursor bound to connection?, and then does PostgreSQL resolve concurrency by assigning cursor to session?


texts:
I am reading my userid , pwd from a file with 400 permissions to connect to the external oracle application.

Now my requirement is that i should not use the plain text pwd present in the file . I should use some encrypted pwd to connect. How i can do this ?


texts:
I was recently involved in debugging a tricky SQL Server locking issue.  We eventually figured out what was going on by studying the application code, but it would have been much easier if SQL Server had provided more information.  I'm wondering if there's a way to get that information.  Here's a simple reproduction:
create table Table1 (k int primary key)
create table Table2 (k int primary key)
go

begin transaction
insert into Table1 values (1)  -- acquire a lock
go

insert into Table2 values (2)  -- acquire a second lock
go

I don't show it, but the application was committing the transaction, as it should have.  Before the commit would happen though, it would cause blocking, and our debugging tools were only reporting the text of the last batch/statement executed--the insert into Table2 in this case--along with locks corresponding to a completely different object.  (Table1 in my example.)
Is there a way to get the SQL corresponding to all of the locks held by a session, instead of just the most recent batch?


texts: MongoDB SECONDARY replica keeps failing on STARTUP2

texts:
I'm converting standalone existing MongoDB instance to a replica set for backing up without affecting primary instance. To test minimal replica deployment I've set up two MongoDB instances on the same machine:

127.0.0.1:27017 — existing DB with 110GB data;
127.0.0.1:27018 — new instance with clean DB.

Both instances have:

different data dirs (/var/lib/mongo_r0 and /var/lib/mongo_r1) with correct read/write permissions under mongod;
separate systemd units (mongod_r0 and mongod_r1);
the same replicaSet settings in configs:

replication:
  replSetName: rs0

What I'm doing

Run both instances with systemctl start mongod_r{0,1}. r0 and r1 start correctly.
Login to first shell. 27017 is shown as SECONDARY for now.
Run rs.initiate() on 27017.
Add second node to replica set with rs.add( { host: "127.0.0.1:27018" } ).
Re-login to first shell. Now it's introduces itself as PRIMARY, and rs.status() shows:

rs0:PRIMARY> rs.status()
{
    "set" : "rs0",
    ...
    "members" : [
        {
            "_id" : 0,
            "name" : "127.0.0.1:27017",
            "health" : 1,
            "stateStr" : "PRIMARY",
            ...
        },
        {
            "_id" : 1,
            "name" : "127.0.0.1:27018",
            "health" : 1,
            "stateStr" : "STARTUP2",
            ...
        }
    ],
    "ok" : 1,
    ...
}

htop and iostat show massive increase of CPU and disk usage, which show initial replication stated.

After several minutes 127.0.0.1:27018 suddenly fails without any error. rs.status() on first instance shows:

"members" : [
    {
        "_id" : 0,
        "name" : "127.0.0.1:27017",
        "health" : 1,
        "stateStr" : "PRIMARY",
        ...
    },
    {
        "_id" : 1,
        "name" : "127.0.0.1:27018",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "lastHeartbeatMessage" : "Connection refused",
        "configVersion" : -1
        ...
    }
],

It I try to get size of DB dirs, it shows:

108G    /var/lib/mongo_r0/
3.5G    /var/lib/mongo_r1/

which means at least some parts of data were replicated.

What I'm doing wrong? I've tried two-machines replica and three-ones config with ARBITER instance, but initial replication always fails in several minutes in STARTUP2.


texts:
I'm setting up a test case to prove a certain deadlock scenario and require some insight on what is going on.
I have a heap table, conventiently called HeapTable. This table is updated by 2 transactions simulateously.

Transaction 1:

BEGIN TRAN

UPDATE HeapTable
SET FirstName = 'Dylan'
WHERE FirstName = 'Ovidiu';

WAITFOR DELAY '00:00:15';

UPDATE HeapTable
SET FirstName = 'Bob'
WHERE FirstName = 'Thierry';

ROLLBACK TRANSACTION

Transaction 2:

BEGIN TRAN

UPDATE HeapTable
SET FirstName = 'Pierre'
WHERE FirstName = 'Michael';

ROLLBACK TRAN

I fire off transaction 1 first, closely followed by transaction 2. As expected transaction 1 will claim some exclusive locks, together with some intent exclusive ones. Transaction 2 will come in and request an Update lock on the same RID:

spid dbid   ObjId       IndId   Type    Resource     Mode   Status
55    5     711673583   0       RID     1:24336:10   X      GRANT
57    5     711673583   0       RID     1:24336:10   U      WAIT

I was kind of surprised to see the second transaction ask for an Update lock on the same RID, since I thought this pointed to a single record & both update statements handle different data. I was somehow expecting a conflict on page level instead.

When the second update of transaction 1 kicks in transaction 2 will be seen as deadlock victim resulting in a rollback of transaction 2 & completion of transaction 1.

Can someone explain me why the second transaction would require an update lock on the same RID although updating a different record?

I know how to fix this (e.g. with an index). I'm not looking for a fix, I'm actually looking for an explanation to why 2 Updates handling different records in a heap would want to lock the same RID. I am using read committed isolation. There are no nonclustered indexes on the table.


texts:
We need to migrate the storage of our production database. What is the appropriate method of doing so and what specific/generic precautions do we need to take??

Database Configuration:
Volume Manager - ASM using Raw Disks. ASM External Redundancy as storage is published from storage subsystemDatabase version - Oracle 10g R2Database Size - 5TB approx.

Existing Storage (source): HP MSA 2312sa Dual Controller. Directly connected to our database server (no fc or ethernet switch).
The Oracle binaries are also on this storage

New Storage (Target): HP EVA6300 FC storage. This storage will be connected to the hosts via FC switches.

Can we use host based storage migration like VxVM Plex attach/dettach to copy data from source LUNs to the target LUNs?
Do we have to use Oracle RMAN backup and recovery method for storage migration?


texts:
GI have a question about performance of SQL over MPLS.

I have a client with 3 localizations. We want to centralize the server environment and migrate all of SQL databases to headquarter. Our client uses aplication based on SQL Server 2017 Standard. Between locations are MPLS Connection 100/100, latency (ping) between locations and headquarter are 5-7 ms. We anticipate that there will be about 150 connections to the database at one time. We're worried about the performance of this.

Of course we want to build cluster SQL based on SQL 2017 Standard and Dell storage array with SSD disks for SQL DB. Does anyone have experience in similiar scenario and could tell about it?


texts: How to get list of users on all instance's databases? excluding the mirrors!

texts:
For an audit of our MSSQL databases, I have been asked to provide a list of all users for each database running on each instance.

There is an existing stack overflow question

which advised to use...

exec sp_MSforeachdb 'select * from ?.sys.sysusers'

However, it hits a few problems.

1) If any of the databases are a mirror, the script errors as below and doesn't return any results.

"The database "###" cannot be opened. It is acting as a mirror database."

2) If any of the databases have a special character in their name (eg. a hyphen -), again it errors and doesn't return results.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

3) The results are all in separate tables.  Ideally I would like it to just output 1 table of database and users.

Can anyone help adjust the code to achieve the above?

Thanks


texts:
I made a lot of research about how to maintain indexes in MySQL to prevent fragmentation and to optimize somehow the execution of some queries.

I am familiar with that formula that calculates the ratio between the max space available for a table VS the space used by data and indexes.

However my main questions are still unanswered. Perhaps this is due to the fact that I am familiar with index maintenance in SQL Server, and I tend to think that in MySQL it should be somehow similar.

In SQL server, you can have several indexes, and each one of it can have different levels of fragmentation. Then you can pick up one and perform a 'REORGANIZE' or 'REBUILD' operation in that particular index, without affecting the rest.

To the best of my knowledge, there is no 'table fragmentation' as such, and SQL Server doesn't provide any tool to fix the 'table fragmentation'. What it does provide are tools to check index fragmentation (understood like the ratio between the number of pages used by an index VS the fullness of that page and contiguity), as well as the internal and external fragmentation.

All of that is quite straightforward to understand, at least for me.

Now, when it comes the turn to maintain indexes in MySQL, there only exist the concept of 'table fragmentation, as mentioned above.

A table in MySQL can have several indexes, but when I check the 'fragmentation ratio' with that famous formula, I don't see the fragmentation of each index, but the table as a whole.

When I want to optimize the indexes in MySQL, I don't choose a particular index to operate on (as in SQL Server). Instead, I do an 'OPTIMIZE' operation in the whole table, which presumably affects all the indexes.

When the table is optimized in MySQL, the ratio between the space used by data + indexes VS the overall space is reduced, which suggest some kind of physical re-organization in the hard drive, which translates into a reduction of the physical space. However, index fragmentation is not only about physical space, but the structure of the tree that has been changed over the time due to inserts and updates.

Finally, I got a table in InnoDB/MySQL. That table has 3 million records, 105 columns and 55 indexes. It is 1.5GB excluding indexes, which are 2.1GB.

That table is being hit thousands of times ever day for updating, insertion (we don't actually delete records).

That table has been created years a go and I know for sure that nobody is maintaining indexes whatsoever.

I was expecting to find a huge fragmentation in there, but when I perform the fragmentation calculation as prescribed

free_space / (data_length + index_length)

it turns out that I have only a 0.2% fragmentation. IMHO that is quite unrealistic.

So the big questions are:

How do I check fragmentation of a particular index in MySQL, not the table as a whole
Does OPTIMIZE TABLE actually fix the internal / external fragmentation of an index as in SQL Server?
When I optimize a table in MySQL, does it actually rebuilds all the indexes on the table?
Is it realistic to think that reducing the physical space of an index (without rebuilding the tree itself) actually translates into a better performance?


texts: Installing MySQL 5.6 Community Debug Info

texts:
I have setup my yum repository as described here https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/, and am wondering where I can install the debuginfo packages. Looking here http://repo.mysql.com/yum/mysql-5.6-community/el/6/x86_64/ it seems there are none, but I wonder if anyone knows if I am missing something obvious.

I have already enabled /etc/yum.repos.d/CentOS-Debuginfo.repo and have tried to do 'yum search mysql' for any such packages. I've tried asking in #mysql irc, but with no response.

Do I have to build the rpms by hand in order to get debug symbols?


texts: How does merge replication work with SQL Server CE

texts:
I have been trying out merge replication with SQL Server databases, and was just wondering how merge replication works on SQL Server CE?

For instance merge replication with SQL Server databases normally relies on triggers, and SQL Server CE doesn't have any triggers.

Also do tables such as MSmerge_conflicts_info and sysmergesubscriptions etc exist with the SQL Server CE version of merge replication?


texts: Why is this join cardinality estimate so large?

texts:
I am experiencing what I think is an impossibly high cardinality estimate for the following query:

SELECT dm.PRIMARY_ID
FROM
(
    SELECT COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) PRIMARY_ID
    FROM X_DRIVING_TABLE dt
    LEFT OUTER JOIN X_DETAIL_1 d1 ON dt.ID = d1.ID
    LEFT OUTER JOIN X_DETAIL_LINK lnk ON d1.LINK_ID = lnk.LINK_ID
    LEFT OUTER JOIN X_DETAIL_2 d2 ON dt.ID = d2.ID
    LEFT OUTER JOIN X_DETAIL_3 d3 ON dt.ID = d3.ID
) dm
INNER JOIN X_LAST_TABLE lst ON dm.PRIMARY_ID = lst.JOIN_ID;

The estimated plan is here. I am working on a statistics copy of the tables so I cannot include an actual plan. However, I don't think that it's very relevant for this problem.

SQL Server estimates that 481577 rows will be returned from the "dm" derived table. It then estimates that 4528030000 rows will be returned after the join to X_LAST_TABLE is performed, but JOIN_ID is the primary key of X_LAST_TIME. I would expect a join cardinality estimate between 0 and 481577 rows. Instead, the row estimate appears to be 10% of the number of rows I would get when cross joining the outer and the inner tables. The math for this works out with rounding: 481577*94025*0.1 = 45280277425 which is rounded to 4528030000.

I am primarily looking for a root cause for this behavior. I am interested in simple workarounds as well, but please do not suggest changing the data model or using temp tables. This query is a simplification of logic within a view. I know that doing COALESCE on a few columns and joining on them isn't a good practice. Part of the goal of this question is to figure out if I need to recommend that the data model be redesigned.

I am testing on Microsoft SQL Server 2014 with the legacy cardinality estimator enabled. TF 4199 and others are on. I can give a full list of trace flags if that ends up being relevant.

Here is the most relevant table definition:

CREATE TABLE X_LAST_TABLE (
JOIN_ID NUMERIC(18, 0) NOT NULL
    CONSTRAINT PK_X_LAST_TABLE PRIMARY KEY CLUSTERED (JOIN_ID ASC)
);

I also scripted out all of the table creation scripts along with their statistics if anyone wants to reproduce the issue on one of their servers.

To add a few of my observations, using TF 2312 fixes the estimate, but that is not an option for me. TF 2301 does not fix the estimate. Removing one of the tables fixes the estimate. Bizarrely, changing the join order of X_DETAIL_LINK also fixes the estimate. By changing the join order I mean rewriting the query and not forcing the join order with a hint. Here is an estimated query plan when just changing the order of the joins.


texts:
We have a table to store the Events.

Table has got a schema like

EventID INT Primary Key
EventName NVARCHAR(100)
StartDate Datetime
EndDate Datetime

So in the front end we want to show the events according to start time and end time.
From front end we are passing StartDate and EndDate as parameters to take the value.

So far we have taken like

   Select * from tbl_Events
   where ((startDate BETWEEN @start AND @end) OR (EndDate BETWEEN @start AND @end))

We have got 2 views to show the events -

A month view - which show all events in the month
A day view showing events of that day

Suppose i got an event with StartDate as 23 October 2012 and EndDate as 27 October 2012.

And i am passing @StartDate as 24 Oct 2012 and @EndDate as 25 OCt 2012

According to the above query its working in the Month view. But its not working in the day view.

Can anyone please help me on this to modify the query to work in both cases


texts: T-SQL: How to avoid losing digits when divided by a number

texts:
We have a product 'A' which is billed once but delivered to the customer in multiple phases example 'A1', 'A2', 'A3'.
I need a report for these phases

Suppose Amount of 'A' is 100, so to fetch amount of 'A1', 'A2', 'A3' I divide the amount by 3.

Actual Output
Description Amount

A1          33.33
A2          33.33
A3          33.33

But the total comes to be 99.99 which is creating a problem for the accountants.

Is there a built-In function or a method where I could fetch the output as given below.

Desired Output

Description Amount
A1          33.33
A2          33.33
A3          **33.34**

I do not have phase wise data stored(33.33).

I have two tables: Product table, which has Name of the product and Amount i.e. 'A' and '100' and the other is the Frequency table which has the product and frequency i.e 'A' and '3'. I want any one of these 3 phases to have amount as '33.34' such that when I add these 3 amounts the total is '100' and not '99.99'


texts: Is there a way to stop MySQL Replication on the master?

texts:
I want the master to stop replicating data to the slave. I know I can do that on the slave with STOP SLAVE;, but I wonder if there is a way to do it in the master.

One possible solution might be to change the server_id to 0, but in this case I'll have to restart mysql in the master for changes to take effect.

What I'm looking for is a statement like STOP MASTER;.


texts: Finding rows where a column contains [ and ] characters

texts:
I need to get results where the column contains a string that includes [ ]. When I use LIKE, it takes the [] as a wildcard sequence, and doesn't work as I need.

For example, I want to search through a column called Address and get any values where the string contains [Sydney]:

SELECT * FROM Location
WHERE Address LIKE '%[Sydney]%'

I can't use CONTAINS(), because this is not a full-text indexed column.


texts: Is there a routine in MySQL similar to fn_dblog in SQL-server?

texts: I already search about fn_dblog and it is about transaction log and it is used in SQL SERVER. if there any way to do transaction log in MySQL like fn_dblog? i can't find one


texts: For a MySQL query, how do you determine physical and logical I/O?

texts: I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index selection but I want more information from the MySQL server, if possible).


texts: FTS is slow when searched word appears frequently in rows

texts:
I have a message table with a content column indexed for full text search:

create index message_fts on message using GIN(to_tsvector('english', content));

The problem I have and that I'd like to understand is that searches involving frequent words are very slow.

Here's a normal fast enough query on a rare "word":

miaou=> explain analyze select count(*) from message where (to_tsvector('english', content) @@ to_tsquery('fajre'));
                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=16808.35..16808.36 rows=1 width=8) (actual time=6.565..6.565 rows=1 loops=1)
   ->  Gather  (cost=16808.13..16808.34 rows=2 width=8) (actual time=1.608..6.556 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=15808.13..15808.14 rows=1 width=8) (actual time=0.456..0.457 rows=1 loops=3)
               ->  Parallel Bitmap Heap Scan on message  (cost=293.28..15803.69 rows=1776 width=0) (actual time=0.453..0.453 rows=0 loops=3)
                     Recheck Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('fajre'::text))
                     ->  Bitmap Index Scan on message_fts  (cost=0.00..292.22 rows=4262 width=0) (actual time=0.954..0.954 rows=0 loops=1)
                           Index Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('fajre'::text))
 Planning time: 0.249 ms
 Execution time: 8.400 ms
(11 rows)

And here's a very slow query on a frequent term:

miaou=> explain analyze select count(*) from message where (to_tsvector('english', content) @@ to_tsquery('faire'));
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
<other identical notices removed for clarity>
                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=189901.34..189901.35 rows=1 width=8) (actual time=47192.053..47192.053 rows=1 loops=1)
   ->  Gather  (cost=189901.12..189901.33 rows=2 width=8) (actual time=47188.506..47192.042 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=188901.12..188901.13 rows=1 width=8) (actual time=47183.158..47183.158 rows=1 loops=3)
               ->  Parallel Bitmap Heap Scan on message  (cost=2012.04..188702.23 rows=79558 width=0) (actual time=72.503..47152.312 rows=66781 loops=3)
                     Recheck Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('faire'::text))
                     Rows Removed by Index Recheck: 1329552
                     Heap Blocks: exact=12673 lossy=22516
                     ->  Bitmap Index Scan on message_fts  (cost=0.00..1964.30 rows=190940 width=0) (actual time=64.740..64.740 rows=200347 loops=1)
                           Index Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('faire'::text))
 Planning time: 0.268 ms
 Execution time: 47199.290 ms
(13 rows)

It can be noticed that the index correctly finds the relevant records (200347 of them).

What should I fix or look at ? I'm especially surprised by the many notices I get when just querying, given that the matching information is already given by the index.


texts:
ip addr|grep inet
inet 192.168.8.47/24 brd 192.168.8.255 scope global ec1
inet 192.168.8.45/24 brd 192.168.8.255 scope global secondary ec1:1

I got like this

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 192.168.8.47
                  Master_User: repl
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000126
          Read_Master_Log_Pos: 82811044
               Relay_Log_File: c60-relay-bin.000002
                Relay_Log_Pos: 426371
        Relay_Master_Log_File: mysql-bin.000125

the 8.47 ,will be remove on the master machine ,and then I do

on slave
stop slave ;
change master to master_host='192.168.8.45';
start slave ;

and then I got error like

Last_Error: Error 'Field 'xxx' doesn't have a default value' on query.

what the database do ? when change master master_host only?

how to resolve this problem ,and the right way to change master (change master to master_host,user,root..etc)?

on the slave

mysql> select @@sql_mode;
+------------------------------------------------------------------------------------+
| @@sql_mode                                                                         |
+------------------------------------------------------------------------------------+
| IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


texts:
I am on SQL 2014 Standard doing offline DBCC CHECKDBs (so on a box other than production). For the most part, my process is going pretty quick but for some reason I have this small DB (6gbs) and its taking hours to do the DBCC. Last time it ran it took 9 hours. It seems to freeze at different %'s completion when checking sys.dm_exec_requests. On this same server I'm doing CHECKDBs on databases in the terabytes range without a problem. I am using the following hints in the CHECKDB at this time (and, yes, eventually I'll do full instead of physical only)

WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY, TABLOCK

What could be messed up with this one DB that is causing it to take forever?


texts: List all Oracle procedures/functions (including in packages)

texts:
I want to list all procedures/functions in an Oracle server, including procedures/functions in packages. Am I doing it right?

SELECT
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.PROCEDURE_NAME ELSE up.OBJECT_NAME END AS name,
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.OBJECT_NAME ELSE NULL END AS package
FROM USER_PROCEDURES up
LEFT JOIN USER_IDENTIFIERS ui ON up.PROCEDURE_NAME = ui.NAME
WHERE up.OBJECT_TYPE = 'PROCEDURE' OR ui.TYPE = 'PROCEDURE';

SELECT
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.PROCEDURE_NAME ELSE up.OBJECT_NAME END AS name,
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.OBJECT_NAME ELSE NULL END AS package
FROM USER_PROCEDURES up
LEFT JOIN USER_IDENTIFIERS ui ON up.PROCEDURE_NAME = ui.NAME
WHERE up.OBJECT_TYPE = 'FUNCTION' OR ui.TYPE = 'FUNCTION';


texts:
I have Log Shipping setup from Primary Server A to Destination B. Server B is both Destination and Monitoring server.
Yesterday, we restarted both servers around the same time for patching. On the Log Shipping Status report, it says Primary Source Backups are Not Up to Date and Failing. So I run the LS backup manually, and it still says backup has not occurred in 2000 minutes??

The Destination Monitoring Server says Copies and Restores are Success.
How do I resolve this problem on Source Server A?


texts:
My source data comes from multiple OLTP databases where the primary key is an identitiy column. We anticipate adding data centers and/or splitting the databases by product. Currently i have one system incrementing from -9223372036848909236, 1 and the other is incrementing from 1134873337, 1

I pull the data into my data warehouse from each of my sources and the data is partitioned on the UTCDate which is also a source value.

Filter criteria will always include the UTCDate. May or maynot have ID as criteria

Question:
will i have less fragmentation on my clustered index UTCDate, ID or  ID, UTCDate


texts: Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way

texts:
Consider a CLR stored procedure that accepts an XML parameter and calls another stored procedure in the same schema:

[SqlFunction(DataAccess = DataAccessKind.Read,
     IsDeterministic = false, IsPrecise = true,
     SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlBoolean TestTest(SqlXml Data)
{
    using (var c = new SqlConnection("context connection=true"))
    {
        c.Open();

        using (var cmd = new SqlCommand(@"select case when exists(select 0 from [testing].WillBeCalledByCLR(@d)) then 1 else 0 end;", c))
        {
            var p = cmd.Parameters.Add("@d", SqlDbType.Xml);

            p.Direction = ParameterDirection.Input;
            if (Data.IsNull)
                { p.Value = DBNull.Value; }
            else
                { p.Value = Data; }  // Or Data.Value

            return (bool)cmd.ExecuteScalar();
        }
    }
}

This CLR function is visible in SQL as:

create function [testing].[TestTest] ( @data xml )
returns bit
WITH CALLED ON NULL INPUT
AS
EXTERNAL NAME [Test].[Test.Test].[TestTest]

The stored function it calls is this:

create function testing.WillBeCalledByCLR (@x xml = null)
returns table
as
return (
  select 1 as one, 2 as two, 'three' as three
);

In this setup, if I call the CLR function like this:

if testing.TestTest(null) = 1
begin
  select 'Meaningful actions';
end;

or like this:

declare @res bit = testing.TestTest(null);

or like this:

declare @res bit;
set @res = testing.TestTest(null);

or like this:

declare @res bit;
select @res = testing.TestTest(null);

then I get:

  Msg 2905, Level 25, State 1, Line 6

  Msg 0, Level 20, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

But if I call it like this:

select testing.TestTest(null);

or like this:

declare @res bit;
set @res = (select testing.TestTest(null));

I get a proper value back (e.g. 1).

If, instead of null, I pass an empty string '', the function is successfully called in all cases.

Why? Did I do something wrong in my CLR function?

  Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
  Sep 16 2010 19:43:16
  Copyright (c) 1988-2008 Microsoft Corporation
  Standard Edition (64-bit) on Windows NT 6.0  (Build 6002: Service Pack 2) (VM)


texts: Tuning Postgres for Single connection use? Or is postgres wrong tool?

texts:
Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions?

I'm a social scientist looking to use Postgres not as a database to be shared by multiple users, but rather as my own tool for manipulating a massive data set (I have 5 billion transaction records (600gb in csv) and want to pull out unique user pairs, estimate aggregates for individual users, etc.).

All the advice I can find online on tuning (this, this, this etc.) is written for people anticipating lots of concurrent connections. Anyone have basic rules of thumb for someone working on a database alone for data manipulation?

UPDATE:
- This also means almost no writing, except to creation of new tables based on selections from the main table. (Apparently that's important -- thanks Erwin!)

Updated 2:
- I'm on a Windows 8 VM with 16gb ram, SCSI VMware HD, and 3 cores if that's important.


texts: Is it possible to find the values of MySQL variables if I do not have SELECT privileges on mysql.user?

texts:
I am getting a 'msyql server has gone away' error sometimes when doing a large binary insert, so I wanted to check the settings on mysql, things like wait_timeout and max_allowed_packet. However

show create table mysql.user

returns

[Err] 1142 - SELECT command denied to user '557574_prod'@'96.25.95.162' for table 'user'

I'd rather not hit up tech support whenever I want to know some of the relevant settings, so I was wondering if there might be a workaround?


texts: Oracle trigger after insert error ORA-01403: no data found

texts:
I'm trying to make a trigger that select a value inserted in other table and concat with other value in an insert on other table

that's my code

CREATE TABLE  "SEX"
   (
    "ID" NUMBER,
    "CS" VARCHAR2(255),
    "SEX" VARCHAR2(255),
     CONSTRAINT "SEX_PK" PRIMARY KEY ("ID") ENABLE,
     CONSTRAINT "SEX_UK" UNIQUE ("CS", "SEX") ENABLE
   )
/

CREATE TABLE  "NAC"
   (
    "ID" NUMBER,
    "CN" VARCHAR2(255),
    "NAC" VARCHAR2(255),
    "SEX" NUMBER,
     CONSTRAINT "NAC_PK" PRIMARY KEY ("ID") ENABLE,
     CONSTRAINT "NAC_UK" UNIQUE ("CN", "SEX") ENABLE
   )
/
ALTER TABLE  "NAC" ADD CONSTRAINT "NAC_FK" FOREIGN KEY ("SEX")
      REFERENCES  "SEX" ("ID") ON DELETE CASCADE ENABLE
/

CREATE INDEX  "NAC_IDX" ON  "NAC" ("SEX")
/

CREATE OR REPLACE TRIGGER  "BI_NAC"
  before insert on "NAC"
  for each row
begin
  if :NEW."ID" is null then
    select "NAC_SEQ".nextval into :NEW."ID" from sys.dual;
  end if;
end;

/
ALTER TRIGGER  "BI_NAC" ENABLE
/

CREATE OR REPLACE TRIGGER  "BI_SEX"
  before insert on "SEX"
  for each row
begin
  if :NEW."ID" is null then
    select "SEX_SEQ".nextval into :NEW."ID" from sys.dual;
  end if;
end;

/
ALTER TRIGGER  "BI_SEX" ENABLE
/

CREATE TABLE  "PRUEBA1"
   (
    "ID" NUMBER NOT NULL ENABLE,
    "SEXO" NUMBER(1,0) NOT NULL ENABLE,
    "NACIONALIDAD" NUMBER(1,0) NOT NULL ENABLE,
    "CEDULA" NUMBER(8,0) NOT NULL ENABLE,
     CONSTRAINT "PRUEBA1_PK" PRIMARY KEY ("ID") ENABLE,
     CONSTRAINT "PRUEBA1_UK" UNIQUE ("NACIONALIDAD", "CEDULA") ENABLE
   )
/
ALTER TABLE  "PRUEBA1" ADD CONSTRAINT "PRUEBA1_FK01" FOREIGN KEY ("SEXO")
      REFERENCES  "SEX" ("ID") ENABLE
/
ALTER TABLE  "PRUEBA1" ADD CONSTRAINT "PRUEBA1_FK02" FOREIGN KEY ("NACIONALIDAD")
      REFERENCES  "NAC" ("ID") ENABLE
/

CREATE INDEX  "PRUEBA1_IDX" ON  "PRUEBA1" ("SEXO", "NACIONALIDAD")
/

CREATE OR REPLACE TRIGGER  "BI_PRUEBA1"
  before insert on "PRUEBA1"
  for each row
begin
  if :NEW."ID" is null then
    select "PRUEBA1_SEQ".nextval into :NEW."ID" from sys.dual;
  end if;
end;

/
ALTER TRIGGER  "BI_PRUEBA1" ENABLE
/

CREATE OR REPLACE TRIGGER  "PROCESAR_USUARIO"
    AFTER INSERT ON PRUEBA1
    FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    pck_usuarios.v_id := :NEW.ID;
    SELECT NAC.CN INTO pck_usuarios.v_nc FROM NAC, PRUEBA1 WHERE :NEW.NACIONALIDAD = NAC.ID;
    pck_usuarios.v_c := :NEW.CEDULA;
    pck_usuarios.v_u := pck_usuarios.v_nc || pck_usuarios.v_c;
    INSERT INTO PRUEBA2(ID, USUARIO, PRUEBA1) VALUES(pck_usuarios.v_id, pck_usuarios.v_u, pck_usuarios.v_id);
END;
/
ALTER TRIGGER  "PROCESAR_USUARIO" ENABLE

CREATE TABLE  "PRUEBA2"
   (
    "ID" NUMBER NOT NULL ENABLE,
    "USUARIO" VARCHAR2(9) NOT NULL ENABLE,
    "PRUEBA1" NUMBER NOT NULL ENABLE,
     CONSTRAINT "PRUEBA2_PK" PRIMARY KEY ("ID") ENABLE,
     CONSTRAINT "PRUEBA2_UK01" UNIQUE ("USUARIO") ENABLE,
     CONSTRAINT "PRUEBA2_UK02" UNIQUE ("PRUEBA1") ENABLE
   )
/
ALTER TABLE  "PRUEBA2" ADD CONSTRAINT "PRUEBA2_FK" FOREIGN KEY ("PRUEBA1")
      REFERENCES  "PRUEBA1" ("ID") ENABLE
/

then when I do

INSERT INTO PRUEVA1(SEXO, NACIONALIDAD, CEDULA) VALUES (1,4,15040302);

the result is

error ORA-01403: no data found ORA-01403: no data found ORA-06512: at "ALFA.PROCESAR_USUARIO", line 5 ORA-04088: error during execution of trigger 'ALFA.PROCESAR_USUARIO'

How can I fix it?


texts: Moving Report Server Databases to Another Drive

texts:
Currently our Reporting Server Databases are in C:
So would like to place them in E:\RS\

Can I know how can I move the ReportServer and ReportServerTempDB Database files from one location to another and things I should keep in mind before this operation.


texts: How do I configure multi master replication on WAMP?

texts: I want to configure mysql multi master replication, but I've not seen any comprehensive article or tutorial on how to do that on WAMP. I will be grateful if some will point  me to a tutorial, or better still show me how to achieve that.


texts: Mysql Innodb cluster does not show mysql server instances in Mysql shell

texts:
I followed docker innodb cluster manual method to create an innodb cluster. Now in mysql server instance, by running select * from performance_schema.replication_group_members, I can see the group contains 3 nodes:

However, mysql shell gives only 1 node in the cluster:

Why they are not identical?  I think the replication group is the same thing as an innodb cluster in this example.


texts: What computations can NOT be performed in standard SQL?

texts: I know loops cannot be expressed in standard SQL. What other forms of processing can it not perform?


texts: Sybase Adaptive Server Anywhere connection problems

texts:
In my work, we have a software system based on Sybase Adaptive Server Anywhere version 6 installed on Windows Server 2008. The system works fine, but after a period of time from system start, problems start. This period of time is variable: maybe an hour, maybe a day; the maximum stability period was one week.

The problem is: the logged-in users stay connected, but newly opened PCs the system can't login and the ODBC data sources window appears with a message, "can't find the server".

This problem, til now and as far as I know, has only one solution: to reboot the SQL engine, or reboot the server. This is so terrible because all connected PCs will stop the system and this may be in the middle of payment operations and so on.

We called technical support of the system, they can do nothing except clearing the log as it reached 21 MB. They said there is a very large amount of data in the log. When he cleared the log, the system stayed stable for one week, but now it started stopping again.

Also they mentioned that it might be because of cache memory in parameter -c150MB. They tried 300 instead of 150 and 120 but no way. I tried to add the parameter -ti1400 to stop automatic disconnecting after period of idle time. I expanded it to about 24 hours!

I also asked the system software company to install a newer version of Adaptive Server Anywhere, but they didn't reply to that.

What shall I do to solve this?


texts: SSIS for/each loop to ETL tables across databases

texts:
I have 100 pre-written SQL Server table insert statements, to pull data from a Progress database via openquery into 100 SQL Server tables e.g.

insert into table1 (col1 varchar(50), col2 varchar(10)) select X,Y from aprogresstable
insert into table1 (col1 varchar(40), col2 int, col3 varchar(15)) select X,Y,Z from anotherprogresstable
for 100 tables..

However, this is slow over openquery. Therefore, I'd like to import these data nightly from Progress into existing SQL Server tables using SSIS.

What is the best way to achieve this, without needing to create 100 data flow components (e.g. 100 sources, 100 destinations).

SQL Server Import/Export wizard is an option here but I've found it has data mapping issues with date fields.

Therefore, is it possible to create a For/Each loop to loop through 100 SQL insert statements via SSIS to pull data from Progress/Push to existing SQL Server Tables?

Thanks!


texts:
I have a issue,

I want to set the ID column, where the ID should start from 11-2 then it should auto- increment by 1 like

11-3, 11-4.

I am using sql-server 2008 R2. Please help


texts: Database normalization: Describing trait - foreign key to table, or varchar field with value?

texts:
My colleagues and I are having a discussion about the normalization of descriptive traits in the database, such as "status" or "type". Let's call the central table of the discussion "Order".

In my regular design-approach, I would define another table, "OrderStatus", to describe the status of an order, and then create a foreign key with a relationship , i.e. "OrderStatusID", on the "Order" table.

This would give me referential integrity. I'd be able to join the status at all times, and my possible values are always present in the "OrderStatus" table.

My colleague doesn't like this degree of normalization, so he'll instead define a varchar field, "OrderStatus" on the "Order" table. This field would contain the values directly.

The possible values of status are defined in his application, more specifically in an Enum of OrderStatuses, and as such, are not available to me unless I have access to the source code of said application.

I'm used to having the entire context of the database exist in the database as relationships and tables, and having to write "WHERE OrderStatus = 'Sold'" as opposed to "WHERE OrderStatusID = 3" bugs me.

What do think? I'm looking for pro's and con's against both approaches, but I'm primarily concerned about performance and readability/maintainability.


texts: PostgreSQL: Administration as Non-Superuser

texts:
I have a database on a shared PostgreSQL server. I possess the ownership of the database and the CREATEROLE privilege and would probably be able to get a few other privileges if I request them but I won't be able to get superuser.
The database should be used by different users that I will create. I was wondering if it would be possible for me to actually maintain the system. A few issues I am seeing:

Other users can and should be able to create their own tables. Would it be possible for me as the database owner to modify/delete them?
Can I create full backups? I.e., would I even be able to see all tables if the creating user doesn't grant me the permissions explicitly? Could I at least set up that privileges are granted to me per default for all users?

As said, I don't have superuser privileges but I might be able to request certain changes to the system and get additional privileges.


texts: Need help in designing a database with a need of adding new columns or tables dynamically

texts:
I want to design a vaccination chart for children.

Each child will have his own schedule and history of administered dates.

I am planning to store the administered dates of each vaccine right in the table of children, with each column storing the storing the administered dates of each vaccine. ( like child_chart(child id, vaccine1_administered_date, vaccine2_administered_date, ...)

Now if a new vaccine is found and to be added to chart, I need to make a new column dynamically.

Is it the only way or can I do it in any other way without dynamically creating a column.

This is my first project using databases. Please help me. Thank you


texts: One of the table of MySQL database is missing

texts:
One of the tables of my database is missing. I didn't drop it but it just went missing.
If I make a query on that table of database. It says

Query 1 ERROR: Tablespace is missing for table courtData.Data.

If I use the command SHOW TABLES it shows me all three tables. When I go to the DB folder, It has only 2 files.
The disk size used on the drive is 15GB. I don't have much going on the server other than DB so I am guessing that the db is still there somewhere on the disk.


texts: How to tell if a backup log chain is broken?

texts:
I had a situation where the Native Backups were being made on a Server.
I happened to see in msdb that there was a third party backup tool (AppAssure) that was also taking VSS (kind-of) backup to virtual device.

At some interval, the AppAssure (backup being made to VIRTUAL DEVICE) was doing a COPY_ONLY backup and at some other interval it was doing a FULL backup breaking the log chain.

Is there any way(T-SQL query) to know when a backup log chain is broken?

Here is a screenshot of the situation from February.


texts: pfile - client parameter file - starting an instance remotely

texts:
I do not understand how we can start an Oracle instance remotely using a pfile.

If we ssh into the Oracle server and issue:

startup pfile='/path/to/pfile';`

the pfile is on the server side.

How can we actually startup a database remotely using a pfile?


texts: Resource Waits: Mathematical link between Wait Time and Average Waiter Count

texts:
Just curious, per BOL

Wait Time (ms/sec) is the wait time in milliseconds for all tasks waiting for one or more resources in the category since last update.

Average Waiter Count is the number of tasks waiting.

Is it a coincidence that on my server Average Waiter Count is always Wait Time / 1000 rounded to nearest 10th?  Is there a link here that makes it so?  Am I going to be embarrassed when the answer is extremely logical but for some reason escaping me at the moment?

i.e here are sample values from the last 15 minutes for latch but other wait categories are the same (when any values exist) - I've noticed it for quite some time (much longer than last 15 mins) it just never piqued my interest enough to investigate until now.

Wait Time         Average Waiter Count
______________________________________
576               0.6
1403              1.4
1856              1.9
1233              1.2
1365              1.4

It just seems to me that since wait time is a cumulative count and waiters is an average the numbers shouldn't follow a ratio of each other as tightly as they appear.


texts: boolean purpose column char(0) or tinyint(1)

texts:
For Boolean purpose column which one is better: nullable char(0) or tinyint(1).

I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said:

  "If you want to store a true/false value in a single bit of storage
  space, another option is to create a nullable CHAR(0) column. This
  column is capable of storing either the absence of a value (NULL) or a
  zero-length value (the empty string). "

which one is better for size, performance, indexing or ...

UPDATE:
I found this link useful for this question:
Efficient Boolean value storage for Innodb Tables


texts:
I manage a MySQL database through phpMyAdmin. I have a table for cinema cupouns tickets. I upload a bunch of tickets, and then I assign tickets to users.

Each ticket contains,

id          -> int(11) unsigned PRI auto_increment // should it be bigger?
ticket_id   -> varchar(20) // should this be the primary key?
type        -> enum('3d', 'common', 'monster')
price       -> int(11)
user        -> varchar(16)
expire      -> date
updated     -> on update CURRENT_TIMESTAMP
created     -> timestamp

So, when tickets are created, user fields are empty, that's how I check how many are available. When a user needs tickets I select the top ones (with empty user value) limiting by the quantity and assign them to the user in the user field.

Sample data

ID  ticket_id               type    price   user        expire          updated                 created
1   V100070263526SA5K3W     3d      45      27060192    2015-07-30      2015-04-01 12:10:06     2015-03-31 08:39:19

I wonder if I'm doing fine, or how should I optimize the design in terms of a database table, considering a bunch of 400 new ticket uploads happen every month.


texts: Speed Up Garbage collection in FileStream process

texts:
In my current process, we have two production site SQL Servers 2014 (Sync), and one DR site SQL Server 2014 (ASync) in one WSFC.

I will be hosting 5-6 databases for AOAG set up, out of which one of the databases will be a filestream database.

Earlier (SQL Server 2008 R2) we used to run a log backup followed by a checkpoint using a log shipping backup job to speed up the filestream garbage collection process.

Now because I will add a filestream database to AOAG (SQL Server 2014), how should I run log backup and checkpoint?

Will sp_filestream_force_garbage_collection alone help?

I am getting:

  failed to access the garbage collection table. Event ID 5571

I have referenced Sql FILESTREAM garbage collection issue, so I am stuck up with it and working on various possibilities to clear the issue.


texts:
I'm new to SQL (also my first question) and have been trying to determine the best practises and most efficient way to code my problem. I'm using mySQLWorkbench on MacOS. I'm trying to get the result of an aggregate query across multiple tables. I need to display the first and last name, team name, earliest and latest instructor for each team. The following query gives me the first and last names of the correct instructors. The comments are what I tried to do, but the WHERE clause can only accept one operand.

SELECT i.firstname ,i.lastname -- , tt.name tm.dateJoined
FROM instructor i --, teachingTeam tt, teamMembership tm
WHERE id IN --and tt.name and tm.dateJoined IN
    (
        SELECT member --, team, dateJoined
        FROM teamMembership AS t
        WHERE datejoined = (
                SELECT min(dateJoined)
                FROM teammembership AS t2
                WHERE t2.team = t.team
                )
            OR dateJoined = (
                SELECT MAX(datejoined)
                FROM teammembership AS t2
                WHERE t2.team = t.team
                )
    );

I have the bulk of the query down I'm just having 2 issues;

Resolving the team ID to the team name (in an efficient way)
I'd like to output the dateJoined from the subquery rather than write an entire other query (seems redundant)

I believe that I can use WHERE EXISTS however I had trouble understanding it

I have a three main tables I need to work with.

One called teamMembership with four columns;

team member dateJoined and dateLeft

teamMembership.team is linked to teachingTeam.teamID,

teamMembership.member is linked to instructor.id

instructor with 5 columns;

id lastName firstName dateOfBirth and email

lastly, teachingTeam with three columns

teamID name and leader

teachingTeam.teamID is linked to teamMembership.team

teachingTeam.leader is linked to instructor.id


texts: Designing Database for Longitudinal Survey Data

texts:
I'm trying to design a relational database based on a bunch of CSV data that I already have.  It's mostly for the purposes of organization and querying subsets, and I won't be adding any new records via transactions.  My data describes around 60 attributes of businesses, and it is longitudinal as they are surveyed each year (25 years).

My problem is that some attributes will see many duplicates over the life of a business, and others will see very few.  Here's a dumbed down example:

id, year, company_name, type, num_employees, total_sales,
056, 2000, papa johns, fast food, 11, 45000
056, 2001, papa johns, fast food, 11, 45557
056, 2002, papa johns, fast food, 14, 50000
056, 2003, papa johns, pizza, 17, 55000
056, 2004, papa johns, pizza, 17, 55456
063, 1998, pops barbershop, barber shops, 3, 15000
063, 1999, fresh cutz, barber shops, 3, 15023
063, 2000, fresh cutz, barber shops, 3, 15000

I'm looking at around 60 million unique ids, most with a good number of years as well.  If the database was in a form similar to this it would be massive and full of duplicates.

Any suggestions on a manner of organizing it?


texts:
In the environment I am currently working, the connection string which is being used is wrong ( AG_LISTENER_Name\InstanceName). This is something we need to correct in all apps that is configured. Today we ran into an issue where the Listener_Name connects to Other instances from other boxes when the port number is not specified. The AG name resolves fine but then connects to some random instance when used without port. We want to use only the AG-Name to connect to particular instances. My questions :

Can we use 1433 as the listener port for all the instances in a box? ( read it can be done)
If yes, then will there be anything else that needs to be done? ( To avoid port conflict if any)
How to explain the scenario where the Listener (without port) connects to random instance.


texts: Is there a limit to the number of objects that can be dropped and recreated (frequently) in SQL Server?

texts:
The datatype for field [object_id] in table sys.objects is "int"
So for all the objects in a database this is unique and has a limit of 2,147,483,647 (2 billion'ish)
What happens when there is some process (connecting to this database) that drops and (re)creates a large number of (user defined) tables every hour every day. Does it get to a point, where there can be no more objects created in the database because it ran out of assigning unique object_ids? I read somewhere that sql server does not reuse an object_id that was assigned earlier, if thats the case, it can run out pretty quick, in this situation, right?
Please guide me to some links/articles if this was just a silly question.
Thanks


texts:
I need to replicate data from a ‘master’ SQL Server out to a number of replicas. Luckily the data only flows from the ‘master’ to the replicas.

I need to replicate both schema and data. Data size approx. 1 TB. I have full control of ‘master’ and replica servers, but they cannot communicate directly – only via a file share.

I’ve looked at Merge Replication and MS Sync Framework.

Merge Replication requires direct communication between the SQL Servers.
MS Sync Framework does not handle schema changes, but that can be solved with SQL Server Data Tool and DACPAC. Unfortunately I have to create a SQL Server to file share as partial participant and then another the other way from file share to SQL Server. Is there any way to get around this?

I would like to support SQL Server version agnostic replication e.g. ‘master’ running SQL Server 2008 R2 and replicas running SQL Server 2008 R2+ (E.g. SQL Server 2012).

Any other implementation suggestions?


texts: How to upgrade SQL Server 2008 to SQL Server 2014 servers with mirroring configured

texts:
We have two SQL Server 2008 R2 instances with mirroring configured. We want to upgrade them to SQL server 2014.

How will this upgrade effect the mirroring between the servers, and also how should I proceed with this upgrade without causing an outage?

As per my research I can upgrade the mirrored server first to 2014 and then do a failover on 2008 server to make it mirror and the 2014 as principal and follow the same procedure to upgrade 2008 as well. Could some one please shed more light to it. What are some intrinsic points that I need to take care of?


texts:
I'm presented with multiple .txt documents (each with its own set of columns) containing rows separated by some type of delimiter (which is different across the files). Each .txt file has different amount of columns/names and they are huge in size, some are split into 100 MB chunks. The data may contain malformed rows.

My job is to import them to an appropriate DBMS that will fulfil these requirements:

Importing speed should not be too bad. Few hours is acceptable for 700m rows
Importing should support ignoring malformed rows
Querying the data should support pattern matching such as %m% in SQL
Read operations are more important than write. It's required that once everything is imported, the read should be within milliseconds
There will be many tables. It's required to be easy to query all tables

I have already tried to use relational DBMS (MySQL, PostgreSQL), but they don't support skipping malformed rows (which is a huge issue). I've tried mongoDB but some data contains double quotes and I'm unsure if the import speed is good enough.

I recently even tried using Cassandra, but it's pretty limited on pattern matching and multiple table querying.

This task is not easy, and I've spent a lot of time reading and trying to find a solution by my own. I'd very much appreciate if someone could help me out to solve this.

The data is already extracted and given, cannot modify that process. Transform is not possible as the data size is beyond the limit of manual editing. Loading is the step I'm struggling with. I'm trying to find a DBMS that can do such thing.

I've thought a lot about NoSQL DBMS but they do not fulfil all requirements. I think SQL such as PostgreSQL would do it, but I have trouble inserting the data as it does not support skipping malformed rows.

Now that I think about it, it's more structured data than unstructured. So it would fit perfectly for SQL DBMS, but the only issue is that the data contains some malformed rows and I need quite good import speed.

Two Rows from file of format (username:email):

Valid row: admin:email@hotmail.com
Malformed row (contains extra column): admin:my:email@hotmail.com


texts:
I try use postgres_exporter.

It monitors postgres and sends metrics to prometheus.

It has a request
https://github.com/wrouesnel/postgres_exporter/blob/master/queries.yaml#L137

The output will be a queryid table and its metrics.

How knowing queryid get query string in Postgresql?

Thanks!


texts: Getting generic Oracle error message 'missing right parenthesis'

texts:
CREATE TABLE IDN_ORG (
  ID VARCHAR2(255) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  NAME VARCHAR2(255) NOT NULL,
  CREATED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  LAST_MODIFIED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  HAS_ATTRIBUTE CHAR(1) NOT NULL DEFAULT '1',
  STATUS CHAR(1) NOT NULL DEFAULT '1',
  PARENT_ID VARCHAR2(255),
  PRIMARY KEY (ID),
  CONSTRAINT TENANT_ORG_CONSTRAINT UNIQUE (NAME, TENANT_ID)
);

ORA-00907: missing right parenthesis

I'm trying to execute the above script and getting the error as mentioned. While reading, got to know that this is a more generic error that Oracle throws. I tried changing a few with no luck. Can someone please have a look for me? (Extremely new to Oracle)
Thanks in advance.