texts: I have a test database and I was able to see the database in SSMS and all of a sudden Its not listng my database and When I checked the mdf and ldf files they seems to be locked . I am using Sql server 2008 R2. Can anyone help me?


texts:
Here is my query:

select     c._id, c.cat_name
from       category c
inner join deptcat dc
on         c._id != dc.cat_id
inner join department d
on         dc.dept_id != d._id
where      d._id = 1

I have 3 tables department, category and deptcat:

CREATE TABLE department
  (_id, dept_name)
AS
  VALUES
    ( 1, 'CSE' ),
    ( 2, 'E&E' ) ;

CREATE TABLE category
  (_id, cat_name)
AS
  VALUES
    ( 1, 'a' ),
    ( 2, 'b' ),
    ( 3, 'c' ),
    ( 4, 'd' ) ;

CREATE TABLE deptcat
  (_id, dept_id, cat_id)
AS
  VALUES
    ( 1, 1, 1 ),
    ( 2, 1, 2 ) ;

My output should be like this:

_id | cat_name
----|-----------
3   | c
4   | d

I need to fetch the cat_id's which is not present in the deptcat table and also associated with dept_id  1, so can some one help me here?


texts: Database Maintenance Planner greyed out (disabled)

texts:
I have to look into some SQL Server 2000 maintenance plans, but Database Maintenance Planner option is greyed out in Tools menu of Enterprise Manager. SQL Server Agent seems enabled and running, because its icon has the green badge when I access the instance via a SQL Server 2005 SSMS.

Any hints?

Thank you


texts: How to join values from multiple rows in a single column?

texts:
I'm working on a project to find the association rule of items for Market dataset. I have a huge database that contains multi-tables. The output I got from this query

select sales_fact_1997.customer_id, sales_fact_1997.product_id,
product.product_name
from sales_fact_1997 , product
where sales_fact_1997.product_id = product.product_id
group by sales_fact_1997.customer_id , sales_fact_1997.product_id
order by sales_fact_1997.customer_id, product.product_name;

is like that:

customer_id     product_id      product_name
13               1466           Hot Dogs
13               937            American Cole Slaw
13               10             Akron City Map
...

Is there a way to get the output in this format:

customer_id     product_id      product_name
13              1466,937,10     Hot Dogs, American Cole Slaw, Akron City Map


texts: How to setup local database development process for small web team?

texts:
Background

I am working on creating a new development process for a small web team of about 4 programmers and 4 designers, with the obvious potential to grow the team in the future.
Our product is a central application that powers client websites that we also design and host.

Previously, we all worked via FTP on a dev server, with a single dev database. It "worked"* for a while, but we are moving into a new direction, so it is time to mature our process.

We use Percona Server 5.5, but this should be database agnostic, with the idea of keeping costs low.

Goals:

I am looking into creating a continuous integration (CI) process for database development with the following in mind:

  Developers have local copy of data to run development code against
  Able to rollback database structure to a previous changeset
  Able to separate new feature schema changes vs schema design fix changes
  Able to modify database structure locally for testing

Initial Concept

I have sketched out a process below using SVN and LiquiBase, though it completely removes #4.

  create a 'development' branch from trunk
  central 'development' db server runs off the 'development' branch
  local developers are set up as slaves to the development branch (provides #1 above)
  liquibase changesets are committed regularly to the development branch, which execute a post-commit hook to update the central development database (which will trickle down to the local machines running as slaves to this development server) (liquibase provides #2 above)
  when features or schema fixes are ready to go to QA, DBA (me) will merge the appropriate changes from development branch into trunk. This act will create a sql script to apply to a staging database server.
  Staging server should reflect TRUNK, which should have the same structure as Production, plus changes that are in QA
  after executing the sql script on the staging server, do some QA on the changes.
  If all looks good, TAG the structure. This will generate the .sql script to be run in production manually by the DBA (for off-peak hours if required)

This process requires that all developers run off the same 'development' branch, meaning there is only one version of the database schema at any given time (not sure that I want this).

It also means that any changes to the schema cannot be tested out locally and could affect other developers if not done right. In our environment, developers might add new tables but rarely modify existing structure. As the DBA, design fixes are done by me. But the inability to test fixes locally is my biggest hangup of the process.

How can the above process be tweaked to allow local development, while still maintaining a relatively up-to-date copy of data (as provided by replication in my proposed process)? I do not require the data to be current up to even the last week.

* By 'worked', I mean it sufficed but was a PITA.


texts: postgres database with multiple database object owners and pg_dump

texts:
with a postgres 11.2 database with multiple database object owners and trying to use the following command as the database owner (not the postgres superuser):

pg_dump  -h hostname -p 5432 -U dbownername dbname --file= dbname_20200406.pg_dump --no-owner --no-privileges --format=p

it fails due to object ownership not being all the same. (I have tested that this is the case by changing all ownership to dbownername and at that point pg_dump succeeds)

is the only way to get this to work is to run it as the postgres superuser?  or are there postgres roles that I can give dbownername to allow them to back up... with out in effect making them another postgres superuser?


texts: Use sp_DatabaseRestore for restore when database does not exist

texts:
I am trying to use sp_DatabaseRestore from the SQL Server First Responder Kit to restore some databases. It works fine if I first restore the database in a normal way using SSMS, but if the database does not previously exist, it says that it could not find the file specified (for both data an log-files).

It also ends with the following message:

  The database "DatabaseName" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.

But the first path is for the full backup, so I am not sure what this means.

Is there some particular use of the parameters that would help me use this even when the database does not exist, or am I forced to create them first?

Any suggestions appreciated.

Edit:
Code that is used (paths and names have been changed). The code fails if the database does not exist. If I create the database, then it works:

DECLARE @VersionDate datetime;
EXEC dbo.sp_DatabaseRestore @Database = N'DatabaseName',
                            @BackupPathFull = N'\\SERVERNAME\Backup\servername-wfcs$ServerNameAG01\DatabaseName\FULL\',
                            @BackupPathLog = N'\\SERVERNAME\Backup\servername-wfcs$ServerNameAG01\DatabaseName\LOG\',
                            @MoveFiles = 0,
                            @ContinueLogs = 0,
                            @RunRecovery = 1,
                            @ForceSimpleRecovery = 0,
                            @Debug = 0,
                            @VersionDate = @VersionDate OUTPUT

Edit 2: Turns out there was a difference in the data-file folder names between the old and the new server. The old server had a version-number lower than the actual version of the SQL Server, probably due to an earlier in place upgrade. When trying to restore from a backup with correct data folder, there is no problem.


texts:
I am trying to query the number of transactions that occur on a given day with the following query
select COUNT(seq_no) from "table"
where start_time >= '2020-04-01 00:00:00'
and start_time < '2020-04-01 23:59:59'

So I would like a count of the total sequence numbers for April 1st to display. But the results return

What am I doing wrong with this query? SQL Server2000, and I am very new and green to SQL. Thanks for any input.


texts: How do you index a text column in MySQL?

texts:
If I create a table with a text column

CREATE TABLE foo ( bar text );

I can't index the bar column.

MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar );
ERROR 1170 (42000): BLOB/TEXT column 'bar' used in key specification without a key length

If I HELP CREATE INDEX, I get,

Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string';

KEY_LENGTH not mentioned only KEY_BLOCK_SIZE, but if I use it as an option, I get

MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar ) KEY_LENGTH = 5;
ERROR 1911 (HY000): Unknown option 'KEY_LENGTH'


texts: Tempdb change settings and recommendation

texts:
In my production database I have below configuration in TempDb and needless to say I am facing a lot of IO issues on the drive .
So, as per recommendation I am planning to change it

Change #1 Adding 2 more .mdf files on different drive.

Change #2 Change initial size of tempdb .mdf size to 1024 and autogrowth to 10%.

Change #3 Change auto-growth of .ldf to 50%

I have three questions:

Do you have recommendation or correction needs to be made?
If I create new file and when I will change the intial size or autogrowth, do I need to restart SQL Server or can it impact SQL Server?
Do I have to create additional .ndf or log file also?

Reference of my other question

How to minimize log operations in SQL Server to avoid "log full" error

Dealing with non active transaction in log file and shrinking

IO request time and less write latency

As per paul recommendation, do I have to create 20 temdb files


texts: Can't add or update child, Foreign Key Error

texts:
I'm kinder new in MySQL.
I was trying to insert value in a relational database.
I know that i have to insert in parent table before doing it in child table.
Assume we have tables named STATE, COUNTRY.

START TRANSACTION;
INSERT INTO country (countryName, nationality,countryPrefix)
SELECT * FROM (SELECT 'Togo' AS countryName, 'Togolese' AS nationality, '228' AS countryPrefix) AS tmp
WHERE NOT EXISTS (
    SELECT countryName FROM country WHERE countryName = 'Togo'
) LIMIT 1;
SELECT country.idcountry FROM country WHERE country.countryName = 'Togo';
INSERT INTO state (stateName, country_idcountry)
SELECT * FROM (SELECT 'Region Maritime' AS stateName, '12' AS country_idcountry) AS tmp
WHERE NOT EXISTS (
    SELECT stateName FROM state WHERE stateName = 'Region Maritime'
) LIMIT 1;
COMMIT;

Below image is the screenshot of the error i encountered.


texts: Why are there no indexes contained in the information_schema database?

texts:
For the information schema.processlist:

Why are there no indexes on any of the fields (for example, a pk on ID)? Is this something that is not written through 'via sql' and is only reading directly from the storage layer to show the stats, or why isn't this table (and all other information_schema tables) constructed like a normal relational table?
And from the top of the mysql docs about them:

The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name


texts: Cannot add new Server Role SQL Server 2014

texts:
It seems that I have the same problem as Yasser in this post

I cannot add a new Server Role "SQL Server 2008 R2"

but I am running SQL Server 2014.

Basically when I right click on Server Roles I only get a menu with three options, "Start Power Shell, Reports, Refresh".

The command CREATE SERVER ROLE works. I am logged in as "sa". Any advice would be really helpful.

Thank you.


texts: How to find a query that is use deprecated option?

texts:
Mongodb writes logs

  2018-04-05T11:38:28.178+0000 W COMMAND  [conn107] Use of the aggregate
  command without the 'cursor' option is deprecated. See
  http://dochub.mongodb.org/core/aggregate-without-cursor-deprecation.

How to find related query?


texts: How can I get all orphan records in a single query?

texts:
I just noticed few tables that has orphan records (foreign keys) that  no longer point to any primary key. How can I query the tables to get a list of all such rows? When these parent records got deleted or how to handle this?

I'm using SQL Server 2008 R2 and we are trying to import 3 similar databases into one after cleaning it up, please advise.

Scripted a sample child table (DocumentDistribution) and parent table (DocumentSource):

ALTER TABLE [dbo].[DocumentDistribution] WITH NOCHECK ADD CONSTRAINT [fk_documentdistsourceid_documentsourceid] FOREIGN KEY([DocumentDistSourceID]) REFERENCES [dbo].[DocumentSource] ([DocumentSourceID])
GO
 ALTER TABLE [dbo].[DocumentDistribution] CHECK CONSTRAINT [fk_documentdistsourceid_documentsourceid]

Doesnt that tell me that the tables are in relationships and explicitely defined the foreign key constraints?


texts: Creating schema for a shop that sells items from multiple tables

texts:
I am trying to create a table in mySQl that contains a list of all the items that a shops. For example:

shops (shop_ID (PK), shop_name)

shops_and_items (shop_ID (PK), item_ID (PK), price)

The issue I'm facing is that a shop can sell two types of products. For example, furniture and clothing. In my database, I already have separate tables for both furniture items and clothing items. The suggested shops_and_items schema therefore does not work.

I'm wondering, what is the best way to go about it. Should I create two tables shop_and_furniture_items and shop_and_clothing_items ?


texts: Calculate Return Value - 1,3,6 Month Returns

texts:
How do I calculate the return for 1mth, 3mths, and 6mths?

I have this query but ValuationDate will not be exactly X months ago.

e.g. 2015-01-30 returns 2014-10-30 in the calculation. However, only 2014-10-31 exists in the table. So how do I return the latest date in the table for that month?

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3,ValuationDate)

ValuationDate is DATETIME. Using SQL Server 2008R2

So in the below table data, I want to return the PriceValue for 2014-10-31 on the row with ValuationDate 2015-01-30

Table Data:


texts: Are GUIDs damaged when inserted into Varchar fields?

texts:
In my staging database, I did the "smart thing", and used NewID() as a default value for Document_ID.  Historically, that GUID has been generated by a fragile .NET script, so I am redesigning the process using database operations.  It worked well, up to the end.

The data from staging are pasted to production after testing, through SSMS (We have a lot of NOT NULLS in the schema, and it's an easy way to get around them).

On the production system, the Document_ID is a Varchar(100).  After the paste, the GUID no longer works in queries, but only as a string:

 -- this format works
select * from Doc_Main where Document_ID like '%B20DC300-DC3C-40D9-AC83-4756C96F83DC%'
 -- Same format as below, doesn't work
select * from Doc_Main where Document_Id ='{B20DC300-DC3C-40D9-AC83-4756C96F83DC}'

Operationally, this is breaking the relationship between Doc_Main and the detail tables.

I am thinking that I will try to generate proper Insert statements based on the Staging data for Doc_Main, and replace the pasted records before doing anything more serious.

Ideas?


texts: MySQL on Windows Refuses to Start as a Service

texts:
I am trying to install MySQL 5.7.33 onto a windows 2012 server. A process I have done many many times.
I have copied the Zip file onto the server and expanded it into G:/mysql57/ (the same zip file I used to install on other servers previously without issues).
I place a my.ini file into this directory. Then I initialize the database:
G:/mysql57/bin/mysqld.exe --initialize --basedir=G:/mysql57/ --datadir=G:/mysql57/data/
So far so good. The error log is created along with temporary password. I then install the service:
G:/mysql57/bin/mysqld.exe --install MySQL57 --defaults-file="G:/mysql57/my.ini"
I then try to start the service, which results in:
Error 1053: The service did not respond in a timely fashion
It also annoyingly doesn't add anything to the error log.
If I try and run mysqld manually however it works fine, no errors
G:/mysql57/bin/mysqld.exe --defaults-file=G:/mysql57/my.ini
I can log in, I change my password etc.
Yet starting the service fails, every time.
I should point out that there is another MySQL database on there in D:/mysql (MySQL 5.5) which is using port 3306 and runs fine. This new one is using Port 3307.
Again a process I have done many times over.
I have checked the Windows Event Viewer, nothing.
I have tried the my.ini file from the old database.
I have tried a minimal my.ini file (port, basedir and datadir only).
Still nothing.
Is there anything else I have missed, as I've now lost most of a day on something that was supposed to be done by lunchtime.


texts:
I' trying to import a csv file in MySql WorkBench but get an error:

  Can't analyze file. Please, try to change encoding type. If that doesn't help, maybe the file is not: csv, or the file is empty.

I've tried to import the csv file create in Open Office Calac, Notepad++ or even just plain MS Notepad but no success. Of course I've set the encoding to UTF-8 in all of the programs.

Here is the the csv file content:

ę,1
ó,2
ą,3


texts: MSSQL - How to log every rpc_completed event into a table?

texts:
I want to log the Execution Time and Parameters for every stored procedure call on my database.
What I reached by searching is to use extended events as the latest method with minimum performance overhead. My aim is to achieve a similar result as the 'rpc_completed' event of 'extended events', but the problem with using extended events is that their sessions log output into .xe* files and there is no option to choose a database table as far as I read from tutorials/docs.
Can I (by any means), redirect extended events logs to a database table synchronously? I can sacrifice hardware resources for this, but can't pick methods like triggering interval-ed queries to read from *.xe files and insert them into a database table


texts: SQL Server Always On File Share Witness (Quorum vote) on different subnet to other nodes

texts:
I am currently having issues with some of my Availability groups where node 1 and node 2 loose connectivity between each other "A connection timeout has occurred on a previously established connection to availability replica".

The errors in the failover cluster manager say "File share witness resource 'File Share Witness' failed to arbitrate for the file share" The server the file share sits on hasn't restarted or had any issues and all permissions are working.

The only thing I can see is that the file share server is on a different subnet to the other 2 SQL Server nodes in the cluster.

Could someone confirm is having the file share server on a different subnet a big no no in an AlwaysOn environment? All the firewall rules are in place as it can talk to the other nodes but out of hours (usually) it loses connectivity.

The other weird thing is there are 3 votes in the quorum including the file share, so even if the file share loses connectivity to the failover cluster, node1 & node2 shouldn't lose connectivity between each other as there's enough votes for quorum (2)


texts: BI - Sales Fact Table Header and Detail Dimension

texts:
I have a FactSales table that is down to the line item.

I also have a sales order detail dimension that contains attributes that don't belong in the fact table.

Now, I'm in need of incorporating sales header information into the data mart.

Options:

Create a sales header dimension and place the key in the fact table.

Create a sales header dimension and place the key in the sales order detail dimension.

Incorporate the header info that I need (not a lot) into the sales order detail dimension. (This seems unscalable and a bad design but it's an option so I'm mentioning it)

It seems like option 1 is the best but I would appreciate if someone could shed some light on the pros and cons.

Thank you


texts: INSERT using results of CTE INSERT to provide unique id values

texts:
I am writing a job to transform data from an old design into a new design. In this process, I need to take the id from an insert into a separate table and use that in an insert to the target table, as such:

CREATE TABLE t1 {
  t1_id BIGSERIAL,
  col1 VARCHAR
};
CREATE TABLE t2 {
  t2_id BIGSERIAL,
  col2 VARCHAR, -- renamed from col1 to avoid confusion
  t1_id BIGINT REFERENCES t1.t1_id
};

I have the SQL defined that matches the following form:

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, (SELECT * FROM ins)
FROM t3 a;

I wanted this to run the SELECT * FROM ins for every row of the SELECT .. but instead it only runs it once and uses that value for all rows in the SELECT.  How can I restructure my SQL to get the desired behavior?

edit4

t1 ends up looking like:

1,<NULL>
(1 row)

t2 ends up looking like:

10,'a',1
11,'b',1 -- problem with id from t1 being 1
12,'c',1 -- problem with id from t1 being 1
.
.

What I want t1 to look like:

1,<NULL>
2,<NULL>
3,<NULL>
.
.

What I want t2 to look like:

10,'a',1
11,'b',2 -- id from t1 of 2
12,'c',3 -- id from t1 of 3
.
.

edit
To address what a_horse_with_no_name said, I also tried this (with the same result):

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, b.t1_id
FROM t3 a
JOIN ins b ON TRUE;

edit2
I just tried directly referencing the appropriate SEQUENCE in my query, and that DOES work - but I don't like that solution very much at all (mostly because I don't like hard-coding object names.)  If there is ANY solution other than directly referencing the name of the SEQUENCE I would appreciate it. :)

edit3
I suppose another solution would be to make use of a PROCEDURE to do the INSERT instead of a CTE .. but I'd still appreciation options/suggestions.


texts: Foreign key set to cascading but fails to update - why?

texts:
As mentioned elsewhere, I am rather new to SQL and databases. Getting my hands dirty at the moment and trying all the different possibilities.

I have a server running MySQL and set up the following table for testing:

Field        Type          Collation       Attributes   Null    Default   Extra
id           mediumint(8)                  UNSIGNED     No      None       AUTO_INCREMENT
domain       varchar(255)  utf8_unicode_ci              No      None
status       enum('...')   utf8_unicode_ci              No      None
replaced_by  mediumint(8)                  UNSIGNED     Yes     NULL

The idea was to have a list of domains that are valid for emails such as "gmail.com" and "hotmail.com".
Now if some domain is recognized as duplicate to another (such as "googlemail.com" to "gmail.com") the status column indicates this and the "replaced_by" column gives the ID to the main domain to be used.

Also, if a domain is recognized as invalid (such as "gymail.com" instead of "gmail.com") the database could store this information and, through status and replaced_by a script could suggest the correct domain.

While ID is the primary key of this table, I wanted to test a foreign key on the replaced_by column with ON DELETE RESTRICT and ON UPDATE CASCASE. so that the replaced_by column stays in-sync with the main ID.

But somehow when I try to update data, I get the following error. Why?

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`domains`, CONSTRAINT `domains_ibfk_1` FOREIGN KEY (`replaced_by`) REFERENCES `domains` (`id`) ON UPDATE CASCADE)

EDIT - some more details to help resolve this case:

CREATE TABLE `test`.`domains` (
`id` MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`domain` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`status` ENUM( 'valid', 'invalid', 'replace' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`replaced_by` MEDIUMINT( 8 ) UNSIGNED NULL ,
INDEX ( `replaced_by` ) ,
UNIQUE (
`domain`
)

ALTER TABLE `domains` ADD FOREIGN KEY ( `replaced_by` ) REFERENCES `test`.`domains` (
`id`
) ON DELETE RESTRICT ON UPDATE CASCADE ;

UPDATE `test`.`domains` SET `id` = '31' WHERE `domains`.`id` =1

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`domains`, CONSTRAINT `domains_ibfk_1` FOREIGN KEY (`replaced_by`) REFERENCES `domains` (`id`) ON UPDATE CASCADE)


texts:
I am trying to run below code in SQL server 2000 SP4 but getting error.

Declare @Body varchar(8000);
Declare @TableHead varchar(8000);
Declare @TableTail varchar(8000);

Set NoCount On;
Set @TableTail = '
';
Set @TableHead = '' +
            '' +
            '' +
            '
' +
            '
Server Name	' +
            'Product	' +
            'Provider	' +
            'Data Source	' +
            'Is Linked?
';

Select @Body = (Select
        name As [TD],
        product As [TD],
        provider As [TD],
        data_source As [TD align=center],
        is_linked As [TD align=center]
    From sys.servers
    Order By is_linked, name
    For XML Raw('tr'), Elements);

Set @Body = Replace(@Body, '_x003D_', '=');
Set @Body = Replace(@Body, '_x0020_', space(1));

Select @TableHead + @Body + @TableTail;

Error:

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'For'.

I took this code from here:

http://www.sqlsoldier.com/wp/sqlserver/buildinghtmlemailswithsqlserverandxml

and am using it as a reference to build a new report. I have a mix of SQL Server 2000, 2005, 2008, 2012 instances in my environment. This code works on other servers but not in 2000 SP4.

Please help me fix this.


texts:
I have three tables growth_spaces, equipment and growth_space_equipment which links the two tables together. I'm trying to write a query to get all growth spaces that have all of the equipment that I give as an array. For example if the array were ('Mist Propagation, Automated Curtain) I only want growth spaces that have both of these equipment (not ones that have either).

How would I go about writing this in PostgreSQL?

http://sqlfiddle.com/#!15/97b86/2


texts:
MySQL 5.

I have the following table (filled with many rows):

CREATE TABLE `SectorGraphs2` (
  `Kind` tinyint(3) UNSIGNED NOT NULL COMMENT '1 - продюсер, 2 - жанр, 3 - регион',
  `Criterion` tinyint(3) UNSIGNED NOT NULL,
  `Period` tinyint(3) UNSIGNED NOT NULL,
  `PeriodStart` date NOT NULL,
  `SectorID` int(10) UNSIGNED NOT NULL,
  `Value` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `SectorGraphs2`
  ADD UNIQUE KEY `Producer2` (`Kind`,`Criterion`,`Period`,`PeriodStart`,`SectorID`) USING BTREE,
  ADD KEY `SectorID` (`SectorID`);

then I run:

EXPLAIN
    SELECT SectorID, SUM(Value)
    FROM SectorGraphs2
    WHERE Kind = 1 AND Criterion = 7
      AND Period = 1
      AND PeriodStart >= ? AND PeriodStart < ? + INTERVAL 1 WEEK
    GROUP BY SectorID

and it produces:

+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | SectorGraphs2 | range | Producer2,SectorID | Producer2 | 6 | NULL | 1 | Using index condition; Using temporary; Using filesort |
+---+---+---+---+---+---+---+---+---+---+

I wonder why it uses a temporary while all keys Kind, Criterion, Period and PeriodStart are in the key Producer2 and it can be calculate by just enumerating the last field PeriodStart of the key. (Accordingly to what I learned, MySQL can enumerate the last field of a key without consulting data, if all previous fields have a fixed value.) Please help if possible to rewrite this without using a temporary.

Note that the above code is intended to store and calculate some statistics.

I need to ensure we do not run heavy queries which may overload our MySQL server. Please help to make proper use of indexes and avoid big temporary tables.


texts:
I'm often given a CSV of data to extract from the database. Sometimes, the user wants something like a count of all rows that match, with NULLs for missing data.

Example: An orders table containing product_number with a foreign key to the products table, then a CSV of certain product_numbers to find total orders (0/null if no orders).

I want to write a query like this:

select count(orders.id) as orders,orders.product_number
from orders
     cross join ( inserted list of product_numbers ) as products

But I can't find a function that turns 1, 2, 3, ... 10 into select 1 union select 2 ... union select 10, so the query is usually:

select orders.product_number,count(orders.id) as orders
from products
    left join orders on products.product_number=orders.product_number
where products.product_number in([huge csv])
group by products.product_number

Is there a way to use an injected CSV in something other than an IN() clause in a lookup table? There's got to be a better way!


texts: How would I maintain referential integrity across a database where I use versioned records while keeping data in sync?

texts:
I am building a database that consists of a few tables that are used as data sources for a form our application generates.

[form]     [question]       [responses]
id         id               id
name       form_id          question_id
desc       question         response
created    created          created
modified   modified         modified

This is a loose example, but a web form is generated using [form] and then questions generated using [question] table. The responses are captured in [responses] table. The problem I see with this, is if a response is captured, and then the question is modified, the historical data will be out of sync (meaning that the response would be directed at the original pre-modified question). This would be an auditors nightmare.

What is the best way to approach this? I feel I need to keep some sort of version information or shadow records of some kind, but I am not sure the best way to go about it.


texts: Design for storing data only applicable to a small subset of records

texts:
So my boss and I are in disagreement how to best design our database for storing miscellaneous data for certain clients.

Basically, we have a bunch of tables in some format similar to this (in pseudo-code for simplicity):

TABLE [dbo].[SalesData]
[CampaignID] int IDENTITY(1, 1) NOT NULL, (This is the PK)
[CustomerID] int NULL,
[Calls] float NULL,
[Responses] float NULL,
[Sales] float NULL,
[Revenue] money NULL,
[Cost] money NULL,
[WebSales] float NULL,
[Channel] nvarchar(20) NULL

What happens is as we gain clients, we are sometimes forced to add in some kind of custom data collecting for each one. For this table, everything is used by every client (we have about 20) except for WebSales and Channel, which is only used for 1 client.  Another client wants a couple new pieces of data tracked, and therefore, we either need to add more columns, or spin off that data into a related table and start a supertype-subtype system.

What I want to do is also spin off WebSales and Channel, and add a table for each customer in this manner:

TABLE [dbo].[SalesData_Client1]
[CampaignID] int NOT NULL, (PK and FK to the SalesData table)
[WebSales] float NOT NULL DEFAULT 0,
[Channel] nvarchar(20) NOT NULL DEFAULT ''
ADD CONSTRAINT [CK__SalesData_Client1__CustomersCampaignID]
CHECK (dbo.CustomerOfCampaignID(CampaignID) = Client1)

Then I would drop those columns from the original table and make the second client a table like this as well. The boss thinks this means we'll have to add too many tables as we gain clients, and wants to add more columns. He would rather change the SalesData table like this:

TABLE [dbo].[SalesData]
[CampaignID] int IDENTITY(1, 1) NOT NULL, (This is the PK)
[CustomerID] int NULL,
[Calls] float NULL,
[Responses] float NULL,
[Sales] float NULL,
[Revenue] money NULL,
[Cost] money NULL,
[Unknown] char(10) NULL,
[Misc1] float NULL,
[Misc2] nvarchar(20) NULL
[Misc3] int NULL,
[Misc4] int NULL
[Misc5] int NULL

Where the "Misc" columns can be used for whatever, and then the programmers can "just figure it out based on the client". I know the reasons as to why you shouldn't do it this way, it violates normalization and will make performance worse and worse as we add columns, etc. But he keeps coming back with "It's way simpler" and doesn't like that every new client could mean another new table. He also makes the point that newer columns like Websales might be used by more than 1 client eventually, and it will be a huge pain to have it in multiple subtype tables, or to move it from multiple subtype tables to the main table if we should ever want to do that.

Is there a better method than supertype/subtype solution that I came up with that can make things easier for him and the programmers? An important note is that each client's data is basically in it's own universe and will NEVER mix. I realize it's kind of subjective and the answer will depend on how many "Misc" columns we are seriously expecting. The reason I want to go the subtype route is because the next client could require 5 new bigint columns for all I know.


texts: Apache Cassandra INSERT-only permissions

texts:
Looking at the Cassandra documentation, it appears that the smallest write-only permission on a table you can grant is MODIFY, which includes UPDATE, DELETE and TRUNCATE.

Is there a proper way to restrict this further to ONLY grant INSERT and no other permission, ideally for the user/role itself, without requiring further logic application side?

I would like for ONLY the DBA to have the ability to alter data in a table, the application user should have INSERT-only rights.

If it's not possible using the permission model only, could triggers be the answer and are there known drawbacks for this approach?


texts: Unable to get Cassandra in a Docker to talk to cluster

texts:
Trying to get Cassandra running in a docker using the official docker image. When I execute the below lines of code it starts then stops the docker without connecting to the cluster. I have checked the cassandra.yaml file and it looks just like the other nodes on my cluster. Same for the cassandra-rackdc.properties file. The docker can ping all the other computers.

I run the following and a few derivations:

docker run --name cassandra -i \
-v /media/mcamp/HDD/Docker/CampgroundContainer1:/var/lib/cassandra \
-e CASSANDRA_SEEDS="192.168.0.114, 192.168.0.101, 192.168.0.106" \
-e CASSANDRA_CLUSTER_NAME=CampCluster \
-e CASSANDRA_DC=campground-wireless \
-e CASSANDRA_RACK=Docker1 \
-e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch \
-e CASSANDRA_START_RPC=true \
-e CASSANDRA_LISTEN_ADDRESS=172.17.0.2 \
-p 7000:7000 \
 cassandra:latest

I get the following error:

INFO  [ScheduledTasks:1] 2017-11-10 03:45:39,428 TokenMetadata.java:498 - Updating topology for all endpoints that have changed
Exception (java.lang.RuntimeException) encountered during startup: Unable to gossip with any seeds
java.lang.RuntimeException: Unable to gossip with any seeds
    at org.apache.cassandra.gms.Gossiper.doShadowRound(Gossiper.java:1415)
    at org.apache.cassandra.service.StorageService.checkForEndpointCollision(StorageService.java:550)
    at org.apache.cassandra.service.StorageService.prepareToJoin(StorageService.java:801)
    at org.apache.cassandra.service.StorageService.initServer(StorageService.java:666)
    at org.apache.cassandra.service.StorageService.initServer(StorageService.java:612)
    at org.apache.cassandra.service.CassandraDaemon.setup(CassandraDaemon.java:393)
    at org.apache.cassandra.service.CassandraDaemon.activate(CassandraDaemon.java:600)
    at org.apache.cassandra.service.CassandraDaemon.main(CassandraDaemon.java:689)
ERROR [main] 2017-11-10 03:46:08,466 CassandraDaemon.java:706 - Exception encountered during startup
java.lang.RuntimeException: Unable to gossip with any seeds
    at org.apache.cassandra.gms.Gossiper.doShadowRound(Gossiper.java:1415) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.StorageService.checkForEndpointCollision(StorageService.java:550) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.StorageService.prepareToJoin(StorageService.java:801) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.StorageService.initServer(StorageService.java:666) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.StorageService.initServer(StorageService.java:612) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.CassandraDaemon.setup(CassandraDaemon.java:393) [apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.CassandraDaemon.activate(CassandraDaemon.java:600) [apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.CassandraDaemon.main(CassandraDaemon.java:689) [apache-cassandra-3.11.1.jar:3.11.1]
INFO  [StorageServiceShutdownHook] 2017-11-10 03:46:08,469 HintsService.java:220 - Paused hints dispatch
WARN  [StorageServiceShutdownHook] 2017-11-10 03:46:08,469 Gossiper.java:1540 - No local state, state is in silent shutdown, or node hasn't joined, not announcing shutdown
INFO  [StorageServiceShutdownHook] 2017-11-10 03:46:08,469 MessagingService.java:984 - Waiting for messaging service to quiesce
INFO  [ACCEPT-/172.17.0.2] 2017-11-10 03:46:08,470 MessagingService.java:1338 - MessagingService has terminated the accept() thread
INFO  [StorageServiceShutdownHook] 2017-11-10 03:46:09,653 HintsService.java:220 - Paused hints dispatch


texts: Select range of non-identical date from different table

texts:
On MySQL5 with innoDB, I have sensor data in multiple tables, all with an ID and a DATETIME field, like so (two tables shown, but there are more). The IDs and DATETIMEs are not identical between tables, as each sensor uploads at a different rate.

temp_data:

INT      id
DATETIME ts
FLOAT    temp
FLOAT    humidity
FLOAT    lux

power_data:

INT      id
DATETIME ts
FLOAT    kW
FLOT     volts

My question is:
I need to pull all rows within a certain date range from BOTH table- in this case, the last 12 hours, so, the most recent N rows, but in the future, any date range in the past.  These will be sorted by the DATETIME value.

Using the (very good!) answer below, I get:

sourceTable    id  ts Ascending        temp    humd    lux
power   19879   2015-05-16 18:13:08 0.5 121.9   NULL
temp    19880   2015-05-16 18:13:10 76  44  1099
power   19880   2015-05-16 18:13:12 0.51    122 NULL
temp    19881   2015-05-16 18:13:13 76  44  1102

...and so on.  I need to preserve the column headers to parse this in JSON using PHP.  Is there a way to do that?  For instance, the volts/kW column headers are gone in this example.  I could write some PHP which looks for a NULL in the third position or check the sourceTable field, so I'm considering this answer correct.  However, I am interested to know if there's a way to keep the headers.


texts: Having trouble starting my Oracle database

texts:
I'm a programming student and I just started learning databases in college. However, everytime I try connecting to my databse in Oracle SQL Developer I have this message:

  ORA-12505, TNS:listener does not currently know of SID given in
  connect descriptor.

After posting here, we came to the conclusion that the databse wasn't properly started. When I try to start it from the cmd I get those errors:

When I type "startup" in the SQL command prompt

  ORA-01078: failure in processing system parameters ORA-00600: internal
  error code, arguments: [kspifisp:ksfdread_resilver], [0x000A9F388],
  [0x000A9F000], [512], [], [], [], [], [], [], [], []

When I type "select instance_name, status from v$instance;" in the SQL command prompt

  ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session
  ID: 0 Serial number: 0

I've seen that I could go into the net manager in "Programs > Oracle - HOME_NAME" but I don't have this in my programs list. Of course I tried uninstalling and reinstalling the database several times.

I've done the same installation on my PC at home and everything works fine I just need to make it work on my school laptop. I'm currently on Windows 10 installed on a Macbook. The database I installed is Oracle Database Express Edition (XE) Release 11.2.0.2.0 (11gR2).

Here is the 'init.XE.ora' file

SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora'


texts: How to return rows with a specific value last?

texts:
My question is reverse of this question, is it possible to define the sort order.

I want to return some records from SQL Server database, sorted in ascending order based on a column's value BUT two particular records should always be on the end.

The original SQL (which needs to be changed) is below:

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000001' and code_id < '000100'
order by CategoryDescription

The results are returned as

000003   ***First Record
000002   ***Another Record
000004   Apple
000016   Books
000014   Cables

I want the below result (first two with asteriks on the last):

000004   Apple
000016   Books
000014   Cables
000003   ***First Record
000002   ***Another Record

I tried the below UNION statement but the resultset is automatically sorted in ascending order and those two rows are in the beginning by default.

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000003' and code_id < '000100'
--order by categoryDescription

UNION

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000001' and code_id < '000004'


texts: Security Considerations for AG vs Log Shipping

texts:
we are considering removing our log shipping server and adding another node to our Availability Groups. While I understand that this should be better cause it's a/synchronous and will move the data to the node faster and can also be used for reporting, I don't know from a security point of view what might be the implications and concerns for the organisation.

Example: in case the primary node becomes infected from some malicious software and effects the logs, what will be the implications on all the other passive nodes.

We still plan to continue taking backups from the primary node frequently, but maybe someone has some experience on the topic.


texts: Why is the following query so bad performance-wise

texts:
After asking this question, I got to thinking about why a certain query was doing all the problems.

In short, there was a query which took 500ms, I ran some execution plans and applied the recommended indexes but it only cut off about 50-60ms.

After running it again I found out that the following query(which occur in multiple locations) was really slow:

SELECT @TempCardNumber = CardNumber
FROM Cards
WHERE (CardNumber=@CardNumber or FullCardNumber=@CardNumber)

When I changed it to be

SELECT @TempCardNumber = CardNumber
FROM Cards
WHERE (CardNumber=@CardNumber)

It ran super fast, and even the removal of indexes didn't matter too much, leading me to believe this was the main bottleneck, but I don't understand.. whats so wrong about it?


texts: Rank students based on their average in MySQL

texts:
I have been researching and trying various queries to get this to work.
What I want to happen is to rank each student based on their average which will be calculated by using AVG(grade) and this will be grouped by student_id. The table with the necessary info is as follows:

assessment_id | student_id | grade | subject_id

What I would like to see after the query is:

student_id | AVG(grade) | rank

I would also like to get the rank of a student using PHP, for example (mysql_query) WHERE student_id = '1001'

The closest I have come to getting this was by using the query below:

SELECT student_id, AVG(grade), (@rn := @rn + 1) AS rank
FROM grades CROSS JOIN (SELECT @rn := 0) CONST
GROUP BY student_id ORDER BY AVG(grade) DESC

The problem with the query above is that it ranks the students based on their student_id number.

I have searched for solutions all over but they just seem to not solve what I am after. I would really appreciate your help towards this.


texts: Postgres 9.1 query plan not using check_constraints on partitions after large data load

texts:
I've created a table in Postgres 9.1 called 'markets' that contains data about a market in a specific US zip code. I've partitioned the data on the zip code column, using the first digit of the zip code. So I have a 'markets' table and 10 inherited tables such as markets_0, markets_1, etc.

My constraint for each of the inherited table looks like such:

CONSTRAINT markets_0_zip_check CHECK ("left"(zip::text, 1) = '0'::text)
CONSTRAINT markets_1_zip_check CHECK ("left"(zip::text, 1) = '1'::text)

and so forth, for child tables markets_0 through markets_9. I also have an insert trigger that handles storing the data in the proper table.

I loaded a few millions rows and ran EXPLAIN on the queries and everything looked fine when I queried on zip code. The query planner went straight to the appropriate table. However, after loading about 12 million rows of data, the query planner is now looking at all 10 tables, even though I'm querying on the zip code.

For example, a query such as this:

EXPLAIN select * from markets where zip='75254'

produces this:

    ->  Seq Scan on markets  (cost=0.00..0.00 rows=1 width=116) (actual time=0.002..0.002 rows=0 loops=1)"
          Filter: ((zip)::text = '75254'::text)"
    ->  Index Scan using index_markets_0_on_zip on markets_0 markets  (cost=0.00..8.52 rows=1 width=118) (actual time=0.021..0.021 rows=0 loops=1)"
          Index Cond: ((zip)::text = '75254'::text)"
    ->  Index Scan using index_markets_1_on_zip on markets_1 markets  (cost=0.00..8.54 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)"
          Index Cond: ((zip)::text = '75254'::text)"
   ->  Index Scan using index_markets_2_zip on markets_2 markets  (cost=0.00..8.39 rows=1 width=118) (actual time=0.014..0.014 rows=0 loops=1)
          Index Cond: ((zip)::text = '75254'::text)

and so forth for each of the 9 tables.

So here is what I've done to trouble-shoot this:

I've verified that SET constraint_exclusion = partition; is enabled in my postgresql.conf file. I've tried setting this in the query itself with no difference in the results.
I've verified that there are no overlapping constraints or zip codes that don't start with 0-9.
I've verified that the data is being stored in each table properly.
I've run VACUUM ANALYZE on each of the tables; just in case.

I have other partitioned tables that are working fine on the same server, so I'm stumped as to why this is no longer working.

Any ideas?


texts:
I am experiencing issue.

Someone 'attacked' my server: simply by searching the same phrase with multiple requests.

As it is text search request and database indices are not used, the engine searches through every row. In general I will deal with myisam full-text indices, but I am curious, is there any way to block/disconnect/interrupt the query which is longer than x seconds?

P.S. I have wait_timeout set to 30, is there anything else I can set(I don't think interactive timeout will be useful).


texts: how to return all the columns not just the updated columns when update in postgresql?

texts:
let's say i have this update statement
UPDATE person set age = 20,location = 'us' WHERE id = 1234

based from documentation of postgresql on UPDATE , it can only return updated fields.
but I want to get all fields regardless if it's updated or not. e.g i have name, first name, gender and where not updated in my UPDATE query.
RETURNING *

doesn't work


texts: SQL Server DB Restore Clustered to Single Node?

texts:
Can you restore a DB from a SQL Server 2005 Clustered Environment (Prod) to a single server (single node) (SQL Server 2005) in Dev/Sys?

Cheers,
Conor


texts:
I am running a simple iterator over all the documents in a collection in a mongo replica set (primary, secondary, arbitrary). On a collection with 1.000.000 items it takes about 10 seconds. On a collection with 12.000.000 it takes more than one hour. We have seen cases when it takes 6 hours to iterate over such a volume.

Why is this? I read somewhere that more than 2.000.000 items in a collection is bad for performance. Is that correct?

This is on MongoDB 3.0 on a RedHat VMware machine.

The size of the collection is some 100GB while the database itself is 400GB. There are some 40 smaller collections in the same database. The average size of a document in this collection is 8KB. Memory usage seems to be around 6GB of 8GB available on the machine. I haven't tracked memory usage while actually running the query.

There may be some updates on the collection while it is running.


texts: Is there a table size above which SELECT performance degrades significantly?

texts:
In MySQL, does SELECT performance degrade significantly after a certain table size? I have a table with about 107 million rows and some queries seem stuck in "Sending data" for a long time. Of course, "sending data" is a catchall for all sorts of query processing according to the MySQL manual. This is puzzling because EXPLAIN says that the query is "using where; using index" and only returns about 1700 rows. It's the only query running, so I know it's not a locking issue.

Would it help to split this table into a few hundred smaller tables, if I'm splitting on something that's already part of the key? It seems like the key itself should be better than that solution, but I'm at a loss as to why this query should take so much time.

The table engine in question is TokuDB, but I see the same results with InnoDB. I haven't tried MyISAM or any other engine with this table.

The query is (apologies for obfuscation due to internal data):

SELECT id FROM my_table WHERE my_end_time>=1234567890 AND
    my_start_time<=2345678901 AND a='XXX' AND b='YYY'
    ORDER BY my_start_time ASC;

The key it's using is a UNIQUE index on

a, b, my_start_time, my_end_time


texts: why MySLQ datadir must be owned by mysql user?

texts:
I read this from MySQL document:

  Otherwise, most of the MySQL installation can be owned by root if you like. The exception is that the data directory must be owned by mysql.

Can somebody tell me why?
Why data directory owned by root is not good?


texts: Can I Run Two Full Backups on SQL Server 2005 at the Same Time?

texts:
Running SQL Server Express 2005 here.  We are creating several programs which run a backup on the database using Server Management Objects (SMO) mostly through C# (.NET).  We have separate processes that will invoke the backup at different times.

Are there any issues with running two full backups at the same time if they are dumping the backup to different locations?  The processes are pretty independent of each other and it would be a bit of work to synchronize them all.


texts:
I have a table of content. Users can vote on a content row. They can vote up to three times.  I'm looking for the most efficient way to make sure they don't go over three votes.

I can think of two main designs...

1) Each user can add one row per vote.

CREATE TABLE Vote
   int userId
   int contentId

2) Each user can add one vote row and then its quantity value is incremented until 3 has been reached.

CREATE TABLE Vote
   int userId
   int contentId
   int quantity

To do this properly (i.e. thread safe) I think the sequence for these cases would be as follows...

1)
a) Read the parent User row and hold an update lock on it.
b) Count the number of votes.
c) If less than three then insert a row.
d) Commit

2)
a) Read the parent User row and hold an update lock on it.
b) Do an exists on the vote row.
c) If not there then add it.
d) If there then check the quantity is below 3 and update if true.
e) Commit

Any ideas? Any way around holding an update lock on User? (At the very least this always seem to be awkward to do with an ORM ;))

If I forget the transaction then I think method 2 is the safest and the worst case scenario is that the odd vote gets lost or a user gets a few more than 3 votes.

Any ideas on how best to achieve this?


texts: Log Shipping - Determining Rows Modified After a Restore

texts:
The company I'm doing some work for is using log shipping to sync a production database with a secondary. Is there any way I can determine the rows that were inserted, updated or deleted after a restore is done on the secondary? Since the secondary is read-only, I cannot put triggers on any tables, enable CDC etc. on the secondary. So I'm looking for a way to know what changed after a restore.

SQL Server 2014 is being used.


texts: Amazon Aurora CREATE_TIME in INFORMATION_SCHEMA.TABLES is NULL

texts:
We are running an Amazon Aurora database that emulates MySQL 5.6.10. The CREATE_TIME in SHOW TABLE STATUS and INFORMATION_SCHEMA.TABLES is consistently NULL for all our tables.

Is this a bug, a limitation of Aurora's architecture, or am I doing something wrong? Is this information available anyplace else?

db2 information_schema> SELECT DISTINCT TABLE_SCHEMA
FROM information_schema.TABLES WHERE CREATE_TIME IS NOT NULL;
+--------------------+
| TABLE_SCHEMA       |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (9.00 sec)

Additional notes: We have several thousand tables across several hundred databases, on several different hosts; many of the tables were created with files-per-table on, and some with it off. We do not have access to the .frm files, and they may or may not exist in Aurora. This has always worked for us in MySQL.

Update: Amazon support told us, vaguely, this is a bug and "it will be corrected in a later release." Still interested in knowing if this is going to be retroactively available, or if there is another place to get this 'cause Amazon is not elaborating.


texts: Loading a table with input from two different input files

texts:
As part of a migration activity i need to load a table in mysql with data from two different input files. As in few fields in the table are loaded from one input file and rest from another. Also the input files are not order wise,what i mean is field 1 in input file 1 might not be the first field in the table. So data needs to be loaded into proper fields in the table from input files.
Hope my question is clear. If anyone can suggest any scripts or anything for this would be helpful
Thanks in advance.


texts: Recommendation for mySQL table structure (no code)

texts:
I need some advice on how to layout the tables for a MySQL database.

I will first describe what I am trying to do:

I have a table for users. (This covers basic info for now)
A user will select a certain workout plan (say plan 1-6). Different workout
The user will then check a number of checkboxes. Say there are 15 checkboxes for the different machines, a user can select however many he/she wants. Let's say the user choose 6 different checkboxes, not necessarily in order. Each checkbox would have a certain ID. Those checkboxes could post a value of 1 to the database if checked
On the next page a list of every checkbox the user clicked would be generated. Each checkbox has a picture and name associated with it. EX: For gym machines
Each machine the user chose is displayed along with a certain number of sets that is set by an admin

Each plan has certain workout machines. I was thinking of making a users table and machines table. Each has user_id. So my database would keep track of each machine the user chose.

I already have the layout pretty much done. I just need to know how to layout my tables in the database so I can start populating data dynamically.

Do I just make 1 table for users and ad like 40 different columns? Probably not.

So let's say a user has clicked 6 machines, each of those machines gets a value of 1. Then on the next page each machine with a value of 1 is displayed along with the picture and name associated with that machine. The number of sets will be set by the admin.

And ideas? I can clarify further if needed.

If you want a better idea of what I am doing, take a look at this picture:

This picture shows the page after the user has selected which machines he/she wants. You can see that 12 machines were chosen. So 12 different machines were chosen and they were all listed here.

The sets at the top were created by an admin and also reflect each machine shown at the bottom.

Everything is being populated from a database and I already have the layout done I just need a good recommendation for a table structure.


texts: MySQL error 1066 - Error Code: 1066. Not unique table/alias: 'TAXES'

texts:
I have two MySQL statemenents:

SELECT PRODUCTS.REFERENCE,PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL,
       SUM(TICKETLINES.UNITS) AS UNITS,
       SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) AS SUBTOTAL,
       SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) As TAXES,
       SUM(TICKETLINES.PRICE * TICKETLINES.UNITS)
        + SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) AS GROSSTOTAL
FROM TICKETLINES
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID = TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID = TICKETS.ID, TAXES
WHERE RECEIPTS.ID = TICKETS.ID AND TICKETS.ID = TICKETLINES.TICKET
AND TICKETLINES.PRODUCT = PRODUCTS.ID
AND TICKETLINES.TAXID = TAXES.ID
GROUP BY PRODUCTS.REFERENCE, PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL
ORDER BY GROSSTOTAL DESC
LIMIT 10

and

SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, PRODUCTS.ISCOM,
       PRODUCTS.ISSCALE, PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, PRODUCTS.TAXCAT,
       PRODUCTS.CATEGORY,PRODUCTS.ATTRIBUTESET_ID, PRODUCTS.IMAGE, PRODUCTS.ATTRIBUTES,
       PRODUCTS.ISKITCHEN, PRODUCTS.ISSERVICE, PRODUCTS.DISPLAY, PRODUCTS.ISVPRICE,
       PRODUCTS.ISVERPATRIB, PRODUCTS.TEXTTIP, PRODUCTS.WARRANTY, PRODUCTS.STOCKUNITS,
       TAXES.NAME, TAXES.RATE, PRODUCTS.STOCKVOLUME
FROM PRODUCTS
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN TAXES ON TAXCATEGORIES.ID=TAXES.ID
ORDER BY PRODUCTS.NAME

Now, I am trying to combine these two statements into one, here is what I've got:

SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, PRODUCTS.ISCOM,
       PRODUCTS.ISSCALE, PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, PRODUCTS.TAXCAT,
       PRODUCTS.CATEGORY, PRODUCTS.ATTRIBUTESET_ID, PRODUCTS.IMAGE, PRODUCTS.ATTRIBUTES,
       PRODUCTS.ISKITCHEN, PRODUCTS.ISSERVICE, PRODUCTS.DISPLAY, PRODUCTS.ISVPRICE,
       PRODUCTS.ISVERPATRIB, PRODUCTS.TEXTTIP, PRODUCTS.WARRANTY, PRODUCTS.STOCKUNITS,
       TAXES.NAME, TAXES.RATE, PRODUCTS.STOCKVOLUME,
       SUM(TICKETLINES.UNITS) AS UNITS,
       SUM(TICKETLINES.PRICE*TICKETLINES.UNITS) AS SUBTOTAL,
       SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE) AS TAXESTOTAL,
       SUM(TICKETLINES.PRICE*TICKETLINES.UNITS)
         +SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE) AS GROSSTOTAL
FROM TICKETLINES
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PPRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN TAXES ON TAXCATEGORIES.ID=TAXES.ID
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT=PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID=TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID, TAXES
WHERE RECEIPTS.ID=TICKETS.ID AND TICKETS.ID=TICKETLINES.TICKET
AND TICKETLINES.PRODUCT=PRODUCTS.ID
AND TICKETLINES.TAXID=TAXES.ID
ORDER BY PRODUCTS.NAME

Why upper statement does not execute and reports error Error Code: 1066. Not unique table/alias: 'TAXES'
?


texts: Is it possible to work around the need to have a sever level permission with ownership chaining or the sort?

texts:
I want to give everyone access to a very limited portion of the dynamic management views. That is, I want everyone in my database to be able to select from this view:
create view dbo.KnowYourself
as
select
  net_transport,
  protocol_type,
  client_net_address
from
  sys.dm_exec_connections   -- requires VIEW SERVER STATE permission
where
  session_id = @@SPID;

dm_exec_connections is a management view in the master database.
I make sure that dbo.KnowYourself belongs to sa and that sa maps to the same user in my database and in master. I make sure both master and my database have DB_CHAINING ON, and are trustworthy just in case, even though it should not matter.
Still, no one can select from the view because they lack the VIEW SERVER STATE permission.
I am aware of two workarounds:

Turn the view into a multi-step table-valued function and assign with execute as owner to it.
This does not work for me because certain queries significantly drop in performance when they have to call a multistep or scalar function instead of an inline function or a view (three seconds vs two hours).

Give all my users VIEW SERVER STATE permission, create them all in the master database and assign them DENY permission on all dynamic management views except the one I need.
That may work, but concerns me as a management burden going forward (I will have to do that for each new user, and I will have to keep denying new management views as they appear in next versions of SQL Server).

Is there a better way to give any user ability to select from this view?


texts:
I am having trouble setting up replication on a new mongoDB installation. Everything works fine when I run without replication but when I run mongod with the --replSet option, and run rs.initiate(), this command hangs. Note that I am only running a single instnace of mongod in this example. No other instances are yet running.

In the logfile, I see:

Wed Aug 15 10:02:04 [conn2] run command admin.$cmd { replSetInitiate: { _id: "regression_data", members: [ { _id: 0.0, host: "nradmin.icd.teradyne.com:27017" } ] } }
Wed Aug 15 10:02:04 [conn2] command: { replSetInitiate: { _id: "regression_data", members: [ { _id: 0.0, host: "nradmin.icd.teradyne.com:27017" } ] } }
Wed Aug 15 10:02:04 [conn2] replSet replSetInitiate admin command received from client
Wed Aug 15 10:02:04 [conn2] replSet replSetInitiate config object parses ok, 1 members specified
Wed Aug 15 10:02:04 [conn2] replSet replSetInitiate all members seem up
Wed Aug 15 10:02:11 [conn1] runQuery called local.system.replset {}
Wed Aug 15 10:02:21 [rsStart] Socket recv() timeout  127.0.0.1:27017
Wed Aug 15 10:02:21 [rsStart] SocketException: remote: 127.0.0.1:27017 error: 9001 socket exception [3] server [127.0.0.1:27017]
Wed Aug 15 10:02:21 [rsStart] DBClientCursor::init call() failed
Wed Aug 15 10:02:21 [rsStart] User Assertion: 10276:DBClientBase::findN: transport error: localhost:27017 query: {}
Wed Aug 15 10:02:21 [rsStart] runQuery called local.system.replset {}

I have played with the bind_ip option a bit but had no luck.

Here is my current config file:

dbpath = /hwnet/mongodb/regression_data/replica0
replSet = regression_data/nradmin.icd.teradyne.com
logpath = /hwnet/mongodb/regression_data/mongodb_r0.log
verbose = true
vvvvv = true
rest = true
fork = true

This is running on my work network. I did try the same setup on a standalone Mac installation and the rs.initiate() command works perfectly.

Does anyone have any idea what I should be looking at here? I'm running out of ideas.


texts: How high can I set undo retention on Oracle 11gR2

texts:
Is it viable to set a very high undo retention, as to allow flashback queries several weeks back in time?

Naturally, enough space must be available in the undo tablespaces to contain the amount of undo data required. Are there other limitations I should be aware of?

What happens if there is not enough undo space available? Will production be affected in any way, or is it just a matter of flashback and rollback being limited (snapshot too old etc).

Update:

With a typical undo generation of a little less than 1 GB per day per instance, and up to 64 GB worth of undo space per instance it sounds viable to run with a 30-day undo retention target. No?


texts: Pull valid row from table even when joining table has no valid data

texts:
I have two tables with a 0 to many relationship. Table A holds the one record where Table B can hold 0 to many records. There is a status field in both tables which can be equal to 'U' or 'D'.

I want to be able to pull the one record from TableA where status <> 'D' even where there are no related records in TableB or when all of TableB's related records are equal to 'D'.

In the below code TableA has a good record and TableB has two related records but their Status = 'D'. So, I still want to pull the data from TableA and blank\null fields from TableB:

SELECT A.Field1,
       A.Field2,
       A.KeyField1,
       B.Field1,
       B.Field2,
FROM TableA A
LEFT OUTER JOIN TableB B ON A.KeyField1 = B.KeyField1
AND A.KeyField2 = B.KeyField2
WHERE A.KeyField = '11111'
AND A.Status <> 'D'
AND B.Status <> 'D'
ORDER BY A.KeyField

When I Select from these tables individually such as:

Select *
From TableA
WHERE KeyField = '11111'

Select *
From TableB
WHERE KeyField = '11111'

I get the results I expect, TableA's Status field = 'U' and TableB's Status field = 'D'.  I know the data is there and is equal to what it should be so, I know there's something wrong with my query.


texts: MySql command line cyrillic Iinsert result is corrupted

texts:
Have putty connection.
Putty config : window-->translation-->remote character set-->utf-8
Launch command line MySQL client.
Perform insert  with cyrillic like:

CREATE TABLE `category` (
  `catName` varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY (`catName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into category(catName) values ('абвгдйка');

The result is that corrupted cyrillic symbols inserted into DB.


texts: Has anyone ran into this replication breaking bug before?

texts:
I've been having this issue where replication was breaking on colliding primary keys.  The annoying thing was these were auto increment generated.

I think my issues been tracked down to http://bugs.mysql.com/bug.php?id=45677 for a couple reasons

The slaves that were breaking were using triggers
Slaves with identical schemas sans triggers were not breaking
I was able to reproduce the problem following the reproduction steps in the ticket

What leaves a shadow of a doubt that's the exact bug that's biting me is the schema and application have been running for years with out issue.  It started happening shortly after upgrading mysql versions.  That might seem like a 'well duh' thing but it was an upgrade from 5.1.5 to 5.1.59.  The ticket indicates it's been around since 5.1.35, so should have bitten me much sooner, no?

If you've run into this what version were you running?


texts:
We are running 2 Microsoft SQL Servers - publisher (2008R2) / subscriber (2008 Express) with merge replication.

I tried to make some schema changes via Transact-SQL and partly failed. So tried to drop the offending table from the publication to make the changes but since then I keep getting this error:

  The replication Merge Agent failed to propagate a Publisher schema change to the Subscriber.

I have tried reinitialising the subscription with a new snapshot and failed.
I have upgraded both SQL Servers to the latest service pack.
I used this to get outstanding schema changes:

EXEC sp_enumeratependingschemachanges
    @publication = 'xxx_Publication'

then used this to skip the outstanding schema change:

EXEC sp_markpendingschemachange
    @publication = 'xxx_Publication',
    @schemaversion = 1554,
    @status = 'skipped'

and it has gone from sp_enumeratependingschemachanges now.
Then did new snapshot/reinitialisation but still getting the same error message!

Please can someone advise? Meanwhile there have been changes to both DBs that need to be synced back together.


texts: How to DROP multiple tables from databases?

texts:
I have situation where someone mistakenly execute script for creating tables under wrong database.

Let's say we have database DB1 which is some test database, and we have to create other database DB2. Script for creating tables is intended for DB2, but someone execute it under DB1.

Now my database DB1 has its own correct tables, and other incorrect tables. I know how to write a select query to view them, and I know that all wrongly created tables were created on a specific date, let's say 2009-12-12. But the tables are related with foreign keys, and I can't just drop them in the order that they appear, I must first to remove all foreign keys and then drop all of the tables.

Can someone suggest how to do that?

I can do it with cursor, but that is not elegant solution.


texts: Oracle: separate column histograms for partitions

texts:
I have a partitioned table like MY_TABLE(PART_KEY, DATE_KEY, ...) with a local partitioned index on (PART_KEY, DATE_KEY).  When I collect stats on the table, I end up with a HEIGHT BALANCED histogram on DATE_KEY.

The issue: the DATE_KEY values are not uniformly distributed in each partition, and the buckets in ALL_TAB_HISTOGRAMS look like they reflect the distribution of values across the whole table.

The question: If I am running a query like SELECT * FROM MY_TABLE WHERE PART_KEY=x and DATE_KEY between y and z is there a way I ensure that the optimizer will use a histogram reflecting the distribution of values for the specific partition?

Otherwise, what I think is happening is that the global distribution makes certain ranges of values look more selective than they really are, given that the query will only hit a single partition.


texts: What is the recommended Buffer Pool size on a 2GB RAM PC?

texts:
I installed DB2 Express-C on a PC with 2 GB RAM and running Windows Server 2008 R2 OS. While creating a new database, there is an option to set Buffer Pool size with default set to 4K.

How much is the recommended pool size for the above mentioned configuration?

I also want to know whether to use default Buffer Pool or a separate Buffer Pool for every database.


texts:
I keep getting: Unknown column 'actions_in_group' in 'field list'

Here's the SQLFiddle: http://sqlfiddle.com/#!9/4ddf0/19

SELECT *,
   SUM(actions_in_group) AS total_rows_in_group,
   GROUP_CONCAT(in_collection) AS complete_collection
   FROM (SELECT stream.*,
          COUNT(stream.id) AS actions_in_user_group,
          GROUP_CONCAT(stream.id) AS actions_in_user_collection
       FROM stream
       INNER JOIN follows
       ON stream.user_id = follows.following_user
       WHERE follows.user_id = '0'
       GROUP BY stream.user_id,
            date(stream.stream_date)
     ) As something
   GROUP BY object_id,
            date(stream.stream_date)
   ORDER BY stream.stream_date DESC;


texts: Multiple product attribute inventory database design

texts:
I have a database to build based on inventory for my shop.   This is the current data that I have put into tables. This is the package:

This is the product attribute:

This is the table to produce

The problem is, each product has different packages. The data shown is the product that has 2 packages, "color" and "power".

So the question is:  how to do "color" and "power" as column on the database?
and what will happen if it has more than 2 packages?

I am using MySQL for this.

What I'm going to do is to track the quantity,

product-23 : white, 100 = 30qty
product-23 : white, 50 = 30qty
product-23 : black, 100 = 30qty

What I have is 2 tables "package" and "values", includes already have tables like product that has product id.

So whenever one user is trying to purchase product-23 : white, 100, it will check the quantity on the database.

Here is the fiddle


texts: Postgresql: Optimize finding all distinct JSON keys from a colulmn

texts:
I'm looking for a way to index / optimize finding only the keys in a JSONB column.  The use case is an ETL operation where I need to take data stored in the JSONB column and output it as delimited text with headers (the keys in this case). The tables have millions of rows, so caching or iterating over all data to discover the complete set of keys isn't practical. The library I'm using needs the headers up front.

I can do the following query which works but will be impractically slow at scale.

SELECT DISTINCT jsonb_object_keys(jsondata)
FROM foo

This query on a 200k row table takes about 3 minutes to run.  I have tables an order of magnitude larger than that.  An explain shows an obviously undesirable full table scan:

Unique  (cost=42283418.97..42372645.97 rows=198454 width=1282)
  ->  Sort  (cost=42283418.97..42328032.47 rows=19845400 width=1282)"
        Sort Key: (jsonb_object_keys(jsondata))"
        ->  Seq Scan on "foo" (cost=0.00..121283.40 rows=19845400 width=1282)

Is there a way to index only the keys in a JSON/JSONB field?


texts: Did SQL Server ever add user principal name support?

texts:
I ran into an issue today where the User Principal Name was greater than 20 characters in length, so the samAccountName was capped at 20.
This resulted in SQL Server not finding the user when the UPN was typed in, even in Domain\Username format.
Googling has led me to several pages from 2014 or older, and even a Q&A on this forum. A detailed explanation may have been on Microsoft Connect, but it's retired and no longer available
Can someone confirm that even SQL Server 2016 and beyond (or Azure) still only support samAccountNames?


texts:
I am working on a project with a Mongo Database for storage.
The current setup is one Virtual Machine with a single Mongo Database.
We notice a performance degradation when executing some heavy queries.

Would it help to create a replication set on the same Virtual Machine and run the queries on the secondary member (readPreference=secondary)? I know that the best practice would be to put each member on a different machine, but for our client this not an option.


texts: WAL buffer checkpoint/flush time during serialization isolation

texts: I have a doubt regarding the flush/checkpoint time of WAL buffers during serialization isolation in Postgres. Is it possible to lose acknowledged writes even if transaction runs in serialization isolation level? What happens if the transaction was committed to WAL buffer and machine crashes after that and there is no recovery possible? Does Postgres always commit serialization isolation transactions to disk?


texts: How to implement a dynamic referral program?

texts:
Example: Currently, we will provide 100 credits each to both the referrer and referred.

Then in the future, we might want to implement a different referral program. Such as change credits to 200 instead of 100.

Then again in the future, give each 100 credits as well give referrer 50 if the referred spends 100 with us for the first time.

Then again in the future, give each 100 credits as well give referrer 50 each time the referred spends 100 with us.

Also, sometimes restrict the program to a few users only.

"Dynamic referral": We should be able to create different referral programs in the long course. Should be dynamic and able to take stats from the data.

Is it OK to change the database every time we come up with a new referral program?

I'm confused. How to create a schema/design/table(model) for such? Please help.

Note: I didn't include any design attempt because I couldn't think of it. I'm confused. I want it to be generic. I could build models for different referral programs. But I'm looking for something more generic. Is it even possible? I wonder.


texts:
There are plenty of discussions on the benefits and drawbacks of using hashes as primary keys, but in my case I have a database of webpages that I need to look up by address, and these addresses are longer than the maximum text index length.  Also, it's quite the performance hit to index on varchar.

Until recently I was using MD5 checksums to look up addresses due to the speed of the function and a limited need for protection against hash collisions.  But this specific application has the potential for the submission of "database-malicious URLs" that are invalid*, but crafted with the same checksum as valid URLs.

Is there a big performance hit switching from a primary key using 128-bit MD5 to a primary key of SHA-224+ (the smallest "unbroken" SHA-based hash I can find)?  Should I index these as byte arrays or chars?  Or is there another solution if I need reverse lookup AND collision security?

My DBMS is MySQL 5.7, but I would consider switching if there's an amazing solution in another DBMS with similar resource requirements that is freely available.

*Invalid as in technically resolvable, but not a "real" URL, i.e. http://example.com/reallyLongStringThatYieldsAHashCollisionWithARealURL


texts: SSMS 2016 Maintenance Plan Designer Perms

texts:
We have some older maintenance plans that were built with SSMS 2014 on a Sql Server 2014 instance.  The connection defined for the plan uses a Sql Server account with sysadmin perms.  When you edit with SSMS 2014, all works just fine.  On my local box, I have SSMS 2016, also have sysadmin perms.  When I edit the plan, the "Notify Operator Task" has a blue indicator that something is wrong

When I try to edit the task, I get the following error:

The sql error log shows the following errors:

Error: 18456, Severity: 14, State: 8.
Login failed for user 'SqlAccount'. Reason: Password did not match that for the login provided. [CLIENT: xx.x.x.x]

The SqlAccount is the one defined in my "manage connections" from the maintenance plan.  Is there some new permission that SSMS 2016 needs?  Are plans created in SSMS 2014 not fully compatible with the SSMS 2016 editor?


texts: How to select and show all the tickets from last calendar year?

texts:
It seems SQL calls varies a lot, and cannot find how to do this in Firebird, where SQL dialect is 3.

There's a table called TICKETS. This table has INSERT_TIME which is TIMESTAMP. How do I select all the tickets which has insert time year = 2016?


texts:
I have had invalid public synonyms in my database. Some of them references to ordinary schema objects and others to sys schema objects.

I compiled all these public synonyms by the following command:

alter public synonym mysynonym_name compile;

My supervisor suddenly told me that I should not have compiled SYS schema synonyms. I tried to invalidate them by the following way:

exec dbms_utility.invalidate(444849);

Where 444849 is the synonym object ID. But got the following error:

ORA-24237: object id argument passed to DBMS_UTILITY.INVALIDATE is not legal
ORA-06512: at "SYS.DBMS_UTILITY", line 1053
ORA-06512: at line 2

Why?

Can compilation of these synonyms cause some problem?

Thank you a lot, I am waiting for your recommendations and answers. Please share your knowledge with us.


texts: SQL Server Baseline and Performance Optimization

texts:
I've been struggling a little bit around Performance Troubleshooting including baseline and troubleshooting of SQL Performance.

Could anyone help with this or point me to where I can possibly get a helpful information around this topic?


texts: How to group two query that have already been grouped

texts:
I am creating a chat system and i have two queries: The first query selects the Max(id) meaning the last message that has been received or sent by the member that is connected and that has not yet been read  and the second selects the last message that has been received or sent by the member that is connected whether it has been read by the other member or not. Those messages are group by users as long as the users is not the one connected

First query

    SELECT MAX(`id`)   AS id, `from_id`, `to_id`,  `sent`, `read_statu` FROM `ojm_chat` WHERE

CASE
WHEN `to_id`='$id_ojm_peoples' THEN `read_statu`!='$id_ojm_peoples'
END

AND (`from_id`='$id_ojm_peoples' OR `to_id`='$id_ojm_peoples')

AND (is_deleted!='$id_ojm_peoples' AND is_deleted >=0)

GROUP BY (IF(`to_id`='$id_ojm_peoples', `from_id`, `to_id`))

Second query

SELECT MAX(`id`)   AS id, `from_id`, `to_id`,  `sent`, `read_statu` FROM `ojm_chat` WHERE

    CASE
    WHEN `from_id`='$id_ojm_peoples' THEN `read_statu`>='0'
    END

    AND

    (`from_id`='$id_ojm_peoples' OR `to_id`='$id_ojm_peoples')

    AND

    (is_deleted!='$id_ojm_peoples' AND is_deleted >=0)

    GROUP BY (IF(`to_id`='$id_ojm_peoples', `from_id`, `to_id`))

In order to make this two query one, I united them using a UNION

    SELECT MAX(`id`)   AS id, `from_id`, `to_id`,  `sent`, `read_statu` FROM `ojm_chat` WHERE

CASE
WHEN `to_id`='$id_ojm_peoples' THEN `read_statu`!='$id_ojm_peoples'
END

AND (`from_id`='$id_ojm_peoples' OR `to_id`='$id_ojm_peoples')

AND (is_deleted!='$id_ojm_peoples' AND is_deleted >=0)

GROUP BY (IF(`to_id`='$id_ojm_peoples', `from_id`, `to_id`))

UNION

SELECT MAX(`id`)   AS id, `from_id`, `to_id`,  `sent`, `read_statu` FROM `ojm_chat` WHERE

CASE
WHEN `from_id`='$id_ojm_peoples' THEN `read_statu`>='0'
END

AND

(`from_id`='$id_ojm_peoples' OR `to_id`='$id_ojm_peoples')

AND

(is_deleted!='$id_ojm_peoples' AND is_deleted >=0)

GROUP BY (IF(`to_id`='$id_ojm_peoples', `from_id`, `to_id`))

PROBLEM

However this did not group the users with their Id  but it rather gave me the users from the first query and then the users from the second query without grouping the users together. This is the output of the query above.

As you can see some users appear twice instead of ONCE (grouped).

Questions
Please how to correctly Group this ? Each query have already been grouped once. Is there a way to group them together ?

Thanks


texts:
To use the wizard from SSMS, we had to provide full access to "everyone" to the shared network location.

Is there a way to avoid this and just limit the access to a specific user?

Already tried the service account but that seems not to be working.

The message received from the wizard is:

\\Hostname\sync' is not a valid shared network location.
(Microsoft.sqlserver.Management.hadr Tasks)


texts: Update statement with "case syntax" is unable to update a specific column in temp table

texts:
With reviewing a stored procedure for a current SSRS migration. I noticed the case syntax listed below for the temp table (#tempShip) is not taking affect for the column "ord_status" in the temp table. The stored procedure that collects data for the temp table is rather massive so I will refrain from posting. But I believe with the code provided for the update it may do some justification.

Questions:

Can you utilize a case statement on a temp table as an update?
Is there another method I should try besides the case statement? (I don't think using a subquery with "IF" or "WHERE EXISTS with a select statement" would aid in this)

Notes:

***Stored procedure executes without any errors and temp table is populated with data as expected (But update with case statement does not have expected results)

***With doing a simple case statement as seen below the translation occurs fine

Basic query to test case translation of ord_status data in temp table. Output is correct

select ord_status,
 CASE
     WHEN RTRIM([ord_status]) = 'N' THEN 'New'
     WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
     WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
     ELSE ''
 END
 from #tempShip

***End of stored procedure where temp table is being updated

UPDATE  [#tempShip] SET
        [#tempShip].[DUP] = 1,
        [#tempShip].[ord_status] =
           CASE
                 WHEN RTRIM([ord_status]) = 'N' THEN 'New'
                 WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
                 WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
                 ELSE ''
           END

        FROM #tempShip
         INNER JOIN
    (
        SELECT X.order_N_ext, COUNT(X.order_N_ext) AS dup
        FROM #tempShip AS X
        GROUP BY X.order_N_ext
    ) AS Y
         ON #tempShip.order_N_ext = Y.order_N_ext
    WHERE Y.dup > 1;
    ----- -- mbs 3/30/2017  #22844 - add parameter @ship_type
    SELECT * FROM #tempShip
    WHERE ship_type LIKE @ship_type
    -------<<<

END
DROP TABLE #tempShip

**order_N_ext

SELECT O.order_no
    , O.ext
    , CONVERT(varchar(20), O.order_no) + ' - ' + CONVERT(varchar(2), O.ext)
AS 'order_N_ext'

Data Example:

Ord_Num  BD order_N_ext Loc     Cust        Ship_date                      Ord_Status
1252868  1  1252868 - 1 RAS HQ  21115   2017-04-19 00:00:00.000 2570.63    P
1253995  1  1253995 - 1 RAS HQ  11471   2017-04-19 00:00:00.000 1270.45    Q
1254526  0  1254526 - 0 RAS HQ  24104   2017-04-19 00:00:00.000 2214.67    P


texts:
I am using SSIS to export an Excel 2007 file to a SQL table. When I execute my package locally it works; however, on the server I get the following error:

Data Flow Task:Error: Excel Source failed the pre-execute phase and returned error code 0xC02020E8.
Data Flow Task:Error: Opening a rowset for "Sheet$" failed. Check that the object exists in the database.
Data Flow Task:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

I have tried the following, with no success:

changing the sheet name to just A
Installing the Ace 12.0 driver
Converted my Excel file to a flat file (that takes a long time, so the app times out)


texts: Best design for a tree-like structure database

texts:
I'm designing a database in MS Access which is used for tracking the locations of a variety of chemical solutions, made in batches.

Each batch can be split (aliquoted) practically indefinitely. That is, we may manufacture 100g of a product, which is aliquoted into two 50g vials. One is kept in storage, the other is shipped somewhere else and further split into 50 1g vials. These vials may be transported at any time, and may be aliquoted further.

I would like to be able to track the history of any particular vial. Where it has been (and when), including the history of its 'parent' vial and the parents of that.

That is, if I look at the history of vial #7 in the above image, I want to see the history of vial #7, (reverse chronologically, for example), followed by the histories of vials #6, #3 and #8.

I'm struggling to find the best way to record this data; currently I have one main table (plus several lookup tables) which has one line for each 'node'. A node represents a vial being in a location; so each line has an AutoNumber primary key, a 'parent' (the primary key of the previous node - either the previous location of the vial, or the parent vial if it was aliquoted), a 'location' field, the batch number etc.

There is already redundancy here because the batch number is on each node whereas it really only needs to be on the first node after manufacture (it clearly doesn't change when the batch is aliquoted).

So the data structure is the first issue here.

Once I've got the data structure, does anyone have any ideas for how best to show historical data for a vial?

Many thanks


texts: A Shop can have Products from a Supplier

texts:
I'm creating a Platform where Suppliers have Products that Shops can choose, add to their Shop and start selling.

My situation is as follows:

A Supplier can have many Products (for example: 'T-shirt', 'Iphone Case') (Supplier->product: One-To-Many)
A Supplier Product can have many SKU (for instance: 'T-shirt Small Black', 'T-Shirt Small White') (Product->sku: One-To-Many)
A Shop should be able to "copy" Products from the Supplier, So would I need a Shop_Product table here? (Shop->Product: One-To-Many)

The main problem is that my Shop has SKU's from the Supplier but no Products. If I add a Shop_Products table representing a relationshhip, then how should I "couple" my Shop_Skus to that table?

Should I add a Shop_Product table, next to the existing Shop_Sku table?

My current database structure is depicted in the diagram below:

With a Shop_Product column it would look like this

But then there is no relationship between the Shop_Product and the Shop_Sku which makes this structure irrelevant.

Any help is welcome!


texts: Add Secondary Node to existing WSFC Cluster

texts:
I need to add new server in my existing WSFC cluster server setup. I need few details on following :
1. I have Windows Server Failover Cluster setup on 2012 R2 with SQL Server 2012, Can I have my new server with Windows Server 2012 R2 with SQL Server 2016?
2. Can I add it on my existing AlwaysOn Availability Group, which is configured with SQL Server 2012?


texts: Why is Echo statement in SQL query not writing in text file?

texts:
Following code is used for writing a number in the text file but instead it is writing "Echo is ON" into the text file.

SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck
Print @CMD

EXEC xp_cmdshell @CMD, NO_OUTPUT

Print @CMD gives ECHO 1>C:\Pharmsuite\Vercheck.txt

Here instead of 1 the query is writing Echo is ON in the text file. What am I doing wrong?


texts: An efficient way to compare two large data sets in SQL

texts:
Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations.

The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and end May 2014 (result = 450K lines). The 2nd data set has the unique StoreKey/ProductKey combinations, for sales begin June 2014, until today (result = 190K lines).

I'm looking to find the StoreKey/ProductKey combinations that are in the 2nd set, but not in the 1st set - i.e. new products sold from the beginning of June.

Up until now, I've dumped the two data sets into temp tables, created indexes for both tables on both keys, and used the EXCEPT statement to find unique items.

What is the most efficient way of comparing such large data sets?
Is there a more efficient way of doing this type of large comparison?


texts: Severity 20 - Fatal Error in LOCAL CURSOR after FETCH

texts:
This week our database on a SQL2014 SP2 CU11 server started throwing fatal errors (severity 20), on the FETCH statement of code, which could be reduced to:

DECLARE @dummy int
DECLARE CurName CURSOR LOCAL FOR
        SELECT TOP (1) t.id FROM dbo.t_table AS t;
OPEN CurName;

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CurName INTO @dummy;
    IF @@fetch_status <> 0 BREAK;
    PRINT @dummy
END;

DBCC CHECKTABLE and DBCC CHECKDB did not find any problem, a server restart did not help (and since it occured on multiple versions of the same database at the same time, it was very unlikely, that this was a database structur problem).

On a developement server with SQL2014 SP2 and only CU6 the same statements runs without problem (on a SQL2017 Server it works too).

The table

has around 4 mio rows
is partitioned by an integer column
has an clustered PK over two columns (the partition column plus a bigint)
has 25 indexes (I guess, I should clean up a little bit :-))


texts: Does sp_indexoption cause index rebuilds?

texts:
I want to switch AllowRowLocks=ON on a number of indexes. Some of those indexes are on large tables, and I don't really want to rebuild or reorganize the index.

It is not clear from the MSDN article whether or not the index will be rebuilt.

As a secondary related part of this question, the MSDN article states that the use of this is being phased out and shouldn't be used anymore:

  This feature will be removed in the next version of Microsoft SQL
  Server. Avoid using this feature in new development work, and plan to
  modify applications that currently use this feature. Instead, use
  ALTER INDEX (Transact-SQL).

Looking at the ALTER INDEX, it seems you are forced to rebuild or reorganize. Have I understood that correctly? Through ALTER INDEX can I just reset this row lock option?


texts: How can Stored Procedures and Triggers be Automatically Loaded When MySQL Starts

texts:
Is there any straightforward method of doing as the questions asks?

We are using MySQL 5.5 & Linux.


texts: Mysql select rows with 7 continuous dates

texts:
Imagine a simple table with these columns:
item_id,  date
And values:

CREATE TABLE foo (item_id int, date date);

INSERT INTO foo(item_id, date)
VALUES
    ( 1, '2017-02-10' ),
    ( 2, '2017-02-10' ),
    ( 1, '2017-02-11' ),
    ( 1, '2017-02-12' ),
    ( 1, '2017-02-13' ),
    ( 2, '2017-02-13' ),
    ( 1, '2017-02-14' );

How to select item_ids that have 7 continuous day records in table?

Start and end dates are unknown. It should just be available from any starting date up to 7 continuous days after.


texts: Select * from (every table in an subselect)

texts:
I was trying to get an select * from (for column table result) every table_name with the result bellow, how can i achieve this?

(SELECT a.table_name
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R'
   AND c_pk.table_name = 'MY_TABLE_NAME')

like:

suppose that the result above was:

TABLE_NAME
TABLE_LOG_NOD
TABLE_LOG_CASH
TABLE_LOG_PLUG

i want to return an select * from (only one row of that table)every one of those tables listed.
Beware i cannot use variable, or any other thing related to PL/SQL, so only SQL.

Sorry for my bad english.
I hope it was clear.
Thank you anyone in advance.


texts: Configuring sqlnet.ora and tnsnames.ora on the client side

texts:
I have an application server talking to an Oracle database, using the Oracle instant client. We are troubleshooting a specific bug, and it was asked if I could add the following flags to to my sqlnet.ora file:

DIAG_ADR_ENABLED=OFF
DIAG_SIGHANDLER_ENABLED=FALSE
DIAG_DDE_ENABLED=FALSE

Prior to this request, we did not need a sqlnet.ora file on the client side, with instant client, we could reference the classpath of ojdbc6.jar and the ld_library_path of the directory of the jar, and use a URL like this:

jdbc:oracle:oci:@server:port:sid

Looking at the Oracle Docs, it appeared that I could reference a sqlnet.ora file and/or a tnsnames.ora file on the client side if I added environment variables for ORACLE_HOME and TNS_ADMIN.

I created the sqlnet.ora file with the parameters above, and added some trace parameters so that I could verify that the file was being read:

TRACE_LEVEL_CLIENT=10
TRACE_DIRECTORY_CLIENT=/tmp

I also created a tnsnames.ora file:

APPDB =
    (DESCRIPTION =
        (ADDRESS LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = *host*)(Port = *port*)))
        (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = *service*)))

Finally, I added environment variables, and restarted the application. I tried with the original simpler URL, and it was clear that the settings weren't picking up, as I would expect with the trace flags that a file would be created if it were being read. Next I tried to create the URL using the TNS name, which per the docs sounded like it would look in TNS_ADMIN for additional files to connect with.

jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = *host*)(Port = *port*)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = *service*)))

So far, I haven't seen any trace files come up, and I have a feeling I'm doing a part of this wrong, or missing a step, or using the wrong URL. I tried a few variants of connection strings that would reference "APPDB" as in my tnsnames.ora file, but either it doesn't work at all, or it connects, but does not seem to be picking up the settings from sqlnet.ora.

Any ideas on what I could be missing, or if in fact applying these settings is only possible on the DB side?


texts: How to DELETE duplicate entries on a field

texts:
I have a huge table that has 1,990,546 users with numerous data.  Sometimes I get email lists and dump them into this table also.

I want to delete users with duplicate email addresses.  However, I want to keep users WHERE chips > 0.

This query ran for over an hour before I terminated it:

SELECT id, email
FROM users
WHERE id IN
    (
        SELECT a.id
        FROM users a, users b
        WHERE (a.email = b.email)
        AND (a.chips > 0)
    )
LIMIT 100;

Is there a faster or better way to do this?


texts: SQL Server 2005 Restore from Full and Differential Backup

texts:
I'm not a DBA, however I have been a developer for 10yrs and am competent.

I wish to move our corporate application to a hosted service and I need to copy the data. The data centre is too far from our office to deliver a disk and ftp is too slow. Our current database uses SQL Server 2005; the target is SQL Server 2008.

My plan is to courier a disk with a full backup on a Friday morning and do a restore at the data centre over the weekend.  I then plan to FTP a differential backup with deltas from Friday's backup and restore the differential to make the data current.

To test the process, I plan to restore a full backup then create a differential and restore that.  My only environment for testing this is our production server.  I believe that I can restore the backup to a new database name using the following T-SQL:

-- make sure have good backup
RESTORE VERIFYONLY FROM DISK = 'x:\mypath\data.bak'

-- get logical names
RESTORE FILELISTONLY FROM DISK = 'x:\mypath\data.bak'

-- restore to new db
RESTORE DATABASE MyNewDb
FROM DISK = 'x:\mypath\data.bak'
WITH
    RECOVERY,
    STATS = 10,
MOVE 'olddb_Data' TO 'x:\mynewpath\MyNewDb.mdf',
MOVE 'olddb_Log' TO 'x:\mynewpath\MyNewDb.ldf',
MOVE 'sysft_ft_resume' TO 'x:\mynewpath\ft_resume'
GO

By running something similar with VERIFYONLY I have observed that this only works with a full backup if no subsequent differential has been run.

My questions are:

Is this safe to run on a production database (I am paranoid about overwriting files, etc)?
Since a differential appears to alter the full backup, will my plan to ftp the differential work given that the full backup will have no knowledge of the differential?

I don't think this is a complex task although I will pay for a professional, if that's necessary.

Any other suggestions would be gratefully received.