texts: Strange behaviour with sample sizes for statistics updates

texts:
I've been playing around investigating sampling thresholds with statistics updates on SQL Server (2012) and noticed some curious behaviour. Basically the number of rows sampled seems to vary under some circumstances - even with the same set of data.

I run this query:

--Drop table if exists
IF (OBJECT_ID('dbo.Test')) IS NOT NULL DROP TABLE dbo.Test;

--Create Table for Testing
CREATE TABLE dbo.Test(Id INT IDENTITY(1,1) CONSTRAINT PK_Test PRIMARY KEY CLUSTERED, TextValue VARCHAR(20) NULL);

--Insert enough data so we have more than 8Mb (the threshold at which sampling kicks in)
INSERT INTO dbo.Test(TextValue)
SELECT TOP 1000000 'blahblahblah'
FROM sys.objects a, sys.objects b, sys.objects c, sys.objects d;

--Create Index on TextValue
CREATE INDEX IX_Test_TextValue ON dbo.Test(TextValue);

--Update Statistics without specifying how many rows to sample
UPDATE STATISTICS dbo.Test IX_Test_TextValue;

--View the Statistics
DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER;

When I look at the output of the SHOW_STATISTICS I'm finding that the "Rows Sampled" varies with each full execution (i.e. the table gets dropped, recreated and repopulated).

For example:

Rows Sampled

318618
319240
324198
314154

My expectation was that this figure would be the same each time as the table is identical. By the way, I don't get this behaviour if I just delete the data and re-insert it.

It's not a critical question, but I'd be interested in understanding what's going on.


texts:
im confused here , i create a table and have 2 columns id and name.

Now id is primary key , not null and auto increment.

Suppose i fire: insert into table (name) values('test');

this will give me:

1  | test

Now if i fire : alter table table_name auto_increment = 5;

this will populate id with 5 on next insert.

now if i delete value of id =5 and again re-insert : insert into table (name) values ('test2');

it gives me :

 6  | test2

why it doesn't give me 5 again?

Also is it not possible that we can't reset the existing auto_increment values to desired one in MySql?


texts: Unable to access sql database with login and password

texts:
I'm writing Windows Service in C# and inside of it I'm unable to connect to the database.
This is the question I've asked recently:
SqlException: Cannot open database "path\db.mdf" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'

What I've done so far:

1) In server properties set flag "Identity verification with Sql Server and Windows"
2) Created login for sql server. Added user to the database with that login.
3) Added all available roles to this login.

Part 1:
If I connect to the server using Sql Server 2012 Identity Verification and than trying to attach database that is in some folder I get:

CREATE FILE encountered operating system error 5(Access is denied.)
while attempting to open or create the physical file 'path\db.mdf'.
(Microsoft SQL Server, error: 5123)

But I can attach the database if it's in this folder:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

That's in SSMS.

Part 2:
What do I get when trying to attach database from Visual Studio 2012:

The attempt to attach to the database failed with the following information:
Login failed for user test_user

If I use this connection string in my code:

<add name="ConnectionName"
         connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;
     provider=System.Data.SqlClient;provider connection string='data source=(LocalDB)\v11.0;
     attachdbfilename=&quot;path\db.mdf&quot;;
     connect timeout=30;
     User Id=test_user;Password=1;
     MultipleActiveResultSets=True;
     App=EntityFramework'"
         providerName="System.Data.EntityClient" />

I get from my Windows Service app (written in C#) the following exception:

EntityException: The underlying provider failed on open.

Inner excetion: SqlException: Login failed for user test_user.

I'm sure I typed the login properly. I get this error whether the database is in MSSQL\DATA folder or not.

There is something wrong with the permissions to access some folders on my computer, but I don't know why, I've given this user all the permissions!

Also, one time after computer restart the windows service started properly and was able to connect to the database, but after some time it lost this ability. And I don't know why.

Please help, I'm struggling with this issue for the last 4 days. Thanks!


texts: what is the best approach for messaging system with mysql?

texts:
I want to build a messaging system and looking for best approach for DB schema.

Here's the system that i want to build.

There will be registered users. They can send message to each other.
These users will belong to one or more organizations. So admin can send msg to organizations.
These users may also belong to one or more groups and admin or users can send msg to groups.

Also i want to add status for messages like new, old, deleted.

Can someone recommend me table schema ?

Important note : one user can be in 2 groups so if sender wants to send a msg to those 2 groups, the user should get only one message not two.
It's the same for organizations.


texts:
I have a simple test table like this:

CREATE TABLE MyTable (x INT);

Within a transaction, I try to add a column and then insert into the newly created column:

BEGIN TRANSACTION;
    PRINT 'Adding column, ''SupplementalDividends'', to MyTable table.';

    ALTER TABLE MyTable
        ADD SupplementalDividends DECIMAL(18,6);

    PRINT 'Column added successfully....';

    PRINT 'Ready to INSERT into MyTable ...';

    INSERT INTO MyTable (x, SupplementalDividends)
        VALUES (1, 3.2);

    PRINT '**** CHANGES COMPLETE -- COMMITTING.';
COMMIT TRANSACTION;

The Problem is an error message when I run the above code:

Invalid column name 'SupplementalDividends'.

Why is this causing an error? If I add the column in a different batch, outside the transaction, it'll work. My problem is that I want to add the column within the transaction. Why the error?


texts: Implications of changing SQL Server collation

texts:
We are currently using SQL Server 2016 and currently the collation set on our database is SQL_Latin1_General_CP1_CI_AS. Our Analaysis services' collation is set to SQL_Latin1_General_CP1_CS_AS.

The recommendation from our vendor is that the relational database and the Analysis services collation should both be case sensitive.

We would like to understand if there are any downside to changing the collation on our relational database to be case sensitive as well, to match with the Analysis services.


texts: Huge Heap Table and table compression on SQL Server 2016

texts:
My database size is huge, and recently I noticed that a new table added few months ago is the culprit.

Here is the table script.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Entry_tracker](
    [S.Number] [int] IDENTITY(1,1) NOT NULL,
    [EntryId] [varchar](50) NOT NULL,
    [EventNumber] [varchar](18) NOT NULL,
    [Data] [varbinary](max) NOT NULL,
    [TrackDateTime] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Entry_tracker] ADD  CONSTRAINT [DF_Entry_tracker_TrackDateTime]  DEFAULT (getdate()) FOR [TrackDateTime]
GO

I collected information on this table, and learned that the table has about 16 million rows and the table size is 1.4TB.

Still, I think that the table size is huge for the no. of records.

This table is not queried by the application. It just stores different versions of same entries from another table.

I checked the fragmentation information and it shows 0 average fragmentation with 93% average space used.

Since I am using SQL Server 2016, I though I could use table compression, so  tried sp_estimate_data_compression_savings to estimate possible space savings.
 However, the results shows no savings.

size_with_current_compression_setting(KB) is equal to size_with_requested_compression_setting(KB)

Can anyone direct me in finding the issue with this table?

It is really important, as this is taking a lot of disk space.

Appreciate your help.


texts: mongodump replicaset - 10 hours data loss acceptable - do we need --oplog (oplog.bson) option?

texts:
We have a 3 node replica set. We take backups from secondary with mongodump. Our customer said that it's ok too loose 10 hours of data in worst case.

So we decided we don't need point in time recovery --oplog (oplog.bson).

Is this correct in our use case with 10 hours of data loss acceptable?

Is oplog the same as MySQL binary logs?


texts:
In Postgresql 9.6 after reboot first I got:

2018-04-21 14:36:27 UTC LOG:  database system was shut down at 2018-04-21 11:01:15 UTC
2018-04-21 14:36:27 UTC LOG:  invalid checkpoint record
2018-04-21 14:36:27 UTC FATAL:  could not locate required checkpoint record
2018-04-21 14:36:27 UTC HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.6/main/backup_label".
2018-04-21 14:36:27 UTC LOG:  startup process (PID 11768) exited with exit code 1
2018-04-21 14:36:27 UTC LOG:  aborting startup due to startup process failure
2018-04-21 14:36:27 UTC LOG:  database system is shut down

Which I solved by deleting that file, and then I got:

2018-04-21 14:38:50 UTC LOG:  invalid primary checkpoint record
2018-04-21 14:38:50 UTC LOG:  invalid secondary checkpoint record
2018-04-21 14:38:50 UTC PANIC:  could not locate a valid checkpoint record
2018-04-21 14:38:51 UTC LOG:  startup process (PID 11982) was terminated by signal 6: Aborted
2018-04-21 14:38:51 UTC LOG:  aborting startup due to startup process failure

Which I resolved by:

/usr/lib/postgresql/9.6/bin/pg_resetxlog -f main

Now I get:

2018-04-21 17:45:25 UTC LOG:  database system was interrupted; last known up at 2018-04-21 14:53:06 UTC
2018-04-21 17:45:25 UTC LOG:  database system was not properly shut down; automatic recovery in progress
2018-04-21 17:45:25 UTC LOG:  invalid record length at 1F/8D000108: wanted 24, got 0
2018-04-21 17:45:25 UTC LOG:  redo is not required
2018-04-21 17:45:25 UTC FATAL:  could not access status of transaction 2562642
2018-04-21 17:45:25 UTC DETAIL:  Could not open file "pg_clog/0002": No such file or directory.
2018-04-21 17:45:25 UTC LOG:  startup process (PID 13311) exited with exit code 1
2018-04-21 17:45:25 UTC LOG:  aborting startup due to startup process failure
2018-04-21 17:45:25 UTC LOG:  database system is shut down

Which I couldn't find any fix, database was recovered via barman few hours ago and was working fine.

Any help is appreciated.


texts: How to remove datafile from oracle database which is already remove locally?

texts:
Created one dbf file from my database using following command,

alter tablesapce PAULA_INDEX add datafile '/u02/oradata/hfd1/index/index17.dbf' size 2000M;

This dbf file is created in my server locally /u02/oradata/hfd1/index/ path. And I am also able to view this file in my oracle database.

Now I want to delete this file from my database. And accidentally it was already removed locally from /u02/oradata/hfd1/index/ path. Now I am not able to drop the file from my database. Please suggest what I can do.
I have tried,

alter tablespace PAULA_INDEX drop datafile '/u02/oradata/hfd1/index/index17.dbf';

But it is not dropped.


texts: MySQL "SLEEP" command throws syntax error

texts:
The 'SLEEP' command in MySQL is throwing a syntax error:

mysql> sleep(5);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sleep(5)' at line 1

This is on MySQL 5.6.23.  I believe I have used SLEEP on the current version of MySQL on this server in the past, but I'm not sure what could have changed since then.

I've tried restarting the database server, but the problem persists.  For what it's worth, the reason I want to use SLEEP is to determine the rate of Queries and Questions running on the server, ie

show global status like 'Qu%'; sleep(60); show global status like 'Qu%';


texts: I work for a company of about 80 employees and they all seem to need access to our database on a regular basis. I don't have time to write applications to meet all of their needs so I'm just going to install SQL Server Management Studio on each employee's computer so they can help themselves to the data. Does anyone know if there's a way to write a wrapper for SQL Server that would save all database transactions to a text file so I can monitor what changes everyone is making?


texts: What architecture is best to store a large 3 dimensional array in MySQL?

texts:
I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy:

  { x, y, z } = { column, row, time }

The z dimension will be time, and I would like to store as much as possible. We are hoping for approximately 1TB of total stored data with modest performance. In other words, we are looking to take a snapshot of one MySQL Table, every 30 seconds, for years. It will be the same table every time, and will be about 10 columns {x} by 1000 rows {y} (approximately 50KB). So if you will, we want to take a snapshot of a 50KB table every 30 seconds.

So this creates the following problem: averting the need to store an endless amount of tables. I have read in various posts on Stack Exchange that it's bad architecture to have millions of tables in a database, and with such a design performance will suffer. So here are the two possible architectures I can think of:

Create a new table and name it using an epoch time, and create millions of new tables endlessly (not good).
Create one database with two columns: epoch_time and json. For every snapshot of the original table, every 30 seconds, convert that into a json string and store the entire table in the json column. So basically, a database with millions of rows containing json serialized tables.

Would number 2 be the best architecture? Is there a better way that I may be missing?


texts: Tiny table causes extreme performance degradation, fixed by forced VACUUM. Why?

texts:
I use PostgreSQL 9.6.

I have a query that joins 17 tables, 9 of those having several million rows. The query was running fine but its performance degraded rapidly this week. EXPLAIN's output didn't help (all scans are index scans except for the very small tables) and I had to try and remove tables from the query to isolate one that caused the degradation.

It turns out that an unremarkable table containing 40 rows broke the query: 800 ms without the table vs 30 s with it. I ran VACUUM FULL on the table, which ran in about a second and now the performance is back to normal.

My questions:

What can explain that a <10kb table breaks performance like this?
How to avoid the same problem in the future?

In the process of debugging, I made a base backup to another server, so I have two filesystem-level copies of the DB, one of them where I didn't run VACUUM FULL. When I logged on to the unvacuumed copy with pgAdmin, I got the following message:

  The estimated rowcount on the table "public.clients" deviates significantly from the actual rowcount. You should run VACUUM ANALYZE on this table.

The unvacuumed table has 40 rows counted and 0 estimated. Here are the rest of its statistics in a screenshot.


texts: Export/import data from Excel into a table with queries

texts:
I'm a newbie interested in learning and implementing things I learn. I've been working on SQL Server 2014 these days and I'm loving it.

I've come across some scenario's where I would have an Excel document with 70,000 rows and about 10-12 columns imported into SQL Server (a table), make use of it to compare/insert/modify existing data in other tables. I'm currently using the manual feature available in GUI (right click >> Tasks >> Import) to import and export when needed; however, I've got informed by one of my colleagues that the same procedure can also be done through script/queries.

Can someone let me know what is the correct way of writing a query to import XLS, XLSX, CSV format files into a table?

P.S.: I've also tried reading some posts about OPENDATASOURCE and OPENROWSET where I've not been successful, always getting some errors about which I have no clue. So, looking to see if there are any other ways, and keen to learn their procedure.


texts: Connect to SQL Server using OLEDB or ODBC?

texts:
We have a desktop application written in Visual Basic .NET. One our clients needs to use ODBC instead of OLEDB to connect to the SQL Server 2008 R2 Database. The company has several databases including Oracle, IBM DB2, and PostgreSQL.

We can migrate the connections, but we need to know what is the better: OLEDB or ODBC when connecting to SQL Server 2008 R2?


texts: Help for grouping a field and taking total sum

texts:
I have a sorted data in a table

F1  F2  F3  F4
A   M   X   1
A   N   Y   2
A   P   X   3
B   M   X   3
B   N   Y   1
B   P   X   5

but I want the sub count of values in F1 column for every row. like this

F1  F2  F3  F4  Sub Total
A   M   X   1   6
A   N   Y   2   6
A   P   X   3   6
B   M   X   3   9
B   N   Y   1   9
B   P   X   5   9

I tried to take sum

select *, sum(count) from table group by F1, F2, F3;
But this is not giving total in the last column.

Please help me


texts: Unable to connect to the Reporting Services instance through SSMS with windows authentication

texts:
I am trying to connect to Reporting Services instance SSRS with SSMS so that I can update some of the parameters on display of remote errors and retention of execution log statistics.

I receive the error message:

The username or password is not valid or you do not have permission to access this server. (Microsoft.SqlServer.Management.UI.RSClient)

Server 2016 with SQL Server 2017.  My network account is an admin on the local server and I have no problem connecting to all DB's on the sql side.


texts: Why does jsonb use more space for storing integers?

texts:
I did some small tests to try these data types, found some puzzling results.

Summary:

jsonb uses more space for integers than strings:

select pg_column_size('{"a":"1"}'::jsonb) as json_size

18

select pg_column_size('{"a":1}'::jsonb) as json_size

28

Why does the size of jsonb inflate when using integers?

Long version:

I was originally interested in using an array of integers, here are those tests. In each pair of comma separated numbers, first corresponds to json_size (bytes), second number is array_size (bytes).

select pg_column_size('[]'::jsonb) as json_size,
       pg_column_size(array[]::smallint[]) as array_size

8,16

select pg_column_size('[1]'::jsonb) as json_size,
       pg_column_size(array[1]::smallint[]) as array_size

20,26

Check if jsonb actually stores integers as plain text; if so then this next one should use 4 bytes more for jsonb:

select pg_column_size('[12345]'::jsonb) as json_size,
       pg_column_size(array[12345]::smallint[]) as array_size

22,26

Strange, it uses 2 bytes more, no idea what that means. Try some more practical and realistic data now:

select pg_column_size('[123,234,345,456,567,678,789,890]'::jsonb) as json_size,
       pg_column_size(array[123,234,345,456,567,678,789,890]::smallint[]) as array_size

104,40

What happened to jsonb here? What caused it to inflate? Maybe if I use smaller numbers that have less characters...

select pg_column_size('[1,2,3,4,5,6,7,8]'::jsonb) as json_size,
       pg_column_size(array[1,2,3,4,5,6,7,8]::smallint[]) as array_size

104,40

Nope. What about strings?

select pg_column_size('["1","2","3","4","5","6","7","8"]'::jsonb) as json_size,
       pg_column_size(array[1,2,3,4,5,6,7,8]::smallint[]) as array_size

48,40

It seems that jsonb likes strings and not integers.

I quickly made it a rule of thumb that I should only use strings in jsonb and use native integers or arrays for numeric data, but I don't know if this rule is misguided or why this anomaly occurs.


texts: Is a Non-Clustered index needed when referencing a small table with a Foreign Key

texts:
I'm not a DBA, excuse me if this question sounds dumb. Hope I can get help from an experienced DBA, because I'm questioning this for a long time.
Assume there are 2 tables, one table with a lot of records, and one table is just static data with 2 records (for example: a Yes/No table).
As example take these 2 tables:

Table_Yes_No (only 2 records: Yes and No. Of course they have an incrementing clustered PK)
Table_Form (Very big table with lots of records, and one column has FK to the PK of Table_Yes_No).

Now my question; Is it worth putting an index on the FK (reference from Table_Form to the Table_Yes_No table)? The column will only contain 1 or 2 (but not sorted, because no index). Is it worth indexing such a FK?
An example in SQLFiddle: http://sqlfiddle.com/#!18/51614/3/0
That column will be queried anyway, the question is will the index help the performance or not.
The small table is static data, will never be changed. The big table will be queried heavily, and there will be also a lot of CRUDs on it.


texts: Does the recovery model affect query speed?

texts: Would using the Full Recovery model make something like a long running DELETE operation slower than it would be under the Simple Recovery model?


texts: GetMore command executor error: DEAD in Mongodb

texts:
I am getting below error. I am clueless where I am getting this error from?

2017-10-03T01:41:31.919+0000 W QUERY   [conn197802] Could not restore RecordCursor for CollectionScan: local.oplog.rs
2017-10-03T01:41:31.919+0000 E QUERY    [conn197802] GetMore command executor error: DEAD, stats: { stage: "COLLSCAN", filter: { ts: { $gte: Timestamp 1501837654000|1 } }, nReturned: 108020905, executionTimeMillisEstimate: 2196403, works: 273399134, advanced: 108020905, needTime: 82689114, needYield: 0, saveState: 92254303, restoreState: 92254303, isEOF: 0, invalidates: 0, direction: "forward", docsExamined: 108020905 }

Though I have 413292 docs only, I am getting nReturned: 108020905.

Mongodb Version: 3.2.13
Environment: Sharded Cluster
OS: RHEL 6.7
Total Docs(in 3 shards): 413292
Docs in Oplog.rs(per shard): 799271

Could someone please look into this and let me know if already faced this kind of error.

Regards
Pravin Dwiwedi


texts: MySQL - Flush Memory after loading files

texts:
I am loading a great amount of data which are in CSV files using LOAD FILE command. It is working fine and after loading that files, it executes some data transformation...

The problem is that, after loading for example 12 files (400 MB each one), mysql process consumes almost all memory available, it is not flushing, and so, the machine slow down, and I need to restart mysql to continue to load data files.

MySQL 8.0.17

Any ideas how to solve this issue?

Added Store Procedure

BEGIN
DECLARE sequenceid INT;

DECLARE `_rollback` BOOL DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

START TRANSACTION;

INSERT INTO filenamedt (filename, filedate, fileinitialid) VALUES (CAfilename, now(), (SELECT MAX(imp_sequence_id) + 1 FROM imp_siscori_sequence));

INSERT INTO imp (select imp, ordem, CONCAT(TRIM(data),'01')) FROM temp_imp);

INSERT INTO imp_data (imp_id, unid_comerc, incoterm) (SELECT imp_id, TRIM(unidadeComercial),  TRIM(incoterm),  TRIM(natInformacao) FROM temp_imp );

INSERT INTO imp_description (imp_id, imp_description) (SELECT imp_id, TRIM(descricaoProduto) FROM temp_imp);

UPDATE imp_sequence SET imp_sequence_id  = (SELECT MAX(imp_id) + 1 FROM temp_imp_siscori);

TRUNCATE TABLE temp_imp;

SET @m = (SELECT MAX(imp_sequence_id) + 1 FROM imp_sequence);

SET @s = CONCAT('ALTER TABLE temp_imp AUTO_INCREMENT=', @m);

PREPARE stmt1 FROM @s;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

IF `_rollback` THEN

    ROLLBACK;

ELSE

    COMMIT;

END IF;


texts: Are expdp/impdp commands appropriate for daily schema sync?

texts:
I've done following comands:

expdp system/password@localdb ^
schemas=SCH directory=dumps_dir dumpfile=SCH_%date%.dmp logfile=expdpSCH_%date%.log

It runs great, without errors. But when I use command:

impdp system/password@localdb ^
schemas=SCH directory=dumps_dir dumpfile=SCH_%date%.dmp logfile=impdpSCH_%date%.log

It gave me bunch of errors that tables, sequences and other stuff already exist. So I have a question, could I use such an approach or it's wrong and I should use something else?


texts: How to pass table name and column name dynamically in Cursor

texts:
I would like to delete millions of records from lots many tables. In the below code, i'm trying to select a table with some filter condition and trying to delete using its row id. In the below, for the cursor test_cusr i'm selecting from (region) table and column (code) = 'XY'. I would like to pass the table, column and the value in dynamically. I tried with for loop and ref cursor but getting some error. Can any one help me in this.

DECLARE
CURSOR test_cur
  IS
    SELECT rowid row_id FROM region WHERE code = 'XY';
TYPE test_aat
IS
  TABLE OF ROWID INDEX BY PLS_INTEGER;
  l_test test_aat;
  limit_in NUMBER := 10000;
BEGIN
  OPEN test_cur;
  LOOP
    FETCH test_cur BULK COLLECT INTO l_test LIMIT limit_in;
    IF l_test.COUNT = 0 THEN
      EXIT;
    END IF;
    FORALL indx IN l_test.FIRST .. l_test.LAST
    DELETE FROM region WHERE rowid = l_test(indx);
    COMMIT;
  END LOOP;
  IF test_cur%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE(test_cur%ROWCOUNT);
    CLOSE test_cur;
  END IF;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  IF test_cur%ISOPEN THEN
    CLOSE test_cur;
  END IF;
  ROLLBACK;
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/


texts: MYSQL crashes on XAMPP while working with drupal

texts:
Good day,
just today morning i was using drupal to add some content, it crashed and mysql shutdown unexpectedly at xampp!!
last thing i was doing was inserting a data into a field with "auto complete entity reference"..but after inserting, it gave me an error in red: "no data exist in entity reference", next thing i did to fix my insertion, it got this error for mysql crashing!
i tried to restarted it many time, but each time it started, it doesn't respond to anything, then shuts down again.
sometimes i was hardly able to logon to phpmyadmin, check the database, (all looed ok to me) but before going deep, it crashed again!
(its been just less than 1 min time, and slow response before the crash!)
i tried writing any sql statment(i thought it has something to do with drupal cache, so i wanted to delete their data), but it hangs and eventually mysql shuts down again.
here's a screen shot of drupals' initial error:

but after trying to refresh and restart mysql at xampp, i get this error:

i looked around google and stack exchange, and found that i need to check the error log file at mysql..after long hours of determining the problem i THINK i found my problem is with a corrupt table:
(i couldnt post all lines of error reporting here cuz they're too long)
so from between the lines i found this:

InnoDB: table  "bitnami_drupal7"."field_revision_field_citizenship"iis corrupted. Please drop the table and recreate.
[Warning] InnoDB: Cannot open table bitnami_drupal7/field_revision_field_citizenship from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

i was surprised that a whole service is shutdown because a simple table, how come? and why would a table be corrupted by itself? i mean i was just minding my own business with drupal contents and adding setting and thing(not even coding, nothing), how could such a thing happen?
i found some solutions, (the one with increasing the packet size didnt work btw, cuz it happened before and it solved, but now it seems different), here are some solutions which say:
(1).

[mysqld]
innodb_force_recovery = 1

or
(2).

Delete: "ibdata1" file at "C:\xampp\mysql\data"

so, the reason im asking here is that, are those methods safe?  will they not affect my drupal tables?
it would be disastrous for me if anything goes wrong, because 1st solution says "FORCE" which sounds scary and could end up corrupting things based on what i read. (im not entirely sure though)
also, 2nd solution which is deleting that file (im not sure wht that file is or does), does that file contain any database stuff that affect my database files? or is it only for configuring purpose? i mean maybe some people are not having serious stuff in database so they tried it and worked for them.
but for me its very critical if anything bad happens to my data from those solution.
I'd like to know if those solution wont affect my data.
I'm pretty new to this database stuff, so im not sure..thats why im trying to take a cautious step to ask here.
Please guide me from your experiences.
Thanks


texts: Functional Indexes in Oracle, compared to MySQL

texts:
Hoping you can help me understand a particular problem I'm facing when converting an Oracle 12c database to MySQL 5.7.

For context, let's say we have a table that looks a bit like this:

id    firstname    lastname
---------------------------
1     John         Jones
2     tom          smith
3     ALEX         JonEs

Data can be entered into the table in any case or mixed case, there is no control at the application level or any constraints or triggers at the database level.

So for the purpose of effective querying, a functional index has been created in Oracle as follows, because we will always query the lastname field using the lower case function:

CREATE INDEX idx_table1_lastname ON schema1.table1 (LOWERCASE(lastname))

We can't do that in MySQL because functional indexes are not a thing. The same column in MySQL has been created as collation utf8-general-ci so my question is this.

If we create the same index on our MySQL database, we can only do:

CREATE INDEX idx_table1_lastname ON table1 (lastname)

Will the index be effective, given the collation of our column is case-insensitive? Or should we be putting something in place to ensure all our database writes for this field are being written in a consistent case?

Thanks


texts:
I have a list of sensors of many machines, that stores on a second base (i.e., 86400 records each day for each machine). One of the many columns is the datetime of the value (that can also be stored separately as date and time).
I'm using PostgreSQL 9.5.

Frequently, I need to find out what dates have sensor's records available for each machine.

I thought about creating a database view of distinct dates for each machine, but after searching for "materialized views" I think I'm ok with updating this list once in a while each day, as data is stored daily.

Would anyone give any advice about doing it?

The table is similar to

ID | Machine_code | Sensor_1 | Sensor_2 | Sensor3 | Datetime
1  | Drimmer      | 12       |  15.8    | 13.4    | 2016-03-03 10:00:03 GMT+1

If it helps anyhow, I'm using Django Framework in front of it.


texts: SQL Server error "Missing family sequence number 2"

texts:
I'm trying to restore a local database on my server, but I'm having problems with the backup. I did a file .bak but every time I try to load it on the server it doesn't let me.

Here's a screenshot of the error message I receive:

I read that I can use RESTORE LABELONLY to restore my file but still not working, every time I run it I get something like this:

  Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.JOSEMA\MSSQL\Backu\INNOVA-T.bak'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Msg 3013, Level 16, State 1, Line 1
  RESTORE LABELONLY is terminating abnormally.

Any help would be great. Thank you.


texts: Why do my ON UPDATE/ON DELETE rules disappear?

texts:
I'm trying to connect a couple of Mysql tables with foreign keys and ON UPDATE/DELETE rules. My rules are working on all tables except the last one (OrderContains). Why aren't my rules working?

I am using phpMyadmin to run my hand written queries and I can see in the relational view (part of phpMyadmin) of the OrderContains table that there are no connections.
I can't even find the Orders.orderNr in the list of possible connections.

Any suggestions?

Here are my table definitions:

CREATE TABLE Users   (
    userNr          INT,
    password        VARCHAR(50),
    language        CHAR(3),
    name            VARCHAR(32),
    email           VARCHAR(32),
    phone           VARCHAR(32),
    security_level  INT,
    address         VARCHAR(32),
    comment         TEXT,

    PRIMARY KEY     (userNr)
)  ENGINE=INNODB;

CREATE TABLE Customers   (
    userNr          INT,

    FOREIGN KEY (userNr)    REFERENCES  Users(userNr)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    PRIMARY KEY (userNr)
) ENGINE=INNODB;

CREATE TABLE Items   (
    itemNr          VARCHAR(32),
    revision        VARCHAR(32),
    description     TEXT,
    weight          FLOAT,
    added           DATE,

    PRIMARY KEY(itemNr, revision)
) ENGINE=INNODB;

CREATE TABLE Orders  (
    customer        INT,
    orderNr         INT,
    title           VARCHAR(32),
    added           DATE,
    status          CHAR(1),
    notes           TEXT,

    PRIMARY KEY     (customer, orderNr),
    FOREIGN KEY     (customer)      REFERENCES  Customers(userNr)
    ON UPDATE CASCADE
    ON DELETE CASCADE

) ENGINE=INNODB;

CREATE TABLE OrderContains   (
    customer        INT,
    orderNr         INT,
    itemNr          VARCHAR(32),
    revision        VARCHAR(32),
    quantity        INT,

    PRIMARY KEY     (customer, orderNr, itemNr, revision),
    FOREIGN KEY     (customer, orderNr) REFERENCES  Orders(customer, orderNr)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    FOREIGN KEY     (itemNr, revision)  REFERENCES  Items(itemNr, revision)
    ON UPDATE CASCADE
    ON DELETE CASCADE

) ENGINE=INNODB;

I made the following query in phpMyAdmin
    SHOW CREATE TABLE OrderContains

and I got this result:

CREATE TABLE `OrderContains` (
 `customer` int(11) NOT NULL DEFAULT '0',
 `orderNr` int(11) NOT NULL DEFAULT '0',
 `itemNr` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `revision` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `quantity` int(11) DEFAULT NULL,
 PRIMARY KEY (`customer`,`orderNr`,`itemNr`,`revision`),
 KEY `itemNr` (`itemNr`,`revision`),
 CONSTRAINT `OrderContains_ibfk_1` FOREIGN KEY (`customer`, `orderNr`)     REFERENCES `Orders` (`customer`, `orderNr`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `OrderContains_ibfk_2` FOREIGN KEY (`itemNr`, `revision`)     REFERENCES `Items` (`itemNr`, `revision`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I've also tried restarting the server.


texts: I cant do a select by one of its parameters even if it exists in the database. Encoding issues? PostgreSQL

texts:
I have a postgresql database snapshot with the following structure

id (integer / autoincrement)
address (varchar)
balance (integer)
datetime (date)

    CREATE TABLE public.snapshots
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    address character varying COLLATE pg_catalog."default",
    balance bigint,
    datetime date,
    CONSTRAINT snapshots_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.snapshots
    OWNER to postgres;

But it has some weird issues, for example I have this data
Some example of data is:
12531   "0x7a250d5630B4cF539739dF2C5dAcb4c659F2488D"    34567733    "2020-09-01"
12532   "0x8cfb1D4269f0daa003CDEa567aC8f76c0647764a"    34547733    "2020-09-01"
12533   "0xdAC17F958D2ee523a2206206994597C13D831ec7"    34512333    "2020-09-01"
12534   "0x94d863173EE77439E4292284fF13fAD54b3BA182"    34511133    "2020-09-01"
12535   "0x7a250d5630B4cF539739dF2C5dAcb4c659F2488D"    34555733    "2020-09-01"
12536   "0x9287130ba603359E9621ae89c4164613BF239Fa2"    34327733    "2020-09-01"

Well the problem is now that I do this select
SELECT * FROM snapshots where address = '0x9287130ba603359E9621ae89c4164613BF239Fa2'

0 returns
But if i do
SELECT id, address, balance, datetime
FROM public.snapshots where datetime = '2020-09-01' and balance = 34327733;

It does return the result
What is going on? It works with some addresses, but doesn't with others


texts: How can I Automate MongoDB schema design from existing MySQL Database and Logs?

texts:
I am working on the project which will automatically generate sample MongoDB schema analyzing existing MySQL database and logs. I am looking for help regarding the below questions. Please, share your thoughts,some links or references.

1) What are the MySQL parameters which can help me to automate the process?

2) What are the criteria to design the idle MongoDB database? What I should keep in mind while designing MongoDB database? Example MongoDB schema for any MySQL database would be great help.

3) How denormalizing the database will help?

4) Is there any existing opensource application/tool which can help automation process?


texts: New Table vs Adding Columns To Existing Table

texts:
I have a table, products with the following columns: id, name
There is another table, countries with these columns: id, name, code
The products have different prices across different countries (same currency is used).

I'm torn between creating a new table to store this, which would have the following columns: product, country, price
and adding new colums to the products table; price_uk, price_kenya, price_sweden, etc, for the different countries.
Which would be the better option, and why?


texts: Storage pool configuration for SQL Server

texts:
We are in the middle of building a new SQL Server 2017 always-on cluster in Azure VM. The data disk layout of VM are as follows

TempDb      :   2    Azure Disk Read Cache
TempDBLog   :   1    Azure Disk No Cache
UserDB      :   3  Azure Disk   Read Cache
UserDBLog   :   2  Azure Disk   No Cache

My plan is to create a multiple storage pool, i.e TempDB storage pool with 2 TempDB disk, UserDB storage pool with 3 User db Azure Disk.

However, according to the Microsoft's documentation on SQL Server in Azure VMs:

  If you are using a disk striping technique, such as Storage Spaces,
  you achieve optimal performance by having two pools, one for the log
  file(s) and the other for the data files. However, if you plan to use
  SQL Server Failover Cluster Instances (FCI), you must configure one
  pool.

Is this applicable to Always-On or just applicable to shared storage FCI. Please guide me, Please share good articles/docs for reference.

Thanks


texts: What happens exactly when a chunk gets migrated?

texts:
So, this is what MongoDB states:

  Independent of the secondaryThrottle setting, certain phases of the
  chunk migration have the following replication policy:

  MongoDB briefly pauses all application reads and writes to the
  collection being migrated, on the source shard, before updating the
  config servers with the new location for the chunk, and resumes the
  application reads and writes after the update. The chunk move requires
  all writes to be acknowledged by majority of the members of the
  replica set both before and after committing the chunk move to config
  servers.
  When an outgoing chunk migration finishes and cleanup occurs,
  all writes must be replicated to a majority of servers before further
  cleanup (from other outgoing migrations) or new incoming migrations
  can proceed.

Getting a real example: the first option "All queries on documents with productId value ranging between 18684 and 27851 will be routed to shard0000" would have been correct if the upper bound was 27850 instead?

If I understood it right, when I start the migration, if I try to run a query against documents ranging between the productId values 18684-27850, MongoDB already understands that it won't have to ask to shard0003 anymore, but instead it will have to ask for those documents to the NEW destination shard and thus redirect requests to shard0000.

So, I haven't actually tryed it, but I guess what you see is that, when you run the db.collection.find(), the cmd will "freeze" and after MongoDB successfully completed the chunk migration, it will retrieve the result set, is that right?


texts: How to insert nested JSON arrays into a table?

texts:
I'm trying to insert a JSON array that came from another JSON array (array of arrays) into a table using json_populate_recordset(), but I'm receiving the following error:

SQL Error [22023]: ERROR: argument of json_populate_recordset must be an array of objects

Here's an example of what I'm trying to do:

DO $$
DECLARE
    j json;
BEGIN
    j := '[[{"a":1,"b":1.23},{"a":2,"b":2.34}],
           [{"a":2,"b":1.23},{"a":3,"b":2.34}],
           [{"a":3,"b":1.23},{"a":4,"b":2.34}]]'::json;

    CREATE TEMPORARY TABLE testj (j json);

    INSERT INTO testj
    SELECT * FROM json_populate_recordset(null::testj, j);

    DROP TABLE testj;
END
$$;

The final table should be something like this:

                 j                  |
------------------------------------|
[{"a":1,"b":1.23},{"a":2,"b":2.34}] |
[{"a":1,"b":1.23},{"a":2,"b":2.34}] |
[{"a":1,"b":1.23},{"a":2,"b":2.34}] |

I'm using a PostgreSQL 9.5 database. What am I doing wrong?


texts: in Mariadb - Can you cache the indexes and data for Aria Storage engine tables?

texts:
I have a table that is a read-only table (real-time view), I used Aria based on the recommendation for read-heavy workload.
Based on this What are the main differences between InnoDB and MyISAM?
When it comes to cache is Aria like MyISAM or like InnoDB? meaning, Can I cache only indexes or can I also cache the data? (under the assumption I have large enough RAM)


texts:
I run a small website which uses a Postgres database. There are regular back ups based on the amount of traffic we have every few days but there was a recent surge of activity just before a power cut which caused 24 user accounts (of our website) to become corrupted.

I followed the instructions set forth on the Postgres site for corruption, made a file system backup, repaired the ext file system, and did what I could for the database.

There are three columns in a single table however that are vital for user authentication and these columns remain corrupted.

Upon trying to select data from one of these columns, the following error is returned:

ERROR:  missing chunk number 0 for toast value 16935 in pg_toast_2619

Attempting pg_dump returns:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast value 11651 in pg_toast_2619
pg_dump: The command was: SELECT tableoid, oid, proname, prolang, pronargs, proargtypes, prorettype, proacl, pronamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = proowner) AS rolname FROM pg_proc p WHERE NOT proisagg AND (pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'))

A VACUUM ANALYSE of the table was attempted resulting in the following:

PANIC:  corrupted item pointer: offset = 8112, size = 1056
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

I also attempted to $ reindexdb dbname but this also failed when reaching the table with the corrupt columns:

reindexdb: reindexing of database "dbname" failed: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

I'm quite at a loss right now, I've been searching and reviewing the documentation because I honestly believe those columns are recoverable, or at least some of the data in them is.

It only affects 24 rows in a single table, with three columns, but due to the small scale the website currently operates on, this was a really nice increase in users which I'd prefer to recover if I could.

If all else fails, the email data isn't corrupt so we could issue them with password reset emails but we'd prefer not to do this if possible.

UPDATE
I have managed to locate the missing data physically on the system in the xlog files. My plan of action is recovering from the log file via a script matching the missing data with the known usernames, restoring the database from the backup from a few days ago, and building an insert command to insert the repaired records.

This seems a much better option than trying to repair the database or recover via psql.


texts: Would you use MongoDB in high-transaction environment?

texts:
using other words:

does the one-write-lock-per-database policy (it's not a "bug" but a "feature" by design) affect contention making its usage in certain kind of workloads less then optimal?

if not how do you maintain that kind of workload?

would you use an async-write-queue  at the application level? (roll-backing would be difficult with such a structure though)


texts: A SQL query optimzation issue with ORDER BY

texts:
An SQL query optimzation issue with ORDER BY

Current status
I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables 'Game' and 'GamePosition'. GamePosition has an index on the fen column and Game has an index on white_elo. I currently have 170471 games and 14813401 positions. I'm running mysql 5.7.28.

Object
I'm trying to fetch top rated games based on elo rating of the players. I've simplified my query a bit here, but the point and performance problem is the same.

SELECT Game.id
FROM Game
JOIN GamePosition ON Game.id = game_id
WHERE fen = 'rnbqkbnr/pppppppp/8/8/3P4/8/PPP1PPPP/RNBQKBNR'
ORDER BY white_elo DESC
LIMIT 10

This query tends to be a bit slow (1.2 s) if I get a lot of results (typically the first move gives 67k+ results). Since I plan to exand the database 10x at least, I want to optimize my query. I've run a EXPLAIN which shows that it needs to do a filesort before finding the top rated games on all the results. This seems to be the issue. Removing the ORDER BY from the query makes it superfast (0.0008 s).

Any ideas if it's possible to optimize the query, or if I could store the data differently?

UPDATE:

       Table: GamePosition
Create Table: CREATE TABLE `GamePosition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `fen` varchar(100) NOT NULL,
  `move_color` char(1) NOT NULL,
  `castling_rights` varchar(4) NOT NULL,
  `en_passant_square` varchar(2) NOT NULL,
  `half_move` smallint(6) NOT NULL,
  `full_move` smallint(6) NOT NULL,
  `timestamp` time DEFAULT NULL COMMENT 'time in video',
  `move` varchar(10) DEFAULT NULL,
  `from_square` varchar(4) DEFAULT NULL,
  `to_square` varchar(4) DEFAULT NULL,
  `parent_position` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `position_ibfk_1` (`game_id`),
  KEY `fen` (`fen`),
  KEY `parent_position` (`parent_position`),
  CONSTRAINT `game_key` FOREIGN KEY (`game_id`) REFERENCES `Game` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

       Table: Game
Create Table: CREATE TABLE `Game` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pgn` text NOT NULL,
  `white_id` int(11) NOT NULL,
  `black_id` int(11) NOT NULL,
  `white_elo` int(11) DEFAULT NULL,
  `black_elo` int(11) DEFAULT NULL,
  `result` tinyint(4) DEFAULT NULL COMMENT '0=white, 1=draw 2=black',
  `date` date DEFAULT NULL,
  `event_id` int(11) NOT NULL,
  `eco` varchar(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `event_key` (`event_id`),
  KEY `black_key` (`black_id`),
  KEY `white_key` (`white_id`),
  CONSTRAINT `black_key` FOREIGN KEY (`black_id`) REFERENCES `Player` (`id`),
  CONSTRAINT `event_key` FOREIGN KEY (`event_id`) REFERENCES `Event` (`id`),
  CONSTRAINT `white_key` FOREIGN KEY (`white_id`) REFERENCES `Player` (`id`)
)

       Table: Player
Create Table: CREATE TABLE `Player` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`id`)
)

Actual Query

SELECT GamePosition.id AS position_id, Game.id AS id,
       white.name AS white, black.name AS black, Game.id,
       white_elo, black_elo, result, date
    FROM Game
    JOIN GamePosition ON Game.id = game_id
    JOIN Player white ON white.id = white_id
    JOIN Player black ON black.id = black_id
    WHERE fen = '$fen'
    ORDER BY white_elo+black_elo DESC
    LIMIT $limit
    OFFSET $offset";

Kind Regards,
Bjorn


texts:
The Search feature on my website is based on SQL Server's Full Text Search (I insert everything relevant to a single table and this table is in the FTS Catalog).

We don't have money to buy a thesaurus but we do want the search to include other similar words too. The Words API provides synonyms for each word sent to it. From tests I've done it's usually 3 synonyms for a word (even if it's more, 3 is enough at this stage).

We were thinking of the following:

Send the text to SQL to get tokens
Call words API on each token to get synonyms
Concatenate synonyms to the string
Query using SQL using FREETEXT
Highlight results using a merged list (tokens + synonyms)

Will that approach hold water? Has anyone implemented something similar before? Is it better to replace the tokens with the synonyms and query SQL server separately the merge the results?


texts:
Imagine I have a table with the following dependency:

  (Name, BoughtFrom, TimeBought) --> Cost

Where the three attributes in bold form a composite primary key for the table. Now then, I want to link (form a relationship) from another table to this one. How do I do that? If I had an ID column I'd know what to do, but I've never come across a scenario like this.

Would I have to add all three columns (Name, BoughtFrom, TimeBought) to the other table? Or is there another way?


texts:
In a Service Broker queue, when one message is proceed over time, we are executing a stored procedure which is performing some heavy operations using temporary tables.
I guess that the sp_WhoIsActive is showing summarized values for tempdb allocations and CPU usage, as the queue is using one session.
The question is, are the allocations released when the current stored procedure completes?


texts: Can I move rows between partitions by updating the partition key?

texts:
I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this.

The question:
Can you move rows of data within a partitioned table from one partition to another by simply updating the partition column so that it crosses the partition boundary?

For example, if I have a table that has a partition key:

CREATE TABLE SampleTable
(
    SampleID INT PRIMARY KEY,
    SampleResults VARCHAR(100) NOT NULL,
)

With the partition function that maps to the primary key:

CREATE PARTITION FUNCTION MyPartitionFunc (INT) AS
RANGE LEFT FOR VALUES (10000, 20000);

Can I move a row from the first partition to the third partition by changing the SampleID from 1 to (say) 500,000?

Note: I'm tagging this as both sql server 2005 and 2008, since they both support partitioning.  Do they handle it differently?


texts: Why does adding ORDER BY makes the select much faster?

texts:
First query, 900ms:

SELECT * FROM geoip.isp_ipv4
WHERE INET_ATON(?) BETWEEN start_ip AND last_ip LIMIT 1

Second query, 0.5ms:

SELECT * FROM geoip.isp_ipv4
WHERE INET_ATON(?) BETWEEN start_ip AND last_ip ORDER BY start_ip DESC LIMIT 1

I wonder why there is so much performance difference.

There could be only one result for a given ip, so the sorting doesn't change the returned row in any way...But why is it so faster?

It's the maxmind geoip db (1.59M rows), this is my table structure:

CREATE TABLE `isp_ipv4` (
  `start_ip` int(10) unsigned NOT NULL,
  `last_ip` int(10) unsigned NOT NULL,
  `asn` mediumint(8) unsigned DEFAULT NULL,
  `isp` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `osn` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`start_ip`),
  KEY `last_ip` (`last_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I'm on MySQL 5.7.17, the same happens in both dev (Windows 10) and production (Ubuntu trusty)

The EXPLAINs are identical:

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'isp_ipv4', NULL, 'range', 'PRIMARY,last_ip', 'PRIMARY', '4', NULL, '795214', '50.00', 'Using where'
'1', 'SIMPLE', 'isp_ipv4', NULL, 'range', 'PRIMARY,last_ip', 'PRIMARY', '4', NULL, '795214', '50.00', 'Using where'


texts: Restore .bkp file of different version oracle

texts:
I have 12.1 Oracle version .bkp file. Can I restore it in 11.2 Oracle XE?
I have used below command:

 RMAN> restore controlfile from
2> '/home/db_dump/201101.BKP';

And now getting below error:

ORA-19690: backup piece release 12.1.0.0.0 incompatible with Oracle release 11.2.0.2.0

Please help me to figure out this or give some conclusion.


texts:
My Drupal site is on a shared host where I cannot modify the my.cnf configurations but the database seems to be performing poorly.  Here are some of the flagged issues that I can see...

In phpMyAdmin, the following are flagged RED in under the Status tab:

Slow_queries    613.2 k
Innodb_buffer_pool_reads    15.9 M
Innodb_log_waits    10
Innodb_row_lock_time_avg    740
Innodb_row_lock_time_max    45.1 k
Innodb_row_lock_waits   296
Handler_read_rnd    14.7 G
Handler_read_rnd_next   1.2 T
Qcache_lowmem_prunes    142.2 M
Slow_launch_threads 17
Created_tmp_disk_tables 31.7 M
Select_full_join    1.2 M
Select_range_check  212.8 k
Sort_merge_passes   174.8 k
Opened_tables   36.5 M
Table_locks_waited  512.5 k

Running http://day32.com/MySQL/tuning-primer.sh also flagged these issues:

QUERY CACHE
Query cache is enabled
Current query_cache_size = 512 M
Current query_cache_used = 488 M
Current query_cache_limit = 32 M
Current Query cache Memory fill ratio = 95.38 %
Current query_cache_min_res_unit = 4 K
However, 136119288 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size

JOINS
Current join_buffer_size = 4.00 M
You have had 1117666 queries where a join could not use an index properly
You have had 207168 joins without keys that check for key usage after each row
join_buffer_size >= 4 M
This is not advised

TABLE CACHE
Current table_open_cache = 2048 tables
Current table_definition_cache = 256 tables
You have a total of 200 tables
You have 2048 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 256 M
Of 53194631 temp tables, 36% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables

I'm not a DBA and have very little experience in DB optimization.  I would really appreciate some help.  What can I do to improve the database performance if I can't modify my.cnf configurations accordingly?

EDIT:
The tables have indexes, but perhaps they aren't optimal?  My database knowledge is limited so it's hard for me to say.  Drupal generates TONS of queries to the database.  I did check the slow query logs and for one of the queries, here is the result of EXPLAIN:

EXPLAIN SELECT menu_links.link_path AS link_path, menu_links.mlid AS mlid, menu_links.router_path AS router_path, menu_links.updated AS updated
FROM
dr_menu_links menu_links
WHERE ( (updated = '1') OR( (router_path NOT IN  ('rss.xml', 'node', 'checkout', 'import', 'image_captcha', 'my-favourites', 'my-orders', 'print', 'search')) AND (external = '0') AND (customized = '1') ))

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | menu_links | ALL  | router_path   | NULL | NULL    | NULL |  757 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

Other queries in the slow query logs include commands like show databases!


texts:
I've been struggling with this model for weeks, going in circles from one type of solution to another. I'd like some advice on the best approach.

One or more organizations can sponsor an event or project; projects are a series of events. Not all events are part of projects but projects always have events. Leaders of any of the sponsoring organizations can create and update an event or a project (and the events in that project). A project has leaders, who can also create and update events for that project. Events may have leaders who can also update that event. Does this make for too little accountability and too many people who can edit and add organizations, events, and projects?

For the output end, I need to display for the leader, each event, project, and organization they can update and then when they do update, make sure they have the right to update that particular event, project, or organization.

As I see it I have to have Event, Project, Organization, and Leader tables. I also need to store which organizations sponsor which events and which projects. Here is where I go round and round. Is it better to have two tables or one:

two tables:

project-sponsor table (ID, organization_id, project_id) AND
event-sponsor table (ID, organization_id, event_id)

one table with entity_type = 'project', 'event', or 'org':

sponsor (ID, organization_id, entity_type, entity_id) OR
sponsor (ID, organization_id, event_id, project_id)

I also need to store leaders and their roles in organizations, projects, and events. I face the same quandary. Is it better to have three tables or one:

three tables:

org_leader(ID, leader_id, organization_id, role, title, term...) AND
project_leader(ID, leader_id, organization_id, role, title, term...) AND
event_leader(ID, leader_id, organization_id, role, title, term...)

one table  with entity_type = 'project', 'event', or 'org':

role(ID, leader_id, entity_type, entity_id, role, title, term...) OR
role(ID, leader_id, event_id, project_id, organization_id, role, title, term...)

Which choices would make it easier to find out who can edit a particular event (from its project, if any, its sponsoring organization) and find out which events, projects, and organizations a particular leader can edit?

I've attached erd's for the various choices.

Any advice and strategies would be greatly appreciated!


texts: Why can't I create a index using column definition in MySQL?

texts:
Consider the following create table syntax

CREATE TABLE test1 (f1 int primary key, f2 int, unique key(f2));

I can also write the above as follows

CREATE TABLE test1 (f1 int primary key, f2 int unique key);

Why can't I do the same for indexes?

CREATE TABLE test1 (f1 int primary key, f2 int, index(f2)); -- is valid
CREATE TABLE test1 (f1 int primary key, f2 int index); -- is invalid

I know that the later one is wrong as per syntax but can you detail the reasons on why MySQL chose to not allow index in column definition? While it's perfectly fine for unique keys what would be the problem for indexes?


texts: How can I enable the reports menu in SQL databases

texts:
according to this picture the reports menu is disabled.

how can I enable this menu. I thought that maybe it depends on the version of my SQL server.so this is my SQL server version:

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)
Enterprise Edition (64-bit) on Windows 8.1 Pro 6.3 <X64> (Build 9600: )


texts: How to organize a single query to several sql servers?

texts:
I need to make an inner join sql query to several SQL Servers (one is 2000 and another is 2008 R2). The query is like the following:

SELECT
   k.ID, k.NAME, k.BIRTH, l.PROFILEID
FROM
   CUSTOMERS..REGISTRY k
INNER JOIN
   SECURITY..USERS l ON K.ID = l.ID

The CUSTOMERS..REGISTRY is on the 2008 R2 and SECURITY..USERS is on the 2000 servers. The servers are situated in different networks, connected through internet. Is there any way to organize such query?


texts: Is it possible today to replicate 30 TB from multiple databases to PostgreSQL close to real-time?

texts:
If you had 20 databases spread across 20 servers, could you replicate them all into a single PostgreSQL instance? And can this be done on a server costing less than $100,000?

The databases total 30 TB on disk. There are ~30,000 tables. The maximum number of rows in a single table is 1.2 billion. The number of rows changed per second is modest (100s or less).

Some of the servers run PostgreSQL, some MySQL. The versions are different, but they're relatively current (PostgreSQL >= 9.1, MySQL >= 5.6).


texts: Find the Foreign Keys Associated with a Given Primary Key

texts:
I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
WHERE EXISTS (
    SELECT tc.*
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
    WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'
        AND tc.TABLE_NAME = 'MyTable'
        /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO

but for a PK returned from such a query how do I establish the associated FK (assuming there is one)?

I know you can also get the referenced tables via:

SELECT CONSTRAINT_NAME = name,
       FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id),
       FOREIGN_TABLE = OBJECT_NAME(parent_object_id),
       REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id),
       REFERENCED_TABLE = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO

but I am struggling now to get the explicit column references.

I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. I need all of the constraint information for any given column (if any).

I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs.

Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both.


texts: Copy/move data from one cassandra server to another

texts:
I'm in the midst of replacing a kubernetes stack (to more specific: moving it from a locally hosted cluster to an AKS cluster in Azure), and as a result have to re-create all of my services, including a Cassandra service. All of the data that is contained in the existing Cassandra has to be copied into a new one. However, I'm having a great deal of trouble finding a way to actually do this. With SQL databases it's trivial; with postgres for example you can just use pg_dump on the old database and just echo out the file into the new db.

However there seems to be no equivalent method for Cassandra and googling hasn't really given me a solution. It seems COPY FROM and COPY TO would require manually copying every single table which is ridiculous. Also should be noted that if possible, I really want a solution that does not require directly accessing the docker containers (ie. some solution that I can run from a remote box that has a connection to both cassandra's), because getting direct container level access in AKS can be a huge PITA.


texts: database schema design for school system

texts:
I am trying to create a database to store information about a school.

I need to store each department in the school. Each department then has a list of teachers. Each teacher has a list of classes and each class has a list of students.

The department name will be unique. However, teachers may span across departments. Another thing to note is that if teacher A teaches a class called 'Maths' and teacher B also teaches a class called 'Maths' then these should be different entities in the database (I think). For example, I may want to get a list of students that take the class 'Math' with 'teacher A' only.

So far I have this:

departments(department_id, department_name)
teachers(teacher_id, teacher_name);
departments_and_teachers(department_and_teachers_id, department_id (FK), teacher_id (FK))
student(student_id, student_name)
teachers_and_students(department_and_teachers_id, student_id);

which I think is good database design? However, it doesn't include any information about the 'classes' and I'm not sure what the best way of altering the schema would be. For each department and teacher combo I need to add a list of classes that are taught. Is it plausible to do something like this:

classes(class_id, class_name)
departments_teachers_and_classes(dtc_id, department_and_teachers_id (FK), class_id(FK));
department_teachers_classes_and_students(student_id, dtc_id)

EDIT - new suggestion based on answer received

departments(department_id, department_name)
teachers(teacher_id, teacher_name)
classes(class_id, class_name, teacher_id (PK), department_id (PK))
student(student_id, student_name);
classStudents(student_id (PK), class_id (PK))


texts: How should I model a database structure to store information about words?

texts:
I have an application where the user can click on any word in a text, which will bring back related information about the base word and the specific inflection (word form) of the word clicked. I've tried representing this in a multitude of ways, but now that I'm close to launch, I'm worried as hell about the best way to represent it (this is my first ever project with programming, so I have no idea what I'm in for).
Considerations
The way I had originally set this up was that there was a base word table that contained the base word, part of speech, and then an array of word forms for each base word.
I then learned that this was a bad idea in terms of normalization, and I also realized that I needed to store specific inflection information for the words (mood, tense, person, etc.), so I rearranged it.
In the second iteration, I had three tables, a base_word table that had the base_word and the part of speech, a word table that had the word and its inflection information, and then a “join” table between them that contained the base_word_id and the word_id.
However, I've been re-thinking this setup, because semantically speaking, a base word is also a word, just a specific type of word. So in this case, I feel like a “self-join” table might be most appropriate, although I could be completely overthinking this. In the “self-join” table, there would be an is_baseword boolean attribute, which would theoretically allow all the inflection columns to store NULL marks if it was set to true. Is this a better idea?

Note: I found these two similar questions which didn't seem to completely answer my questions, so I'm posting my twist on these here:

Table design for a dictionary that can have words with many different spellings

How can I represent a list of words and related words in a relational structure?


texts: I need to do periodic commits in my purge function

texts:
I wrote a purge function that runs in Postgres 9.6. The function goes through all of the tables in our application starting from the bottom most child table and ending with the top most parent table and purges based on date or client. When we run this in production and beta, there are other processes happening at the same time and the database is getting blocking locks based on my uncommitted deletes. One possible solution would be to do a commit every thousand or so rows. But I am new to Postgres and I can't get Postgres 9.6 to do commits within a loop. It's possible that Postgres 9.6 won't do this and maybe Postgres 12 will. We are moving to Postgres 12.
Is there anyway to get the commits to work in Postgres 9.6? I included some test code so that you can test the commits on your own. My actual code is far more complicated.
Thanks
\timing

DROP TABLE IF EXISTS _tmp_test_transactions_table;
CREATE TABLE _tmp_test_transactions_table ( pkey INTEGER );

create or replace function _tmp_test_transactions ( p_number_of_rows INTEGER )
returns int language plpgsql
as
$fun$
DECLARE
   v_counter     INTEGER := 0;
   v_final_count INTEGER := 0;
BEGIN
   START TRANSACTION ISOLATION LEVEL READ COMMITTED;

   LOOP
      v_counter := v_counter + 1;
      EXIT WHEN v_counter > p_number_of_rows;

      IF MOD( v_counter, 10 ) = 0
      THEN
         COMMIT;
         START TRANSACTION ISOLATION LEVEL READ COMMITTED;
      END IF;
   END LOOP;

   SELECT COUNT(*)
     INTO v_final_count
     FROM _tmp_test_transactions_table;

   RAISE NOTICE 'Inserted % rows in the _tmp_test_transactions_table table', v_final_count;
   COMMIT;
END
$fun$;

SELECT _tmp_test_transactions( 100 );
psql:bbyrd_test_transactions.sql:36: ERROR:  unsupported transaction command in PL/pgSQL
CONTEXT:  PL/pgSQL function _tmp_test_transactions(integer) line 6 at SQL statement
Time: 0.473 ms


texts: Add New Columns that Repeat Min and Max Dates

texts:
I have a table that has a column for the Line # and a column for the date that Line # was invoiced for.  I want to create two new columns: one column that repeats the Min date for the Line # and another column that repeats the Max date for the Line #.

Here is the original table:

Here is what I want the result to be:


texts: ORACLE MULTIPLE TABLE INDEXES ON GROUP BY

texts:
I have a VIEW with multiple tables, it has a group by statement containing multiple columns from each table. The problem is access to the view takes 4,5 seconds and ideally should take less than 1.

When I run the Query Plan, it seems SORT(GROUP BY) is the most expensive statement. I tried to create indexes on the columns present in the group by statement but oracle doesnt want to use them.

What could be another solution?


texts: PostGIS: how to retrieve a report of costs by decades from a table

texts:
I have a table with a list of 'films' and, for each film, information about the 'company' that produced, the 'cost' of the production and the 'year' of production. I want to consult the 'total_cost' that each 'company' spent every 'decade' from the 1940s to the 1990s, so the information retrieved should be a table with the following columns:

'company'
'sum_cost_1940s'
'sum_cost_1950s'
'sum_cost_1960s'
'sum_cost_1970s'
'sum_cost_1980s'
'sum_cost_1990s'
'total_sum'

I´ve been able to obtain a new table all the information in three columns ('company', 'decade', 'sum_total_decade'), with the next consult:

SELECT company, sum(cost) AS total_cost_by_decade, trunc(year,-1) AS decada   FROM data_table GROUP BY company, decada ORDER BY company, decada ASC

And I've benn able to obtain a new table with the columns 'company', 'sum_cost_1950s', 'total_sum', using the next consultation:

SELECT base.company AS company,
       decada5.sum_cost AS sum_cost_1950s,
       base.total_sum
FROM   (SELECT company,
               sum(cost) AS total_sum
       FROM data_table
       GROUP BY company
       ORDER BY company ASC) base,
       (SELECT company,
               sum(cost) AS sum_cost
       FROM data_table
       WHERE trunc(ano,-1) = '1950'
       GROUP BY company ORDER BY company ASC) decada5
WHERE base.company = decada5.company

But I wans't able to increase the table, with only one SELECT, with the other decades. How can I to it?


texts: mysqladmin debug writes out Current locks but PROCESSLIST and INNODB STATUS don't show any

texts:
I'm using the 1.1.8 mysql-cacti-templates to gather metrics on MySQL 5.0.77 (CentOS 5.7).

Both the InnoDB Current Lock Waits and InnoDB Lock Structures graphs are charting with Cur[rent] values but there are no Locked Transactions charting in the InnoDB Active/Locked Transactions graph. I also checked SHOW PROCESSLIST and SHOW INNODB STATUS and there are no locks.

However, if I do a mysqladmin debug it does print out a long list (dozens) of Current locks to the error log, i.e.:

lock: 0x1e6a21b0:

lock: 0x1e7cd750:

lock: 0x1e5bdcb0:

lock: 0x1e839180:

lock: 0x1e821870:

lock: 0x1e737840:

lock: 0x1e6c5590:

lock: 0x1e5b8fa0:

[snip]

How can I determine what is causing these locks if they don't show up in a SHOW PROCESSLIST or a SHOW INNODB STATUS? FWIW, I also tried the InnoDB Lock Monitor but it too did not show any locks.

Please advise.

Thanks!


texts: What's the best database design for implementing categories and tags?

texts:
For example, a news website might have "categories" such as Politics, Business, Entertainment, Sports, Lifestyle, Tech, Science, Health, ... etc. Each article belongs to only one category, but can have multiple "tags". Tags are just like hashtags we see on social media and can be very detailed.

This StackOverflow question shows the best approach to design a database for "tags". What if I need to add "categories"?

I'm not sure if it's okay to simply record each article's category in its table row. Maybe adding a column named "Category"? If so, is the performance still good when visitors want to search, say, all articles which belong to "Business" category and have "Facebook" tag?


texts: SQL Group by MAX then get the MIN of all the groups

texts:
*edit: I'm using SQL Server 2012.

I have a table that looks like this:

id     LogOutTime
------------------------------
8563   2017-11-21 09:21:28.330
7961   2017-11-21 09:22:40.627
7961   2017-11-21 09:26:48.507
8563   2017-11-21 09:29:05.977

I want to get this the latest LogOutTime for each id. Then I want to get the oldest LogOutTime out of that group.

This is the record I want:

id     LogOutTime
------------------------------
7961   2017-11-21 09:26:48.507

This gives me the latest LogOutTime for each group:

SELECT MAX(LogOutTime) AS MaxLogOut,
       id
FROM table1
GROUP BY id

This gives me the date that I want:

SELECT MIN(table2.MaxLogout)
FROM (SELECT MAX(LogOutTime) AS MaxLogOut,
             id
      FROM table1
      GROUP BY id) AS table2

I need to get the ID and I'm not sure how to go about it and haven't found an example that is like mine.


texts: Update row based on match to previous row

texts:
I have the following table:

Declare @m_TestTable table
(
RowID                  int,
OrderNo                nvarchar(10),
DetailID               int,
FirstUniqueDetailEntry int
)

Insert into @m_TestTable Values (1,'Order1',1,1)
Insert into @m_TestTable Values (2,'Order1',1,1)
Insert into @m_TestTable Values (3,'Order2',10,1)
Insert into @m_TestTable Values (4,'Order2',10,1)
Insert into @m_TestTable Values (5,'Order2',15,1)
Insert into @m_TestTable Values (6,'Order3',20,1)
Insert into @m_TestTable Values (7,'Order3',20,1)
Insert into @m_TestTable Values (8,'Order3',30,1)

The requirement I am battling with is to update the FirstUniqueDetailEntry column to be 0 if the OrderNo and DetailID are the same as the previous OrderNo and DetailID, resulting in:

'Order1',1,1
'Order1',1,0
'Order2',10,1
'Order2',10,0
'Order2',15,1
'Order3',20,1
'Order3',20,0
'Order3',30,1

Attempt:

Update @m_TestTable
Set FirstUniqueDetailEntry  = 0
where
    [OrderNo] = (Select [OrderNo] from @m_TestTable t where t.RowID =  RowID -1)
and [DetailID] = (Select [DetailID] from @m_TestTable t where t.RowID =  RowID -1)


texts: Is there a graceful or safe restart for mysql like for apache httpd?

texts: I would like to restart mysql gracefully just like httpd where threads are served before restart. I would dislike queries breaking.


texts: Just wondering if there is any gotchas with changing the owner of a sql server replication job.  The job owner is currently a domain account, but that person is leaving the company, so the account is soon to be disabled.  I am planning on setting the owner to SA, but wanted to check about the replication jobs first.


texts: How do you monitor slow query in AWS RDS Postgres

texts:
My RDS Postgres has 5 read replica and it's the maximum number of read replica and cannot scale out by increasing read replica. In this situation, some of the read replicas occur too much high CPU utilization.

I think one of the solution against it is query tunining. But we cannot login RDS postgres with ssh connect, so to read Postgres Error log we need to download and analyze it. This way is too inefficient and very depend on who do this analysis work.

So I want to build up the system of slow query analysis in RDS PostgreSQL environment. And I want to hear what sort of approach or what middleware is recommend.

My plan is
- Using RDS REST API, download db log file every hour to local storage.
( https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/RESTReference.html )
- Using fluend, build up log analysis system


texts: How do I map a SQL Server login to a user that already exists in a read-only database?

texts:
One of our database users complains that they can't select from one of the databases in this particualr instance while they have no problems in accessing others in this very same instance. The one that they have access problems is shown as "Standby / Read-Only". The others are just normal.

So, I checked the Security -> Logins for the given principal and looked at the principal's properties to see if there were any user mappings to the read-only database for this login. There were no such mappings for that particular db while there were mappings for other databases and all with "db_datareader" role membership. Initially, I wanted to add a user mapping for this db as well; However, it failed with the below message:

  "Failed to update database X because the database is read-only"

Then, I checked the read-only database and found that a user exists in there with the same name as the account that had this issue. It also had "db_datareader" role membership. So, I guess I just need to link the two; however, I don't know how. Please help me! Thanks.


texts: Grant access to only one key in azure keyvault

texts: There are multiple keys and secrets in azure key vault . I need to grant a user access to only one key and secret in azure portal. Is that possible?


texts: Getting Postgres CREATE TABLE statements

texts:
I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me over 800 lines of output for the 9 tables. For example, part of the output creating the first table is

--
-- Name: popresearch_question; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE popresearch_question (
    id integer NOT NULL,
    created_date timestamp with time zone NOT NULL,
    modified_date timestamp with time zone NOT NULL,
    question_text character varying(500) NOT NULL,
    question_template_id integer,
    question_type_id integer,
    user_id integer
);

ALTER TABLE popresearch_question OWNER TO postgres;

--
-- Name: popresearch_question_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE popresearch_question_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE popresearch_question_id_seq OWNER TO postgres;

--
-- Name: popresearch_question_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE popresearch_question_id_seq OWNED BY popresearch_question.id;

and then later on are more ALTER statements:

--
-- Name: popresearch_question id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY popresearch_question ALTER COLUMN id SET DEFAULT nextval('popresearch_question_id_seq'::regclass);

and then later:

--
-- Name: popresearch_question popresearch_question_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY popresearch_question
    ADD CONSTRAINT popresearch_question_pkey PRIMARY KEY (id);

--
-- Name: popresearch_question popresearch_question_question_text_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY popresearch_question
    ADD CONSTRAINT popresearch_question_question_text_key UNIQUE (question_text);

and after that there are at least a dozen more ALTER TABLE statements just for this one table scattered in the pg_dump output. Is there a way to get a simple, condensed CREATE TABLE statement that includes all the keys, constraints, etc.?


texts:
How can i make this trigger update my table DATA if there is no row to update.
Until i insert data in other table lets called ABC, this table uses a after insert trigger to insert information into DATA table , but the problem here is that it is firstly inserted information into Prod_Has_Ma table (its just how the system works).So this is the trigger that Prod_Has_Ma has but it wont update any information into DATA table how can i fix this problem. Is there a way to update information in a table after the corresponding row is created?

I'll aprecciate any type of help, thanks.

DELIMITER
//
create trigger ai_P_has_M
before insert on Prod_has_Ma

for each row

BEGIN

update DATA

set

Cod_Maquina1=new.Cod_Maquina1,

Nombre_Maquina=
(
SELECT Nombre_Maquina
from Maquina
where Cod_Maquina=new.Cod_Maquina1
),

Seccion=
(SELECT Seccion
from Maquina
where Cod_Maquina=new.Cod_Maquina1),

id_empresa =
(SELECT id_empresa
from Maquina
where Cod_Maquina=new.Cod_Maquina1
)

where Cod_Produccion = new.Cod_Produccion2;

END;

//
 DELIMITER ;


texts:
Consider two tables:

CREATE TABLE Users(
  id INT PRIMARY KEY,
  name VARCHAR(128)
) Engine=InnoDB

CREATE TABLE Tags(
  id INT PRIMARY KEY,
  user REFERENCES Users(id),
  tag VARCHAR(128)
) Engine=InnoDB

I would like to know which Users do not have any Tags assigned to them. I could do a LEFT JOIN and then add a WHERE clause for no Tags, but as there are tens of thousands of Users and each could have a few tens of Tags, I think that would be impractical. Is there a better way?

This is running on MySQL 5.5 and 5.6 on Ubuntu Server 12.04 running in Amazon EC2.


texts: Unable to insert into table after creating an After insert trigger

texts:
I have two tables
Table one: default_sellers_commissions  have:

id
value - INT

Table two:  marketplace_saleperpartner  have:

id
userID
commission - INT default '0.00'
commission_id

what I need is after insert a new row in table marketplace_saleperpartner update the marketplace_saleperpartner.commission value with value from default_sellers_commissions.value where the marketplace_saleperpartner.commission_id = default_sellers_commissions.id

So I create a trigger :

CREATE TRIGGER `marketplacedefaultcommission`
AFTER INSERT ON `marketplace_saleperpartner`
FOR EACH ROW
UPDATE marketplace_saleperpartner
SET `marketplace_saleperpartner`.`commision` = `default_sellers_commissions`.`value`
WHERE `marketplace_saleperpartner`.`comm_id` = `default_sellers_commissions`.`id`
AND `marketplace_saleperpartner`.`commision`='0.00';

P.S:
for some reason I need the  marketplace_saleperpartner.commision to insert for the first time with value='0.00'

now I am getting this error while trying to insert a new row into table marketplace_saleperpartner

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

Any help please ?


texts: Does missing logout event in SQL Server trace mean connection leak?

texts:
(originally asked this question on StackOverflow was told that this community is a right one).

I am investigating possibility of unreleased SQL Server database connections. I ran SQL Server Profiler and collected event traces. Typically after an executed SQL command, I see "Audit Logout" event and often exec sp_reset_connection that indicates that connection is reused for a new command.

However, I track some SPIDs and see that after some SQL command (e.g. INSERT), there are no more events for the given SPID. Does this mean that connection is not released, stays open and can not be reused?


texts:
Is it possible in MySQL to block users from executing these statements?

DESC "table"
SHOW TABLES
SHOW CREATE TABLE "table"

Or I need to write a MySQL transport port wrapper ?

GRANT ON TABLE does not help to hide database structure from a user.
I want my developers to not be allowed of dumping database structure and show tables name

The configuration file: /etc/my.cnf

[mysqld]
skip-show-database
skip-networking
bind-address=127.0.0.1
#skip-show-tables
#skip-table-stucture-show


texts: SQL Server data files in Windows Azure: filegroups and partitioning still applicable?

texts:
I just created a database using the new functionality "SQL Server 2014 data files on Windows Azure" and this works quite well.

However, I have a table with a billion records and in our local environment we have partitioned this table into 33 filegroups, my question is whether to store as Azure blob and also is it worth partitioning it into filegroups for higher performance?

My understanding is that Azure's storage is different from that in our environment, so, can we apply the same techniques to improve performance?


texts: Do I need tweak something for this MySQL setup?

texts:
I'm running a fairly popular website with the following stats and setup.

stats: ~250,000 pageviews per day, rendering ~47 queries / second to MySQL setup: the MySQL instance is running alone on a Linode512 on Debian 6.0. Nothing else runs there, for performance reasons. Using MyISAM and the ini for "large" mysql.

I see the following figures right now from MYSQLREPORT.

Please advice. The site performance is quite OK but I'm not sure what the 256 MB key buffer indicates and if that is tannable in the long run or not. I'm not very good at understanding the inner workings of MYSQL. Also, I can't figure out why the QC is being hit so low i.e. why isn't it using all of the QC.

-- Key --
Buffer used   226.52M of 256.00M  %Used:  88.49
  Current     256.00M
 Write hit      64.93%
 Read hit      100.00%

-- Questions --
Total         139.38M    47.0/s
  QC Hits      51.28M    17.3/s  %Total:  36.79
  DMS          41.99M    14.2/s           30.12
  Com-         30.75M    10.4/s           22.06
  COM-QUIT     15.36M     5.2/s           11.02
  +Unknown     10.68k     0.0/s            0.01
Slow 10 s         127     0.0/s            0.00  %DMS:   0.00  Log:  ON
DMS            41.99M    14.2/s           30.12
  SELECT       35.92M    12.1/s           25.77         85.56
  UPDATE        3.15M     1.1/s            2.26          7.50
  INSERT        2.66M     0.9/s            1.91          6.33
  DELETE      256.47k     0.1/s            0.18          0.61
  REPLACE           0       0/s            0.00          0.00
Com-           30.75M    10.4/s           22.06
  set-option   15.38M     5.2/s           11.03
  change-db    15.36M     5.2/s           11.02
  show-fields   2.70k     0.0/s            0.00

-- SELECT and Sort --
Scan            1.21M     0.4/s %SELECT:   3.38
Range         175.99k     0.1/s            0.49
Full join          24     0.0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan       2.51M     0.8/s
Sort range      4.04M     1.4/s
Sort mrg pass 177.95k     0.1/s

-- Query Cache --
Memory usage    2.78M of  16.00M  %Used:  17.38
Block Fragmnt  23.23%
Hits           51.28M    17.3/s
Inserts        33.35M    11.2/s
Insrt:Prune   68.97:1    11.1/s
Hit:Insert     1.54:1

-- Table Locks --
Waited          1.19M     0.4/s  %Total:   1.74
Immediate      67.07M    22.6/s

-- Tables --
Open              227 of  256    %Cache:  88.67
Opened          1.16k     0.0/s

-- Connections --
Max used           27 of  151      %Max:  17.88
Total          15.36M     5.2/s

-- Created Temp --
Disk table      2.16M     0.7/s
Table           2.30M     0.8/s    Size:  16.0M
File          327.26k     0.1/s

-- Threads --
Running             7 of    7
Cached              1 of    8      %Hit:  99.88
Created        19.02k     0.0/s
Slow                0       0/s

-- Aborted --
Clients             4     0.0/s
Connects           26     0.0/s

-- Bytes --
Sent          958.38M   323.0/s
Received      954.98M   321.9/s`

Btw, I tried posting this question in the DBA forums of stack exchange prior to here but it's like a grave in the other forums. Appreciate the help.


texts: Combining 2 mysql instances into 1 without downtime

texts: Can I use replication from one database server to another and then start using instance for production and then import other database server into new instance and then use replication to catch up and then use the new database server that contains both database servers combined into 1


texts: Is a Self-defining Table of Domains Normalized?

texts:
I don't really know the official name of what I've made but it can't be the first time this has been made, so apologizes up front for not knowing every term.

My question, is a table as outlined below normalized?

pk    fk    description
1     1     domains
2     1     people
3     1     cars
4     2     tom
5     2     smith
6     3     vmw
7     2     betty
8     3     ford

The main 'rule' this table confuses me about is A table should store only data for a single type of entity. It stores domain information, but it stores multiple domains' information.

A example use for this table, fetch the domain of people.

select * from domains where fk = 2

which returns

pk    fk    description
4     2     tom
5     2     smith
7     2     betty

The fk is the 'domain' the item is in, which is the pk of the domain. This is what to me is self-defining because this table defines which domains it contains.


texts: Are #tables and #procedures 'not best practice' or 'bad practice'?

texts:
I am working on a project and part of it is an interface tool that executes SQL code stored in a xml file to import/export files.

In the file, temp procedures and temp tables are used quite heavily (list of imported/exported records, logs, support tables, stored procedures for easier reading etc).

It has been decided by powers above and out of reach (luckily for both them and for me ;) )
that creating temp tables and temp procedures called during execution is 'not so good, maybe even bad' and has to be removed.....

This got me thinking and I would like to know (as I can't ask THEM), did anyone come over some general knowledge stating that 'using temp tables and procedures is not so good and should be avoided'

I know this question is a bit more vague then usual and is a bit subjective, but I would be really thankful for any insight from other, more experienced, wiser heads.

Details (obviously , i cannot be totally specific, so please bear with some obfuscation):

There is a tool that handsles importing exporting files (mostly
CSV) to the DB
the tool handles the common parts ie logging or
moving file based on success/error
there are more interfaces, depends on deployment configuration, lets assume ... on avarage 10-20 executions per day
XML contains phases for imort - init, validate, import/export, deinit
data size varies (so does speed), usually takes less then 10 min to run, some take 1 hour maybe 1,5 hours. CSV have some 100s some have 1000s  some have 10000s rows, less then 100 'columns in CSV'


texts: Stored Procedure is taking very long to complete

texts:
We have a csv file with 2000 rows and a database table1 about 2 million rows and another table2 with 60000 rows.

We need to query from table1 based on parameters from the csv file, so each row in csv should execute one select query. Initially we tried to execute the following query within a for loop in the application:

SELECT table1.c1,
       table1.c2,
       ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
       point(table1.c3, table1.c4)) * 0.2 AS length,
       table1.c5
FROM   table1
WHERE  table1.c1 IN (SELECT DISTINCT table2.c1
                     FROM      table2
                     LEFT JOIN table2.c1=table3.c1
                     WHERE table2.c2=1
                     AND   table2.c3 BETWEEN 1000 AND 2000)
HAVING length < csv.c4[i]
AND    table1.c5 BETWEEN date("start_date") AND date("end_date")
ORDER BY table.c1

the csv.c1[i] i is actually the loop index. Because of the 2000 round trips to MySQL server, it takes a long time to complete this. It takes about 16 hours just to get through the queries.

So, I wrote the SP below to avoid looping and simply call this SP so can do the looping in MySQL server:

CREATE PROCEDURE sp()
    BEGIN
      DECLARE arg0 VARCHAR(255);
      DECLARE arg1 FLOAT;
      DECLARE arg2 FLOAT;
      DECLARE arg3 FLOAT;
      DECLARE cur1 CURSOR FOR SELECT * FROM csv_based_table;
      OPEN cur1;
      read_loop: LOOP
         FETCH cur1 INTO arg0, arg1, arg2, arg3;
         SELECT col0,
                col1,
                ST_Distance_Sphere(point(arg1, arg2),point(col3,col4))*2 AS length,
                arg0
         FROM   important_table1
         WHERE  col0 IN (SELECT DISTINCT c0
                         FROM   important_table2
                         LEFT JOIN table3
                         ON col0 = c0
                         WHERE c1=1
                         and c2 BETWEEN 1000 AND 2000)
         HAVING distance < arg3
         AND col5 BETWEEN date("start_date") AND date("end_date")
         ORDER BY arg0, col0;
      END LOOP;
      CLOSE cur1;
    END;

So, instead of using the csv file, we create a database table for that csv file and run the above stored procedure. Problem is this turned out to be slower than the original 2000 iterations loop. In a test over 100 csv rows, for loop-based original solution finished in 317.6 seconds while the stored procedure took 321.5 seconds just for the SP itself. Why is this happening and how can I optimize this?


texts:
I have this query to aggregate some data from t2 into t1. This is done to optimize the application I am working on so that there are less queries to the database. I chose the below approach to make sure that I don't have to update t1 twice.

The big questions is, what indexes am I likely missing here and can the query be optimized further?

update t1
set
  col1 = t2.col1_count,
  col2 = t2.col2_sum,
  col3 = t2.col3_sum
from  (
  select
    b.user_id, b.t1_id,
    coalesce(count(b.id), 0) as col1_count,
    sum(case when b.col5 = true then b.col2 else 0 end) as col2_sum,
    sum(case when b.col5 = false then b.col3 else 0 end) as col3_sum
  from t1 a
    left join t2 b on b.t1_id = a.id
  where
    b.user_id = 1
  group by b.user_id, b.t1_id
) as t2
where
  t2.t1_id = t1.id;

EDIT Adding requested information

These are my current indexes:

create index ix_t1_user_id on t1(user_id);
create unique index ux_t2_t1_id_t3_id on t2(t1_id, t3_id);
create index ix_t2_user_id on t2(user_id);
create index ix_t2_t1_id on t2(t1_id);

explain analyze gives me the following result:

Update on t1  (cost=2725.40..2737.42 rows=1 width=138) (actual time=1.428..1.428 rows=0 loops=1)
  ->  Nested Loop  (cost=2725.40..2737.42 rows=1 width=138) (actual time=0.646..1.148 rows=166 loops=1)
        ->  Subquery Scan on t2  (cost=2725.40..2725.42 rows=1 width=84) (actual time=0.642..0.729 rows=166 loops=1)
              ->  HashAggregate  (cost=2725.40..2725.41 rows=1 width=17) (actual time=0.639..0.685 rows=166 loops=1)
                    ->  Nested Loop  (cost=5.81..2725.39 rows=1 width=17) (actual time=0.034..0.536 rows=197 loops=1)
                          ->  Bitmap Heap Scan on t2 b  (cost=5.81..414.29 rows=193 width=13) (actual time=0.024..0.050 rows=197 loops=1)
                                Recheck Cond: (user_id = 1)
                                ->  Bitmap Index Scan on ix_t2_user_id  (cost=0.00..5.76 rows=193 width=0) (actual time=0.017..0.017 rows=197 loops=1)
                                      Index Cond: (user_id = 1)
                          ->  Index Scan using t1_pkey on t1 a  (cost=0.00..11.96 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=197)
                                Index Cond: (id = b.t1_id)
                                Filter: (user_id = 1)
        ->  Index Scan using t1_pkey on t1  (cost=0.00..11.98 rows=1 width=58) (actual time=0.002..0.002 rows=1 loops=166)
              Index Cond: (id = t2.t1_id)
Total runtime: 1.490 ms


texts: Authentication failed on secondary mongodb

texts:
I have primary mongodb v3.4.10 on ubuntu (bare metal) with these users:

{
    "_id" : "admin.root",
    "user" : "root",
    "db" : "admin",
    "roles" : [
        {
            "role" : "root",
            "db" : "admin"
        }
    ]
}

Secondray is docker image with this config (I start secondary-newnode with initial sync and it become secondary):

    services:
      mongodb:
        image: mongo:3.4.15-jessie
        restart: always
        volumes:
          - /opt/mongodb/data:/data/db
          - /opt/mongodb/replication.key:/data/db/ssl/replication.key
        network_mode: "host"
        command: --storageEngine=wiredTiger --wiredTigerCacheSizeGB=12 --journal --replSet rs0 --bind_ip localhost,192.168.50.3 --auth --keyFile /data/db/ssl/replication.key

Now, when i want connect to secondary, with mongo shell authenticataion failed with

    Error: 18 { ok: 0.0, errmsg: "auth failed", code: 18, codeName: "AuthenticationFailed" }
    0

forced to new node become PRIMARY not work.


texts: How can it be that count(distinct x, y) < # records distinct x, y

texts:
I'd like to learn why the query

select distinct m.id, m.sex
from   member m
join   household h on m.household_id = h.id
join   contact c on c.household_id = h.id
where  c.contact_date between '2018-04-01' and '2018-04-30';

returns 923 rows, yet will return the value 918 with the query

select count(distinct m.id, m.sex)
from   member m
join   household h on m.household_id = h.id
join   contact c on c.household_id = h.id
where  c.contact_date between '2018-04-01' and '2018-04-30';

The query

select count(distinct m.id)
from   member m
join   household h on m.household_id = h.id
join   contact c on c.household_id = h.id
where  c.contact_date between '2018-04-01' and '2018-04-30';

also returns 923.

The statement select * from member m where sex not in ('Male', 'Female') returns no rows.


texts:
I read something in wikipedia about CDBMS. I have no idea how to setup a CDBMS server!

Is it comparable to how I am currently using mysql?

I have some extremely large databases and I want to convert my dataset into CDBMS and test my codes with the new database. What are some things I need to be aware of before I start doing this, and how will the new code be different, if at all, from my existing code?

I don't have any more details. My supervisor asked me to check if CDBMS is a better way for analyzing a large dataset. So, I just wanted to setup a CDBMS and convert my dataset to it.