Have less tables with a lot of data or a number of tables with smaller amounts of data? Lets say I have a number of tables that will have a type and status and I want the types/statues to be configurable by a end-user of the application (so I can't use enums here).  There are 2 different ways I am think about doing this:

Have Types and Statuses tables for each table that needs them.
Have one Types and Statuses table that every table that needs them uses

What are the benefits/disadvantages of each of these methods?  Which one would you go with?

Only you can really answer that question.  If you are trying to determine whether to have a global Types table and a global Statuses table for all other tables that have this data, or whether to have a Types table and a Statuses table for each table that requires type/status data.

Would the pertinent data fields be the same or similiar?  In other words, would the multiple tables have the same schema and structure?  If so, you could think of combining them.  How many tables are we talking about, and how many Types and Statuses would there be?  You don't want to saturate what would normally have been a quick operation (though choose indexes and keep statistics updated often obviously).

Answering these questions will allow you to determine what to do.

But the main point here is that, if the Types and Statuses are not related then they should be kept in separate tables.  You never know if down the line you want to add a certain field.  That'd be much easier if it's isolated to a smaller Type and Status table.


Remove last character in selected column? Query:

select * from aa;

Output:

ID  FirstName   LastName    City
-----------------------------------
29  Abcrdrr     hai         chennai
67  adf         adsd        adsf
1   John        vinoth      CITY

I want to display the lastname column without last character. I am using following queries:

Declare @name as varchar(100);

select @name = city from aa
Select @name, left(@name, len(@name)-1) as AfterRemoveLastCharacter

But output will come first row only

"The first shall be last, and the last shall be first"

REVERSE(STUFF ( REVERSE(lastname), 1, 1, ''))


Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications? I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made approximately about 15 people in different times. And it was normal practice to us to create foreign keys on the reference tables in different schemas because it is really convinient and keeps database very clean, because we don't need to keep the same refernce tables in a different schemas.

But anyway my DBA (who created new instance with DB and copy my application inside of the Solaris zone) said very harsh today, "Foreign keys on the different schemas is evil and you need to destroy it!". He didn't explain his point of view.

Is it really bad idea to do that with a big applications?

Schemas are nice to isolate tables from logical subsystems. Foreign keys guarantee data integrity. These are orthogonal concepts - as obviously data integrity between subsystems is also a must have. Accounting and Shipping and possibly Central Customer Data do not live in silos where a customer can get deleted while being used in accounting.
This is how I do it on SQL Server (although, again, our definition of schema is IIRC a LITTLE different from the Oracle definition).
As such, I think the requirement of the DBA is a sign of incompetence. T


MySQL Brute Force Defense I was wondering what defense MySQL server uses to protect against brute force attacks on the user credentials? I hope this isn't too simple of a question! I have tried to Google it, but can't seem to find really any clear cut answer.

MySQL security hasn't been every a particular strong point of the server software. That is why most people recommend not expose the MySQL port outside the internal network/firewall. Like any other software, there has been records of vulnerabilities found.

Having said that, MySQL has a basic protection against port-scan attacks, as it maintains internally a list of blocked hosts and it adds a host to this list when more than 10/100 TCP/IP connections are interrupted (max_connect_errors). Please note that this is not a real brute-force attack protection, as both a successful and an unsuccessful complete connection will reset the counter and localhost is whitelisted.


New DB is not in the end user list I created a user tom(SQL Login) in SQL Server 2019 Standard edition. Then I remove and reinstall the SQL Server 2019 standard edition(same version) and restored all the databases again with the same setting which I did previously with same instance name. Everything is same. Even same machine is used. I copied all the logins through a script provided by Microsoft. I ran the script on new installation/instance and all the logins are back again with same privileges as was granted earlier.
End user tom is unable to view the new database that I created in the List of databases in the SSMS. Tom can view the previously created databases in the list but not the new one. I granted tom sysadmin rights but still he is unable to see the new db I created from his machine. I asked tom to download and reinstall new version of SSMS, which he did but still he is unable to view the new db I created. Interestingly, when i used his credentials to login from my own personal machine to check, that new database will be coming in his(tom) list from my own personal machine, but when he login from his own machine with the same credentials, that new db is not coming into his list of DB. If right/permission is an issue then why it is coming in my own personal machine when I used his credentials to check. So where is an issue? Is it firewall issue in tom machine? Or something else?

I don't see how it is possible for tom to have sysadmin rights and not see a database.
Does tom still have sysadmin rights with with the new version of SSMS?  Normally refreshing or closing and re-opening SSMS is all that is required
Are you sure he is connecting to the correct server?  Sometimes there is a dev or test server with a similar name that the user also has rights on, but is not the same as the one you are working on.
The normal cause of the situation you describe is that 'tom' (assuming 'SQL Server login' as apposed to a windows account) has a different SID in one scenario than in the other.  This is corrected by using
SP_CHANGE_USERS_LOGIN
sp_change_users_login (Transact-SQL)
If you can go to his desk and look over his shoulder OR if he will screen share; you should see where he is doing something different then you are doing when you log on with what you believe is the same account.
Another test is you can use sp_who to see if tom is actually logged on to the server you think he is.


Importing from CSV file, is crashing mysql server I am facing a problem with importing large data into single mysql table.
Basically I assume it is happening because limited machine resources but,
I am having troubles to proof it.

this is my CSV file information:

root@my:~# tail -n 1 /tmp/keys.csv
ffff7771-e330-9d8e-6783-daf8a75fe4ef,7
root@my:~# wc -l /tmp/keys.csv
162281544 /tmp/keys.csv

So basically I have 162,281,544 lines of

ffff7771-e330-9d8e-6783-daf8a75fe4ef,7

in my CSV and its file size is 6G

I need to import this table on a daily basis and to use it only for SELECT information by the KEY eg:

SELECT uuid_tags_codes FROM keys WHERE uuid = 'ffff7771-e330-9d8e-6783-daf8a75fe4ef';

I choose MyISAM as my table engine because I read that it should be better from selecting information.

CREATE TABLE `keys` (
  `uuid` varchar(60) NOT NULL,
  `uuid_tags_codes` int(11) NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

After all this Information and I really hope it is clarifying all my technical stuff, If you think I missed something I will be happy to provide more details as you wish.

The problem is when I am using load data in file command as below:

LOAD DATA LOCAL INFILE '/tmp/keys.csv'
INTO TABLE key
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(uuid,uuid_tags_codes);

After I am executing this command it mysql is running but it is unresponding and I have to delete the table from filesystem and restart it,
Also I think the system have no enough data storage 10G free space in total before importing the file.
But this is OK I always can Improve the server machine, by extending the SSD size.

Here are my questions:

In case I extend my SSD drive should this process be OK, is mysql single table should be able to handle 6-8G data of 2 cols?
Is Mysql is the right solution to my case, or should I use different DB server for the pulling this info?
In case using mysql is ok, is my table definition is ok for the data I am storing in it? and what type of indexing should I use?

Let's do some calculation:

uuid: 36 bytes (Add 1 because it is varchar) ==> 37
uuid_tags_codes: 4 bytes
When indexed, full length of uuid field: 100 bytes

(37+4+100)bytes * 162,281,544 rows = 21.3 G

So, 21G without adding the overhead! Therefore, your first problem is the space.

To answer your questions:

It is more than 6-8G. To ba safe you need ~30G. Single MyISAM table can handle that. I don't recommend it though. MyISAM is not as safe as InnoDB for example.
MySQL is not wrong solution, but I think there are better solution. Since it is kind of look up table, key-value store would do better job. For example, memcache, Redis.
You can improve the structure by making uuid char(36). In fact, this article has even more efficient solution. Also, use the smallest integer type that fits your maximum value of uuid_tags_codes
You may want to load the data first to the table without its indexes, then create the required index.

HTH


Rolling week numbers into one row and getting cummulative values I have a table of orders with varying dates and have been asked to get the weekly order count for past 8 weeks and cummulative order count overall

I have been using the datepart clause to break my dates in week and the count function to calculate the amount of orders in that week. The code below breaks my dates into week numbers and there is a count for orders but I would like to roll it up if possible so all week 1 orders are on the same line?

Select datepart(wk,orders.date_created), COUNT(*)
from Orders
group by date_created;

I would also like to count the number of orders by week from a certain city.

Select datepart(wk,orders.date_created), COUNT(*)
from Orders
right join City as C
on Orders.city_id = C.city_id
Where city_name = 'Zurich'
group by date_created

This works to a certain extent but not as neat as I would like.  I would also like a cummulative total of orders as the weeks go on, week 1 + week 2, so on and so forth.

Any ideas

create table orders(id int identity, dt_created date);

insert into  orders (dt_created) values
('20180101'),('20180108'),('20180109'),('20180110'),('20180112'),
('20180113'),('20180114'),('20180115'),('20180116'),('20180117'),
('20180118'),('20180120'),('20180121'),('20180122'),('20180123');
GO

15 rows affected

SELECT
    DATEPART(WK, dt_created) week,
    COUNT(*) CT
FROM
    orders
GROUP BY
    DATEPART(WK, dt_created)
GO

week | CT
---: | -:
   1 |  1
   2 |  5
   3 |  6
   4 |  3

Have a look at OVER clause and how to get a cumulative total.

WITH WK AS
(
SELECT
    DATEPART(WK, dt_created) week,
    COUNT(*) CT
FROM
    orders
GROUP BY
    DATEPART(WK, dt_created)
)
SELECT
    week,
    SUM(CT) OVER (ORDER BY week) total
FROM
    WK;
GO

week | total
---: | ----:
   1 |     1
   2 |     6
   3 |    12
   4 |    15

db<>fiddle here


Insert Into MySQL over linked server when a medium text is the last column breaks Int fields I have a table with

ID INT(16)
StateName VARCHAR(50)
Details MEDIUMTEXT

When I do an insert over the linked server connection:

Insert Into MySQL...tblState (ID, StateName, Details) Values (1,'Alabama','Test')

The row ID is set to 0 instead of 1.
If I flip the table structure to put the Details before the Statename, so that the MEDIUMTEXT is not the last column, and do the same insert statement.

Insert Into MySQL...tblState (ID, Details, StateName) Values (1,'Test','Alabama')

Then the insert works exactly like it's supposed to.

Is this a known bug, or is there a setting that I need to change to fix this?

I'm using the MySQL ODBC 5.1 Driver

Edit: In case this helps anyone, here are the results of a test I did.
In phpmyadmin:

CREATE TABLE `tblState` (
  `StateID` int(11) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Details` mediumtext NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `tblState2` (
  `StateID` int(11) NOT NULL,
  `Details` mediumtext NOT NULL,
  `State` varchar(50) NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

From SQL Server over linked server:

insert into mysql...tblState(StateID,State,Details) values (1,'alabama','test1')

(1 row(s) affected)

select * from mysql...tblState

StateID     State                Details
----------- -------------------- --------------------
0           alabama              test1

(1 row(s) affected)

insert into mysql...tblState2(StateID,Details,State) values (1,'test2','alabama' )

(1 row(s) affected)

select * from mysql...tblState2

StateID     Details              State
----------- -------------------- --------------------
1           test2                alabama

(1 row(s) affected)

I'm going to go out on a limb and say that there's probably a bug somewhere - especially given the mediumtext data type. Here's a little work I did, obviously there're a few additional tests that you can run. Here is a SQLFiddle for your reference.

DDL

CREATE TABLE `tblState` (
  `StateID` int(11) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Details` mediumtext NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tblState2` (
  `StateID` int(11) NOT NULL,
  `Details` mediumtext NOT NULL,
  `State` varchar(50) NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tblState3` (
  `StateID` int(11) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Details` mediumtext NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DML

INSERT INTO tblState(StateID,State,Details) values (1,'alabama','test1');
INSERT INTO tblState2(StateID,Details,State) values (1,'test2','alabama');
INSERT INTO tblState3(StateID,Details,State) values (1,'test2','alabama');

INSERT INTO tblState(StateID,Details,State) VALUES (2,'It is nice in','Colorado');
INSERT INTO tblState2(StateID,State,Details)
    VALUES (3,'South Dakota','Is actually very nice');
INSERT INTO tblState3(StateID,State,Details)
    VALUES (3,'South Dakota','Is actually very nice');

INSERT INTO tblState(Details, State, StateID)
    VALUES ('It never rains in southern','California',7);
INSERT INTO tblState2(Details,StateID,State)
    VALUES ('It used to be so hard to spell, it used to make me cry',5,'Mississippi');
INSERT INTO tblState3(Details,StateID,State)
    VALUES ('It used to be so hard to spell, it used to make me cry',5,'Mississippi');

select * from tblState;

renders:

STATEID     STATE       DETAILS
--------------------------------------
1           alabama     test1
2           Colorado    It is nice in
7           California  It never rains in southern

while

select * from tblState2;

renders:

STATEID     DETAILS                                                 STATE
--------------------------------------------------------------------------------
1           test2                                                   alabama
3           Is actually very nice                                   South Dakota
5           It used to be so hard to spell, it used to make me cry  Mississippi

Finally,

select * from tblState3;

renders the output from table 2 in the order of table 1 (as expected):

NOW, having said all of this, this has been executed on a MySQL server directly (not through a linked) that is a recent version (MySQL 5.5). Perhaps you can run the content of the fiddle directly on your server through phpMyAdmin. This may help us understand if your MySQL version is the problem, or not.

You'll note that tblState3 preserves the original order of the problematic table while varying the order of inserts, just to add a bit of robustness to the experiment.


MYSQL - order by indexed column I have this table with 500,000 rows:

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_id` int(10) unsigned NOT NULL,
  `cat_id` mediumint(5) NOT NULL,
  `title` mediumtext NOT NULL,
  `views_point` int(10) unsigned NOT NULL DEFAULT '0',
  `publishedon_hourly` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `views_point` (`views_point`),
  KEY `listings` (`publishedon_hourly`,`published`,`cat_id`,`source_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=365513 ;

I want to make a query like this:

SELECT *
FROM listings
WHERE (`publishedon_hourly`
    BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' )
    AND UNIX_TIMESTAMP( '2015-10-5 12:00:00' ) )
AND ( published =1 )
AND cat_id IN ( 1, 2, 3, 4, 5 )
ORDER BY views_point DESC
LIMIT 10

This query sometimes works exactly as I want (see this), but according to some opinions, this is incorrect. I searched the web for solutions and found these:

http://venublog.com/2007/11/29/mysql-how-to-avoid-filesort/
http://www.getsymphony.com/discuss/issues/view/657/

I added an index on views_point then tried this query:

select t1.*
from  listings t1
left outer join  listings t2 on (t1.views_point=t2.views_point)
order by t1.views_point
limit 10

This is the explain:

but I cannot add this condition:

`publishedon_hourly`
    BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' )
    AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' )
AND ( published =1 )
AND cat_id = 5

I don't know, which one should I assign to (t1 or t2)?

On the other hand what about this way:

SELECT *
FROM listings
WHERE (`publishedon_hourly`
    BETWEEN UNIX_TIMESTAMP( '2015-09-5 00:00:00' )
    AND UNIX_TIMESTAMP( '2015-09-5 23:00:00' ))
AND (published =1)
and views_point is not null
ORDER BY views_point DESC limit 20

If neither one will be good, can any one tell me please how the big blogs get the posts by hits?

When building an INDEX, start with the '=' columns in WHERE.  Then move on to one range:

INDEX(published, publishedon_hourly)  -- or
INDEX(published, cat_id, publishedon_hourly)

In newer versions of MySQL, the second choice may do a leapfrog game (with the IN) to be more efficient than in older versions.  Give it a try.

My cookbook.

You could also give it this one

INDEX(published, views_point)

but that is risky -- It will be fast ood if 20 desired rows show up with high values of views_point, but it will be slow otherwise.  The Optimizer is not smart enough to always choose correctly between handing the ORDER BY versus handling the WHERE.

(As a side note... Did you want the extra noon?  BETWEEN..AND is inclusive.)


Replace string in the all tables of DB I'm moving my website to new domain, need to replace all references of old domain to a new, in fact actually replace one string on another in all tables of MySQL DB.

Please help me build the query to do this.

Thanks.

Assuming you have access to the sed command line application, doing something like:

mysqladmin create newdb
mysqldump --routines olddb | sed 's/olddomain.com/newdomain.com/' | mysql newdb

will convert all strings with "olddomain.com" into "newdomain.com" from a dump we get from the database olddb and will import it into a newdb database, which was just created.

You may need to add extra parameters or a defaults client .my.cnf file for the connection options.

Be careful, this is an easy process but a bit dangerous, as it will change the string wherever it is found, be it in on table or column names and comments, and without checking if it is part of a longer url or not. This is why I have make it load it on a separate database so you can check that it is working as intended.

I use this every time to convert between table engines from mysql dumps:

sed 's/ENGINE=PERFORMANCE_SCHEMA/ENGINE=InnoDB/g' p_s_57.sql

Once you have seen that it is correct, just point your production database to the new place or rename your tables (the safest way to do that is again using mysqldump).

There are other options, that could involve scripting by querying information_schema.columns and using UPDATE table SET column = REPLACE(column, 'olddomain.com', 'newdomain.com');. Those could give you more control, but would require a bit of extra scripting.


how to fix replica set error I am getting this error

{ "ok" : 0, "errmsg" : "server is not running with --replSet" }

when I run the command on windows cmd

rs.initiate( { _id : "repl1", members: [ { _id : 0, host : "mongodb0.example.net:27017" } ] })

I changed it to

rs.initiate( { _id : "repl1", members: [ { _id : 0, host : "localhost:27017" } ] })

but still have same error

how can I fix this issue?

Simple, start your replica sets nodes with --replSet xxx (where xxx is your replica set name) -parameter. If you use config files, then replication.replSetName: xxx

Read here how to configure/deploy replica set.


What happens when a file size limit is reached? I have a SQL Server 2008 R2 Standard Edition database for which I have configured the ROWS (.mdf) database file with autogrowth ON and set to max size to 10GB.

After a while, I get an alert that my tempdb log has filled a drive. I figure that my database's size is stuck on the limit at 9990MB or something, unable to extend any further.

Are those two things possibly related? Would the database, being unable to expand, store its transactions in tempdb?

What happens when a database ROWS file gets filled and someone keeps adding data to it?

The data was coming from SQL Server performance data monitoring:

No, the two are unrelated.

TempDB is used for explicit temp tables, but also for sorting and database snapshots and lots of other internal things. Was there some other very large transaction going on when the TempDB filled up?

If a transaction needs more data space (or log space, or TempDB space) but the file is set to not grow (or there is insufficient space on disk), the transaction will fail and roll back. So no new data will be added.


DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query I have data in a java object as data1, data2.

data1 and data2 together forms a composite key in myTable where I want to insert the object.

The writing is happening as a batch. Like 10 insert statements are prepared using 10 objects and are executed as a batch.

I want to insert the above data with the constraint: data1 + data2 should not already be present in myTable i.e. data1 + data2 should be unique --- if unique then write else just ignore.

The query I am using is:

Insert into mySchema.myTable(column1, column2)
  select 'abc', '123'
  from SYSIBM.DUAL
  where not exists
        ( select 1
          from mySchema.myTable A
          where 'abc' = A.column1
          and '123' = A.column2
        )

Running above query independently for single set of data runs successfully.

However, while running in batch scenario I am getting "com.ibm.db2.jcc.b.ie: Non-atomic batch failure." error.

I think it has something to do with using SYSIBM.DUAL in batch scenario.

Code which is failing:

Insert Query:

Insert into mySchema.myTable(column1, column2)
  select ?, ?
  from SYSIBM.DUAL
  where not exists
        ( select 1
          from mySchema.myTable A
          where ? = A.column1
          and ? = A.column2
        )

Statement Setters:

ps.setString(1, item.getColumn1());
ps.setString(2, item.getColumn2());
ps.setString(3, item.getColumn1());
ps.setString(4, item.getColumn2());

where item is the java object holding the two columns to write.

Error is:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar

[Insert into mySchema.myTable(column1, column2) select ?,?
from SYSIBM.DUAL
where not exists
(select 1 from mySchema.myTable A where ?=A.column1 and ?=A.column2)];

nested exception is com.ibm.db2.jcc.b.ie: Non-atomic batch failure.

The batch was submitted, but at least one exception occurred on an
individual member of the batch.

Use getNextException() to retrieve the exceptions for specific batched elements.

Thanks,
Nik

This question is somewhat old, so you may have already figured out your problem, but if I may offer an alternative...

It may be better to use a MERGE statement in this situation (click the link for the publib entry):

MERGE INTO mySchema.myTable tab USING (
        VALUES ('abc', '123')
    ) AS merge (C1, C2)
    ON  tab.column1 = merge.C1
    AND tab.column2 = merge.C2
    WHEN MATCHED THEN
        IGNORE
    WHEN NOT MATCHED THEN
        INSERT (column1, column2)
        VALUES (merge.C1, merge.C2)

This will take the columns in the "fake" table merge, and compare them using the keys in the ON clause, and if there is not a match, then it will use the INSERT statement.  If there is a match, that row will just be ignored.

However, the availability of MERGE depends on your platform.  I'm fairly certain MERGE has been in Linux/Unix/Windows DB2 since v8 (although, you can only use PREPARE'd merges since 9.7), and it was added in z/OS DB2 in v9.1.  I don't know about the other platforms (AS/400, etc.).


WHERE clause seems to be messing up FIELD function I'm trying to do a simple query but just can't get it to work and can't find an answer online that's working correctly.  All I want to do is query a table with a where clause, then order those results based on some value.  Here is the query:
SELECT * FROM reservation_form_results
    WHERE campaign_id = $campaignId
    ORDER BY FIELD(status, '$sortValue')

Here is what the data looks like (id is an int, campaign_id is an int, and status is an enum where the values default to undecided, but include good_fit, bad_fit, added):
id | campaign_id | status
----------------------------
 1 |          80 | good_fit
 2 |          80 | good_fit
 3 |          81 | bad_fit
 4 |          85 | added
 5 |          80 | added
 6 |          80 | bad_fit
 7 |          85 | undecided

Any thoughts on what could be going wrong?  It currently works if I remove either the where clause or the Field function.  Thanks

I didn't know MySQL understood "$campaignid" as a variable, in the examples I see "@" being used, as in "@campaignid".
Anyway, in stead of FIELD (which needs a list of arguments, not an argument containing a list) I'd use LOCATE, with sortValue containing "good_fit,bad_fit,added,undecided":
SELECT * FROM reservation_form_results
    WHERE campaign_id = $campaignId
    ORDER BY locate(status, '$sortValue')

"undecided" will have the highest location, preceded by added, etc.
db fiddle
PHP MySQL prepared statements


Restrict range of dynamic ports available to SQL Server Is there a way to limit the range of dynamic ports available to SQL Server from the database side, or database server side? Our SOP is to use static ports across the network firewall and a vendor is having trouble locking down their ports. Theoretically, if we allowed a range of 1000 ports inside the dynamic range (49152–65535) across the firewall how would I limit SQL Server to only assign a dynamic port inside that range?

Here's the start of a PowerShell solution as I mentioned in the comments:

$instanceId = "<Instance ID>";
$registryKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceId\MSSQLServer\SuperSocketNetLib\TCP\IPAll"
$portNumber = (Get-Item $registryKey).GetValue("TcpDynamicPorts");

netsh advfirewall firewall add rule action=allow localport=$portNumber protocol=TCP dir=in name="SQL Server Database Engine ($instanceId)"

I'm not sure if this is going to be appropriate for the exact situation, but it might help someone out there.


How to split a large table in Teradata I'm trying to load data from a very large table (200 million rows) into a Presentation Datamart (PDM) for an Enterprise Data Warehouse using Teradata 14.10. I want to split this large table into several separate tables containing 30 million rows each.

I've simplified things down, but here's the table structure:

CREATE SET TABLE MYDB.LARGE_TABLE ,NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
(
    CUSTOMERID INTEGER TITLE 'CUSTOMER IDENTIFIER' NOT NULL
    , FULLNAME VARCHAR(30) NOT CASESPECIFIC TITLE 'FULLNAME'
);

I've gotten as far as using ROW_NUMBER() so I know how many actual rows there are in the table:

SELECT
    ROW_NUMBER() OVER (PARTITION BY CUSTOMERID, ORDER BY CUSTOMERID) AS RANK_CUST
    , CUSTOMERID
    , FULLNAME
FROM
    MYDB.LARGE_TABLE AS MYTBL

Because of the SQL standards that are enforced we have to follow these restrictions:

Cannot use Stored Procedures
No single piece of code to contain more than 3 table joins
No single piece of code to result in more than 30 million rows of output

I'm preparing this SQL script for a very stringent review, and it definitely won't be approved unless I can find a way to split this large table into smaller rows of data. With these restrictions in mind, does anyone have any ideas?

You can use the QUALIFY statement to restrict the number of rows against a large table.

SELECT CUSTOMERID
     , FULLNAME
  FROM MYDB.LARGE_TABLE AS MYTBL
QUALIFY ROW_NUMBER() OVER (ORDER BY CUSTOMERID)
        BETWEEN 1 and 30000000;

edit - corrected syntax of ROW_NUMBER()


MySQL vs SQL Server Express (Compare Free DBMS) I am searching for a comparison table between MySQL and SQL Server Express.

Comparisons should include...

Usage limit
Tools (management/dev/backup/monitoring/...)
"simultaneous connection"

It's for my company, I need to know what should I install.

It's for migrating from access db

if you have opinion, suggestions,....

Thank you

I will be totally honest with you. While MySQL works fine in Windows, I would not recommend it for any Enterprise use in Windows.

You should go with SQL Server Express because you will have a database that works great in Windows for Free. Should your company fully commit to Enterprise Database usage, you could simply upgrade to SQL Server (different advanced versions).

If you are going for moderate budget, go with MySQL, BUT SWITCH TO Linux. High Availability solutions fit Linux and Unix, not Windows.


Problem with mysql group_concat, complex query, not getting required result I have 5 tables

college
trade
district
contact
intake

Their relationship looks like this

I'm having problem with the contact table. In this table, cid and location are the primary key. It can have only two location campus and office for each cid i.e. college id. Table intake connects college and trade table.
With this query below, I'm getting results twice for each address.
This is my query:

SELECT cname, ctype, tname, seats, hostel, new, dname, address, ph_no, email, website
FROM college, trade, district, intake, contact
WHERE intake.cid = college.cid and intake.tid = trade.tid and college.did = district.did and contact.cid = college.cid
ORDER BY cname ASC;

My current result:

Result that I want:

I know this can be achieved by grop_concat and concat function but I don't know how to use these functions in this situation. Somebody please help me to build the query to achieve this result.

Thank you.

It will be something like this:

SELECT cname, ctype, tname, seats, hostel, dname,
GROUP_CONCAT(addr SEPARATOR '\n') addresses
FROM
(
    SELECT cname, ctype, tname, seats, hostel, dname,
    CONCAT('Address: ',address,',Phone:',ph_no,',Email:',email,',Website:',website) addr
    FROM college, trade, district, intake, contact
    WHERE intake.cid = college.cid
    and intake.tid = trade.tid
    and college.did = district.did
    and contact.cid = college.cid
    AND cname LIKE '%{$cname}%'
    AND tname LIKE '%{$tname}%' {$ctype}
    AND dname LIKE '%{$dname}%' {$hostel}
    ORDER BY cname ASC
) A GROUP BY cname, ctype, tname, seats, hostel, dname;

Since group_concat_max_len has a default of 1024, you may need do two more things to accommodate GROUP_CONCAT results longer that 1024

First, login to MySQL and run

mysql> SET GLOBAL group_concat_max_len = 1024 * 1024;

Then, add this to my.cnf under the [mysqld] group header

[mysqld]
group_concat_max_len=1M

GIVE IT A TRY !!!


Is MAX(rowversion) optimized for the table? When trying MAX(rv) for the table--the rv rowversion is defined there--I have noticed that the operation is very fast even for big tables. Is there an index implemented implicitly for the rowversion column? Or is the MAX() function optimized for the rowversion value? In other words, is the maximum value for the rowersion of the table stored elsewhere?
It is SQL Server 2014 if it matters.

No, it doesn't appear there is any special treatment for rowversion columns.  This small demo confirms the entire index is scanned to find the MAX value:
CREATE TABLE dbo.RvTest
(
    Id int NOT NULL,
    rv rowversion,

    CONSTRAINT PK_RvTest PRIMARY KEY (Id)
);
GO

INSERT INTO dbo.RvTest
    (Id)
SELECT DISTINCT
    [number]
FROM master.dbo.spt_values v1
WHERE
    v1.[number] IS NOT NULL;
GO

SELECT MAX(rv) FROM dbo.RvTest;

Interactive db<>fiddle link
This makes sense when you consider that the rowversion is unique across the whole database, rather than a specific table:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion.

It doesn't look like SQL Server stores table-specific metadata about rowversions.
If you need that check to be very fast, there's nothing stopping you from indexing that column yourself:
CREATE NONCLUSTERED INDEX IX_rv ON dbo.RvTest (rv DESC);

Which results in a single row being read for that MAX query:

As a side note, as Panagiotis Kanavos has mentioned in the comments, this is usually used for optimistic concurrency checks - to tell if a specific row has changed since the last time it was read.  The way you're using it is still valid (keeping the last maximum, and checking the current max to see if any data in the table has changed), although the built-in Change Tracking feature might be attractive to you as an alternative.


Hallengren @DatabasesInParallel sql_message_id 50000 in sysjobhistory table Just implemented Ola's Parallel execution for backups, integrity checks and index maintenance. Everything appears to be running great but I am seeing sql_message_id 50000 in the sysjobhistory table for the new executions. I am also seeing [SQLSTATE 01000] on each line of the job output files. Is this normal behavior? I can't seem to find any in-depth documentation online.

The Message 50000 in msdb.dbo.Sysjobhistory is intended.

Take part of Ola Hallengren's index optimize procedure:[dbo].[IndexOptimize], part of the integrity checks: [dbo].[DatabaseIntegrityCheck]  or part of the backup procedure: [dbo].[DatabaseBackup], the same RAISERROR() Statements will be shown:

  SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120)
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar(max))
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar(max))
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Platform: ' + @HostPlatform
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Parameters: ' + @Parameters
  SET @StartMessage = REPLACE(@StartMessage,'%','%%')
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Version: ' + @VersionTimestamp
  RAISERROR(@StartMessage,10,1) WITH NOWAIT

  SET @StartMessage = 'Source: https://ola.hallengren.com' + CHAR(13) + CHAR(10) + ' '

  RAISERROR(@StartMessage,10,1) WITH NOWAIT

The RAISERRROR()'s are going to be executing when the procedure runs.

Looking into the history, we see the 'error's' shown:

select message From msdb..sysjobhistory
where sql_message_id =50000

Result

Executed as user: DOMAIN\test. ...0000)  Server: test [SQLSTATE 01000] (Message 50000)  Version: 12.0.5546.0 [SQLSTATE 01000] (Message 50000) ...

With the same Server and Version info as defined in the previous T-SQL examples.

Why does the 'error' start at 50000?

It starts at 50000, because starting from 50000 and upwards is where user generated errors can be defined.

Why are there raiserror()'s in the procedures?

My opinion is that the RAISERRROR() is added to add additional information to the history DMV, as to know what is being executed, and have additional information without it being a mess. It gives you control over what will be shown.

I will leave more in depth reasoning to Ola himself.

Subsequentially the history is added to the SSMS Job Activity monitor that we (Most of us) know and use:

So to answer the question, it is coded into the proc with reasoning behind it.

RAISERROR() 50000 default example

You can only generate custom errors with raiserrror when using an error number starting from 50000.

Testing with a job, with no specific error number:

Result:

Starts at 50000.


One database per company for "security" reasons Possible Duplicate:
  Is that a good idea to use one database for 50.000+ shops?

We are developing an internet application in C# with MVC 4.
It's connected to a database which will contain a companies customers and their turnovers, also the products.

Naturally,these companies want their data secured, and also the data needs to be brought from the database to the web application as quickly as possible.

We are unsure what is the most secure and performance effective approach:

Have one smaller database for each company, containing their customer data. This way the only "weak" human link is to code the connection string correct depending on who's logging in.
Use one single database containing every companies data and all their customer data. This will naturally be a very much larger database, and possible(?) more room for error, leading to one employee seeing another companies turnovers. A critical situation.

What solution might be most secure, and the fastest in crunching numbers and processing it to the web application?
is this a common "security" solution, to minimize human error by giving each company their own database?

Separate databases are preferable for many reasons. THese are the ones I gave when we had the same decision to make:

Separating client databases means there is no way someone can manipulate a querystring to view another client's data.
If client1 somehow nukes their database, Client2 through Client-n isn't affected and rollback only affects a single client.
Better performance because each database has it's own separate file with its own dedicated IO, cursors, etc.
Smaller tables are quicker to access. This may not be an issue now, but five years down the line when you have 10 10GB databases, you'll be glad you don't have only one 100GB database.
By separating the databases, you remove the need for one whole layer of management. You don't need to check whether a request for data is allowed based on the source client and the targer client's data. This speeds up the data access even more.
Backups are much more simple. What would you do if Client3 asks you to create a backup of their data? You'll need to write some serious SQL-fu if all the data is in one database. Having separate db's for each client means you can simply use SSMS to run a quick export. Onle a few clicks, a quick ZIPm and the backup is ready for delivery.


Having both a integer Primary Key and a GUID/UUID column on same table-is this bad I have my MySQL database having tables where on each table, we have a ID (int, auto increment) clustered primary key and a UUID (varchar36) column that's just a GUID.

The ID is there just for indexing purposes and nothing more.

Joins and all queries will run against the UUID column. That means foreign keys will reference the UUID columns.

Reason I do this is because I want to be able to migrate more easily.
I am aware of the size penalty of the GUID.

Question: is this bad architecture? What are the performance implications beyond size?

I personally don't see a problem with having both a ID and a UID, I use this technique quite a lot actually. However, I still use the primary key to perform the JOIN on as this performs better - the UID is used mainly in the WHERE clause when searching for records.

In your example, the biggest point I would make is not to use a VARCHAR(36) for your UID - I would use BINARY(16) as this fits a UUID perfectly. The CHAR type is 1 byte and so you are using an extra 20 bytes of storage to persist your UUID. Also, joining on this column will be slower than a join on the BINARY(16) equivalent.


Minimise time for migration I want to migrate  production DBs from one server to a different one.

This is my plan:

Shut down the application
Change the DB to single user mode
Take a full backup for all databases
Restore the backup to the New server and remap logins
Create replication
Point the application to the new server
Start the application

This may take 4 hours to complete. Is there a way I can perform this in less time? It is kind of hard to request 4 hours down time.

You can significantly lower the amount of downtime by running both current and new versions in parallel. Use something like Log Shipping, Always On Availability Groups or even replication to generate a copy of your database on a new instance that is running whatever newer version you want to upgrade to. I'd go with Log Shipping as it's the simplest, technically, and is available on any edition of SQL Server. Always On AG gets you closest in sync but take a bit more work to setup. Repl is kinda in between.

It may take a while for both databases to be "almost" in sync depending on how large the database is and your infrastructure but that is not as important since your system is online the whole time. Once you get to a steady state where all existing data from the original DB has moved to the new DB and you're left with only new transactions to move, then stop transactions on the original, let all data get moved to the new DB. Actual method/command depends on what approach you take but all are well documented on MSDN.

Verify all pending transactions have been pushed to the new DB then make the necessary changes so applications point to the new DB. Finally, run validation tests against the new DB to make sure everything works as it's supposed to (even if you've tested in your pre-prod/QA environment) and re-enable applications/transactions if all tested clear.

This puts the bulk of your downtime on whatever tests you need to run and you really should not skip that part. Cutting over from Log Shipping, AlwaysOn AG or replication is a matter of minutes, assuming no server, storage or network bottlenecks.


Query to find customers that have deposit in all branches located in a specific city I have 3 tables as follow:

Customer (CustomerName , CustomerCity , CustomerStreet)
Branch   (BranchName , BranchCity)
Deposit  (CustomerName , BranchName , AccountNumber , Balance)

Here I show you sample data of the above tables :

Customer ('John','City1','Street1')
         ('Lili','City2','street2')

Branch   ('Branch1','Kaj')
         ('Branch2','Bahar')
         ('Branch3','Bahar')
         ('Branch4','Bahar')

Deposit  ('John','Branch1',12,1000)
         ('Lili','Branch1',123,1222)
         ('Lili','Branch2',124,45233)
         ('Lili','Branch3',345,6000)
         ('Lili','Branch4',567,5600)

So based on the information above, I need a query to give me CustomerName
that have a deposit in all branches that are in Bahar.

I should see 'Lili' in the output because it has deposits for all branches that are in Bahar but I should not see John cause it has a deposit only in one branch of Bahar.

I did my best to write the query but I could not. Could you help me with this please?

I have two solutions for your question :
solution 1 , I use Not Exists , Except

Select distinct C.CustomerName
From customers C
where NOT EXISTS ((Select BranchName
                   From Branch
                   Where BranchCity='Bahar')

                      EXCEPT

                   (Select BranchName
                    From Deposit a
                    where A.CustomerName = C.CustomerName))

In solution 2 I use Not Exists ,

Select Distinct C.CustomerName
From Customers C
Where NOT EXISTS  (Select BranchName
                   From Branch b
                   Where BranchCity='Bahar'
                   and NOT EXISTS (select BranchName
                                   from Deposit a
                                   where a.CustomerName=C.CustomerName
                                   and a.BranchName=b.branchName))

Each one of the query above will answer your query and give you the desired result.


SQL query to list software by major versions I have the following code i am tiring to get it to only show one line in the results for each major version, i need to combine all of the rows that are the same Like so.

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
,swc.MajorVersion
,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid

WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
GROUP BY
arp.DisplayName, arp.Publisher, swc.MajorVersion

order by
arp.DisplayName asc

Results :

Count   displayname MajorVersion

41  Adobe Reader 8  8

1   Adobe Reader 8.1.1  8

40  Adobe Reader 8.1.3  8

1   Adobe Reader 9.3    9

1   Adobe Reader 9.3.3  9

1   Adobe Reader 9.4.0  9

1   Adobe Reader 9.5.5  9

4   Adobe Reader X (10.1.13)    10

1   Adobe Reader X (10.1.4) 10

1   Adobe Reader X MUI  10

7   Adobe Reader XI 11

139 Adobe Reader XI (11.0.02)   11

1   Adobe Reader XI (11.0.03)   11

2   Adobe Reader XI (11.0.06)   11

28  Adobe Reader XI (11.0.07)   11

1   Adobe Reader XI (11.0.08)   11

62  Adobe Reader XI (11.0.09)   11

5527    Adobe Reader XI (11.0.10)   11

3   Adobe Reader XI (11.0.10)  MUI  11

try this: I removed a few fields and changed the group by

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
-- remove these fields
--,swc.MajorVersion
--,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid

WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
--change the group by
--GROUP BY
--arp.DisplayName, arp.Publisher, swc.MajorVersion

-- to this
group by arp.DisplayName

order by
arp.DisplayName asc


What intersection do multiple inner joins return? If I have a query:

SELECT * FROM A
INNER JOIN B ON B.ID = A.ID
INNER JOIN C ON C.ID = B.ID

Would that return a different result to this query?

SELECT * FROM A
INNER JOIN B ON B.ID = A.ID
INNER JOIN C ON C.ID = A.ID

It seems to me that in the first query I would get the intersection of A,B,C. And in the second query I would get the intersection of A,B, and the intersection of A,C.

If that were the case, would I find NULL values in the columns for B/C where there is no intersection between B,C?

No, the two queries are the same.

Since A.ID = B.ID and A.ID = C.ID, then by definition B.ID = C.ID.

For any given ID x, if there's a row in A with the ID and a row in B with the ID, but no row in C with the ID, then the row(s) from A and B will be ignored, because both joins are INNER JOINs. Same holds true for A-C-B and B-C-A, of course.

So, when all the JOINs are INNER JOINs, the only way to get NULL for a given column is if that column is NULL in an existing row - you can't fill in a row of NULLs in one table because there's no matching row, as the lack of a matching row in one INNER JOINed table means the rows in the other tables aren't valid.

In other words - For a given row in B, if there's no matching row in C, then it's also true that there's no match for the rows in A JOIN B that use that B row. That B row (and any A rows that match it) won't be included because a row has to exist for all INNER JOINed tables.

You asked if anything changed if the query was A INNER JOIN B RIGHT OUTER JOIN C or A LEFT OUTER JOIN B INNER JOIN C.

It can change.

Under normal circumstance, joins will be evaluated from left to right (that is, from the first listed join to the last).

If for some reason you must modify that behavior, you do so by moving the position of the ON clause. Depending on your DBMS, you may also have to add parentheses to indicate the order in which the clauses should be evaluated (I tried MS SQL and MySQL; MySQL required the parentheses to work, MS SQL allowed them but did not require them). I've included the parentheses in my examples below that don't evaluate left to right, to help make that clear.

Examples:

A INNER JOIN B ON <condition1> RIGHT OUTER JOIN C ON <condition2>

This would first join A and B (requiring a match on <condition1> to associate rows from the two tables), then perform the right outer join to C (where all rows from C that meet the WHERE condition will be returned, and will be associated with any rows from the A-B join where <condition2> is met.
A INNER JOIN (B RIGHT OUTER JOIN C ON <condition1>) ON <condition2>

This, on the other hand, would join B and C (including all rows from C, that meet the WHERE clause, associated with any rows from B that meet <condition1>. Then, the results of the B-C join are inner joined with A, associated where there is a row from A and a row from B-C that match based on <condition2>. Note that here, you will get different results for A.id - B.id and A.id = C.id, as all rows will have a non-NULL C.id, but B.id will be NULL where there was no matching row from B.
A LEFT JOIN B ON <condition1> INNER JOIN C ON <condition2>

With the ON clauses in the usual places, this is evaluated left to right. So, first we taken all rows from A, associating them with any matching rows from B based on <condition1>; then, we do an inner join of the results of the A-B join with C, returning only the rows where a row from A-B matches a row from C based on <condition2>.
A LEFT JOIN (B INNER JOIN C ON <condition1>) ON <condition2>

Here, we're again forcing the B-C inner join to happen first, so we get rows when a row from B matches a row from C based on <condition1>; then, we take the results of the B-C inner join and match that to A (returning all rows from A, with any matching rows from B-C based on <condition2>

Here's a couple of SQLFiddle links showing the results of these joins as you move around the ON clauses: one for MySQL and one for MS SQL Server.

NOTE: I've said nothing about how this works with non-ANSI join syntax. If you don't know what I'm talking about - thank your lucky stars and move on. I don't know precisely how the precedence works with that syntax. I'd expect it to go through the list of tables, left to right (first mentioned to last). Obviously, since there are no ON clauses, those have no impact. There may be ways to force the order there to (perhaps parentheses again, presumably in the FROM clause?), but I don't know the answer. As most people (myself included) would strongly recommend against trying to use that syntax for your JOINs, I'll simply use that to avoid the question :-).


Deleting aerospike bins I have an Aerospike instance and I messed up one of my namespaces. I didn't know there was a ~32k limit on bins, so I wrote unique bin names into my namespace. I hit that 32k limit, and now my whole namespace is hurting. How can I delete a bin from the entire namespace so I can free up my bin limit?

I've looked at the aerospike documentation and I just can't seem to find anything. I've even looked for 3rd party programs and I can't find anything there ether.

The data structure that maintains the bin information grows monotonically during the life of the process. The only way to remove Bin[s] from Aerospike is to first remove the bin from all records containing it followed by a rolling restart of the cluster.

You can find documentation on using scan here

And see DeleteBin.java in aerospike/examples for how to delete a bin.


Insufficient Privileges When Connect to SYS and SYSTEM I have installed EBS VISION instance on my virtual machine.

After installation from my windows machine I am able to connect to schemas such as APPS, HR, however, unable to connect to SYS and SYSTEM schemas. I am getting insufficient privileges error.

What could be the reason and how able to resolve this?

Update 1

Files screenshot

SQLPLUS screenshot

As andba77 suggested, it sounds like a problem with remote password file.  First you need to determine if you have a password file.  It should be on the database server at $ORACLE_HOME/dbs/orapw.  Second is the setting of the init parm 'remote_login_passwordfile'.  If it is set to 'none', no remote logins as sysdba will be allowed.  You can read more about that parameter in the docs at http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams212.htm#REFRN10184

It would help if we had more detail on the remote connection.  Best and most reliable would be to reproduce the problem with sqlplus at a command prompt, then copy and paste the entire command and the entire response.  Only then can we know exactly what you are doing, and in this particular question "exactly" is important.


Use GIN to index bit strings I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). Insertions are rather infrequent, whereas searches are the mostly used operation. In a search, I would like to get all the rows that exactly match the bit string.

It looks like a perfect job for GIN (in combination with my own data type), or what do you think?

In a search, I would like to get all the rows that exactly match the
bit string.

Use a B-Tree index, the default type. I don't see a case for a GIN index here.
Up to 1000 bits result in up to 133 bytes (or slightly more) storage size on disk for a bit varying type.
SELECT pg_column_size(repeat('1', 1000)::varbit)  -- 133

Not that much. A plain B-Tree index should do. But maybe the column is big enough that the following tricks improve performance.
If a small part of the bitstring column is distinctive enough to narrow your search down to few hits, an index on an expression might give you better performance, because the smaller index can fit into RAM and is faster to process all around. Don't bother for small tables, the overhead would eat the benefit. But could make a big difference for big tables.
Example
Given table:
CREATE TABLE tbl(id serial PRIMARY KEY, b_col varbit);

If the first 10 bit are enough to narrow down a search to a few hits, you could create an index on the expression b_col::bit(10). Casting to bin(n) truncates the bitstring to n bit.
CREATE INDEX tbl_b_col10_idx ON tbl ((b_col::bit(10)))

Extra parentheses are required for the cast operator in an index definition. See:

How to create an index on an integer json property in postgres

Then, instead of the query
SELECT * FROM tbl WHERE b_col = '1111011110111101'::varbit; -- 16 bit

You would use:
SELECT *
FROM   tbl
WHERE  b_col::bit(10) = '1111011110111101'::bit(10) -- utilize index
AND    b_col = '1111011110111101'::varbit;  -- filter to exact match

Be aware that shorter values are padded with 0's to the right (least significant bits) when cast to bit(n).
In a real world application this starts to make sense with several 100s of bits. Test for the turning point.
Optimize further
Since most installations operate with a MAXALIGN of 8 bytes (64 bit OS) (more details here), your index size is the same for any data not exceeding 8 bytes. Effectively, per row:

 4 bytes item identifier
 8 bytes for the index tuple header (or 23 + 1 byte for heap tuples)
 ? actual space for data
 ? padding to the nearest multiple of 8 bytes

Plus some minor overhead per page and index / table. Details in the manual or in this related answer on SO.
Therefore, you should be able to further optimize the above approach. Take the first 64 bit (or last or whatever is most distinctive and works for you), cast it to bigint and build an index on this expression.
CREATE INDEX tbl_b_col64_idx ON tbl ((b_col::bit(64)::bigint))

I cast twice (b_col::bit(64)::bigint) for there is no cast defined between varbit and bigint. Details in this related answer on SO:

Convert hex in text representation to decimal number

Effectively, this is just a very fast and simple hash function, where the hash value also allows to look up ranges of values. Depending on exact requirements you could go one step further and use any IMMUTABLE hash function - like md5(). Details in the answer linked above.
The query to go along with that:
SELECT *
FROM   tbl
WHERE  b_col::bit(64)::bigint = '1111011110111101'::bit(64)::bigint -- utilize index
AND    b_col = '1111011110111101'::varbit;  -- narrow down to exact match

The resulting index should be just as big as the one in the first example, but queries should be considerably faster for three reasons:

The index typically returns much fewer hits (64 bit of information vs. 10 bit)

Postgres can work with integer arithmetic, which should be faster, even for a plain = operation. (Didn't test to verify that.)

The type integer has no overhead like varbit - 5 or 8 bytes. (In my installation 5 bytes for up to 960 bit, 8 bytes for more).
Effectively, to keep the index at its minimum size, you can only pack 24 bit into a varbit index - compared to 64 bit of information for a bigint index.

CLUSTER
In such a case CLUSTER should improve performance:
CLUSTER TABLE tbl USING tbl_b_col10_idx;

It's a one-time operation and has to be repeated at intervals of your design. Be sure to read the manual on CLUSTER if you want to use that. Or consider the alternative pg_repack. Details:

Configuring PostgreSQL for read performance

If the first 64 bit of your values are unique most of the time, CLUSTER will barely help, since the index scan will return a single row in most cases. If not, CLUSTER will help a lot. Consequently, the effect will be far greater for the first example with the less optimized index.


INSERT with ON CONFLICT does not insert rows as expected We have two tables:
CREATE TABLE listing_master (
    id SERIAL PRIMARY KEY,
    feed_id character varying(20) NOT NULL,
    feed_listing_id character varying(20) NOT NULL,
    updated_time timestamp(0) without time zone NOT NULL
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX listing_master_pkey ON listing_master(id int4_ops);
CREATE UNIQUE INDEX listing_master_feed_id_feed_listing_id_idx ON listing_master(feed_id text_ops,feed_listing_id text_ops);

And
CREATE TABLE listing (
    id SERIAL PRIMARY KEY,
    mls_num character varying(20),
    feed_listing_id character varying(20) NOT NULL,
    feed_id character varying(255) NOT NULL,
    list_price double precision,
    postal_code character varying(255) DEFAULT NULL::character varying,
    photos_count integer,
    unparsed_address character varying(255) DEFAULT NULL::character varying,
    city character varying(255) DEFAULT NULL::character varying,
    status character varying(20) NOT NULL DEFAULT 'new'::character varying,
    processing_status character varying(20) NOT NULL DEFAULT 'none'::character varying,
    last_update_from_feed timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
    images_data jsonb,
    coordinates point,
    raw_data jsonb,
    deleted_date timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
    state_or_province text,
    country text,
    type character varying(40) DEFAULT NULL::character varying,
    ownership_type character varying(40) DEFAULT NULL::character varying,
    bedrooms integer,
    living_area integer,
    lot_size integer,
    year_built integer
);
COMMENT ON COLUMN listing.images_data IS '(DC2Type:json_array)';

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX listing_pkey ON listing(id int4_ops);
CREATE UNIQUE INDEX listing_feed_id_feed_listing_id_idx ON listing(feed_id text_ops,feed_listing_id text_ops);
CREATE UNIQUE INDEX listing_mls_num_feed_id_state_or_province_idx ON listing(mls_num text_ops,feed_id text_ops,state_or_province text_ops) WHERE state_or_province IS NOT NULL AND status::text = 'live'::text AND mls_num IS NOT NULL AND deleted_date IS NULL;

listing_master is populated from an API call and holds all the records that eventually have to exist in the listing table.
So, let's assume
listing_master has 100k records
listing has somewhat less
In order to verify that I ran this query:
select *
from listing_master
where feed_id = 'ddf'
 and feed_listing_id not in (select feed_listing_id
                             from listing
                             where feed_id = 'ddf'
                               and deleted_date is null );

And, as promised, this gave me 136 records back.
Now, I want to populate the missing listings from listing_master into listing, and trying it this way:
insert into listing(feed_id,feed_listing_id,status,processing_status)
select lm.feed_id, lm.feed_listing_id, 'new' as status,'none' as processing_status
from listing_master lm
on conflict (feed_id,feed_listing_id) do nothing;

For some reason, this inserts 0 records, while it clearly should add 136 of them.
What am I missing?
I am in PostgreSQL 12.

I see an elephant in the room:
... and deleted_date is null

There can be rows with non-null deleted_date, which are ignored by your test with  SELECT but still conflict in the unique index on (feed_id,feed_listing_id).
Aside, NOT IN (SELECT ...) is almost always a bad choice. Even if it's not going to break completely with all NOT NULL columns in your case, it still typically delivers bad performance. See:

Select rows which are not present in other table

Use NOT EXISTS instead:
SELECT *
FROM   listing_master  m
WHERE  feed_id = 'ddf'
AND    NOT EXISTS (
   SELECT FROM listing
   WHERE  feed_id = 'ddf'
   AND    feed_listing_id = m.feed_listing_id
   AND    deleted_date is null                 -- ?????
   );

What's more, even with deleted_date is null out of the way, this expectation of yours is strictly incorrect:

... while it clearly should add 136 of them.

You only tested for feed_id = 'ddf'. There can be any number of additional rows with a different feed_id. So it would be "at least 136 of them".

If that's not it, we need to talk about possible concurrent write access. Related:

Concurrent transactions result in race condition with unique constraint on insert
How to use RETURNING with ON CONFLICT in PostgreSQL?

Or possible index corruption. In this case the cure would be:
REINDEX INDEX listing_feed_id_feed_listing_id_idx;


Delete comment - actually delete it, or create `deleted` column and update it? I'm really not sure if this is the right community to ask it in, but it seemed like it fits here.

But anyway, I'd like to know what's the usual approach of deleting a comment from database through website?

What I mean: when the user decides to delete his comment for example, does the comment actually get deleted from the database, or do developers create a column like deleted - int(1) default 0, where 0=not deleted, 1=deleted, and that column is being updated from 0 to 1 when a comment is being deleted? In this case, only comments where the deleted column is 0 would be displayed on the site.

Or is there no usual approach for this at all?

I'd say this depends on the set requirements - many projects demand that the database never forgets "things" - to be able to generate reports and so on. In these cases you usually have this deleted column already. On delete you update said column - and for the report-functions you have an additional table that keeps record of who "ordered the delete" (just in case "he" deleted something that wasn't meant to be forgotten).


If archiving is disabled and the last redo log file is full, why LGWR continues by overwriting the first available active file, not inactive file? I found the following sentence from Oracle documentation.

  If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

My question is, active log file must be used when Oracle doing instance recovery, so if active log files are overwritten, this will cause some problem if you encountered an instance failure now. If so, why LGWR writes to the first active (instead of inactive) log files?

Thanks.

You probably misunderstood the documentation. Online Logfiles are used in a cyclic fashion, regardless whether you are in archivelog mode or not. The difference is that LGWR may have to wait until the next (in line) online log is being archived before it is allowed to write into it.

Never will LGWR overwrite an Online Logfile that is still in status ACTIVE - exactly because else an Instance Recovery would be impossible. It has to wait at least until the checkpoint, associated to that ACTIVE log is completed and it becomes INACTIVE.

Look here (again?) for a more detailed explanation:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/onlineredo001.htm


Eliminate duplicates in different order from all combinations, can I order returned columns alphatically? this should actually be not a too hard task, but it seems to be a little too early for me to see the solution:

I have 4 different string values.

I want to have all combinations of those 4 values where not all values have to be present, so combining all values by cross joining those values zero times, one time, two times and three times. Like I also demonstrate in this fiddle:

CREATE TABLE BaseValues (ID INT PRIMARY KEY IDENTITY(1,1), Title VARCHAR(63));

INSERT BaseValues (Title)
  SELECT 'One'
  UNION
  SELECT 'Two'
  UNION
  SELECT 'Three'
  UNION
  SELECT 'Four';

SELECT Title AS T1 FROM BaseValues;

SELECT
    BV1.Title AS T1,
    BV2.Title AS T2
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  WHERE BV1.Title <> BV2.Title;

SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV2.Title <> BV3.Title;

SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3,
    BV4.Title AS T4
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  CROSS JOIN BaseValues BV4
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV1.Title <> BV4.Title
    AND BV2.Title <> BV3.Title
    AND BV2.Title <> BV4.Title
    AND BV3.Title <> BV4.Title;

Now this is almost the result I want, except I still have duplicates inside of that.
I do not care in what order values appear in my result set I just want to have rows in which those combination of values are unique; for example from the second result set here the rows containing One Two and Two One would be equivalent to me and I just want one of them.

I thought maybe I could concatenate all columns from my result set into one column that contains those values in alphabetical order so a SELECT DISTINCT would eliminate my undesired duplicates.

But I have not been able to figure out how I could do that or maybe there is another more convenient way?

Any hints are appreciated.

I'm not 100% sure what you're trying to achieve, but would something like this solve if for you?
(If so, I'm sure there's a more effective method as well).

SELECT
BV.Title,
BV2.Title
FROM dbo.BaseValues AS BV
CROSS APPLY dbo.BaseValues AS BV2
WHERE bv.Title <> BV2.Title
AND BV.ID <= BV2.ID

SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV2.Title <> BV3.Title
    AND BV1.ID <= BV2.ID
    AND BV2.ID <= BV3.ID

    SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3,
    BV4.Title AS T4
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  CROSS JOIN BaseValues BV4
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV1.Title <> BV4.Title
    AND BV2.Title <> BV3.Title
    AND BV2.Title <> BV4.Title
    AND BV3.Title <> BV4.Title
    AND BV1.ID <= BV2.ID
    AND BV2.ID <= BV3.ID
    AND BV3.ID <= BV4.ID


Finding out the exact size of the database after inserting records? I have this query:

SELECT
  table_schema "db.name"
, Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM
  information_schema.tables
GROUP BY
  table_schema;

Does this query show accurate results?

To be honest with you, that formula is correctly expressed but does not reveal fragmentation. If you were to defragment all your tables, then, yes, it is accurate.

If you want to figure out the fragmentation, let give you my method.

I have an old post, How do you remove fragmentation from InnoDB tables?. If you look on the bottom of that post, I show you how to find the fragmentation of a single InnoDB table. Let us go through this exercise.

SCENARIO

datadir is /var/lib/mysql
Given table mydb.mytable

Physical file /var/lib/mysql/mydb/mytable.ibd (If table is InnoDB)
Physical file /var/lib/mysql/mydb/mytable.MYD (If table is MyISAM)
Physical file /var/lib/mysql/mydb/mytable.MYI (If table is MyISAM)

TOTAL SIZE OF A SINGLE TABLE (mydb.mytable)

You would run the query

SELECT data_length+index_length tblsize
FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

From the OS

DB=mydb
TB=mytable
cd /var/lib/mysql/${DB}
TBL_OSBYTES=0
for X in `ls -l ${TB}.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done

Combining the two methods, you get this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_PASS} -p${MYSQL_PASS}"
DB=mydb
TB=mytable
cd /var/lib/mysql/${DB}
TBL_PHYBYTES=0
for X in `ls -l ${TB}.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done
SQL="SELECT data_length+index_length"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND table_name='${TB}'";
TBL_LOGBYTES=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBL_FRGBYTES = TBL_PHYBYTES - TBL_LOGBYTES ))
echo "Table Stats for ${DB}.${TB}"
echo "Physical Size : ${TBL_PHYBYTES}"
echo "Logical Bytes : ${TBL_LOGBYTES}"
echo "Fragmentation : ${TBL_FRGBYTES}"

TOTAL SIZE OF A DATABASE (mydb)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_PASS} -p${MYSQL_PASS}"
DB=mydb
cd /var/lib/mysql/${DB}
TBL_PHYBYTES=0
for X in `ls -l *.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done
SQL="SELECT data_length+index_length"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}'";
TBL_LOGBYTES=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBL_FRGBYTES = TBL_PHYBYTES - TBL_LOGBYTES ))
echo "Table Stats for ${DB}.${TB}"
echo "Physical Size : ${TBL_PHYBYTES}"
echo "Logical Bytes : ${TBL_LOGBYTES}"
echo "Fragmentation : ${TBL_FRGBYTES}"

TOTAL SIZE OF ALL DATABASES

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_PASS} -p${MYSQL_PASS}"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')";
DBLIST=""
for DB in `mysql ${MYSQL_CONN} -ANe"${SQL}"` ; do DBLIST="#{DBLIST} ${DB}" ; done
TBL_PHYBYTES=0
for DB in `echo "${DBLIST}"`
do
    cd /var/lib/mysql/${DB}
    for X in `ls -l *.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done
done
SQL="SELECT data_length+index_length"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')";
TBL_LOGBYTES=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBL_FRGBYTES = TBL_PHYBYTES - TBL_LOGBYTES ))
echo "Table Stats for ${DB}.${TB}"
echo "Physical Size : ${TBL_PHYBYTES}"
echo "Logical Bytes : ${TBL_LOGBYTES}"
echo "Fragmentation : ${TBL_FRGBYTES}"

GIVE IT A TRY !!!


PostgreSQL query and shape in json array? I have a json array stored in my postgres database in jsonb column. The json data is facebook ad data and look like this:

{
"cpc": "1.1",
"cpm": "11.1",
"cpp": "11.1",
"ctr": "0.11",
"ad_id": "11",
"reach": "1",
"spend": "1.1",
"clicks": "11",
"actions": [
    {
        "value": "11",
        "action_type": "video_view"
    },
    {
        "value": "1",
        "action_type": "post_reaction"
    },
    {
        "value": "11",
        "action_type": "mobile_app_install"
    }
],
"ad_name": "ad name here",
"adset_id": "11111",
"date_stop": "2015-05-25",
"frequency": "1.0511",
"adset_name": "adset name here",
"date_start": "2015-05-20",
"campaign_id": "11111111",
"impressions": "1111",
"account_name": "Account name here",
"campaign_name": "campaign_name here",
"unique_clicks": "11",
"unique_actions": [
    {
        "value": "111",
        "action_type": "video_view"
    },
    {
        "value": "11",
        "action_type": "post_reaction"
    },
    {
        "value": "11",
        "action_type": "mobile_app_install"
    },
    {
        "value": "11",
        "action_type": "link_click"
    }
],
"publisher_platform": "instagram",
"inline_link_click_ctr": "0.1111"

}

Now, I can select the firt elements like:

select jdata->>'cpc' as CPC,
   jdata->>'ad_name' as ad_name,
   jdata->>'cpm' as CPM,
   jdata->>'cpp' as CPP,
   jdata->>'actions' as actions
from my_table_name

I want to have row data by selecting and it returns similiar kind that I need. But for actions element not. Because "actions" element is a json array. I need the "actions" element like json to row but like this format;

---------------------------------------------------------------------
| ad_name        |actions->video_view  |actions->mobile_app_install |
---------------------------------------------------------------------
| ad name here   |         11          |            1000            |
---------------------------------------------------------------------
| 2 ad name here |         12          |            10001           |
---------------------------------------------------------------------

I have queried with jsonb_array_elements function, but it did not return that i need?

Any help and suggestion appreciated

You can use jsonb_path_query_first function. Here is a sample usage.

SELECT
    jdata->>'ad_name' as ad_name,
    jsonb_path_query_first(
      jdata ,
    '$.actions[*] ? (@.action_type == "video_view").value'
    ) AS "actions->video_view",
    jsonb_path_query_first(
      jdata ,
    '$.actions[*] ? (@.action_type == "mobile_app_install").value'
    ) AS "actions->mobile_app_install"
  FROM test

ad_name      | actions->video_view | actions->mobile_app_install
:----------- | :------------------ | :--------------------------
ad name here | "11"                | "33"

db<>fiddle here
You can read The SQL/JSON Path Language for reference.


sqlplus: ORA-01017 logon denied from one host but OK from another I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain:

1) I have an Oracle 11.2 server running on a virtual host, say host1
2) I set up another VM, say host2 (CentOS 6.5 x86_64) with oracle-instantclient 11.2.0.4 64bit, built up from a minimal install ISO
3) I also downloaded a CentOS 6.5 x86_64 VM image from the net (a desktop install with everything already installed), say host3, on which I installed the same Oracle instantclient

I use the same environment and tnsnames.ora file on both host2 and host3, something like:

MONSTER=
(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CRM)))

Issue:
On host2, I cannot get rid of the ORA-01017 error:

host2> sqlplus sys/<passwd>@monster as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 1 12:09:51 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

BUT, on host3, everything is OK:

host3> sqlplus sys/<passwd>@monster as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 1 06:21:59 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release ...
SQL>

Has someone an idea why connecting from one host is OK while connecting from the other one is KO?

NB: Needless to say this is not an issue of case sensitivity, or bad orapwd, or remote_login_passwordfile not set to EXCLUSIVE, since it works from host3.

Thanks

At first make sure the database host is available ping host1.

Then you have to install a client and configure service name.

To install the oracle client correctly and be able to use it you have to:

Choose the "Administrator" type of the oracle client installation, not instant client, it provides you database administrator tools.
After your client installation run Net Manager (netmgr) to add a new service name. When you start testing the connection (Final step), make sure the "scott" user is available in your database.
Then run tnsping YOUR_SERVICE_NAMECONNECTION_NAME(Thanks @vapcguy for the correction) - tnsping is a tool that comes in the administration installation).
Run sqlplus sys/passwd@<YOUR_SERVICE_NAME> as sysdba or sqlplus sys/passwd@host1:PORT/<DATABASE_SERVICE_NAME>


Oracle setup required for heavy-ish load I am trying to make a comparison between a system setup using Hadoop and HBase and achieving the same using Oracle DB as back end. I lack knowledge on the Oracle side of things so come to a fair comparison.
I am looking for what kind of Oracle setup is required to handle a certain work load (hardware, OS, software stack, etc.).
The work load and non-functional requirements are roughly this:

12M transactions on two tables with one simple relation and multiple (non-text) indexes within 4 hours. That amounts to 833 transactions per second (TPS), sustained. This needs to be done every 8 hours.

Make sure that all writes are durable (so a running transaction survives a machine failure in case of a clustered setup) and have a decent level of availability?
With a decent level of availability, I mean that regular failures such as disk and a single network interface / tcp connection drop should not require human intervention. Rare failures, may require intervention, but should be solved by just firing up a cold standby that can take over quickly.

Additionally add another 300 TPS, but have these happen almost continuously 24/7 across many tables (but all in pairs of two with the same simple relation and multiple indexes)?

Some context: this workload is 24/7 and the system needs to hold 10 years worth of historical data available for live querying. Query performance can be a bit worse than sub-second, but must be lively enough to consider for day-to-day usage.
The ETL jobs are setup in such a way that there is little churn. Also in a relational setup, this workload would lead to little lock contention. I would expect index updates to be the major pain. To make a comparison as fair as possible, I would expect the loosest consistency level that Oracle provides.
I have no intention of bashing Oracle in favor of some non-relational DB solution. I think it is a great database for many uses. I am trying to get a feeling for the tradeoff there is between going open source (and NoSQL) like we do and using a commercially supported, proven setup.

Firstly, I will say that this workload is by no means heavy by Oracle standards; thousands of commits/sec are possible, easily. Secondly, however, what matters here is not your database and it's not your server: it's your storage. There are many options here; you won't go too wrong with something like NetApp (I don't work for them, just a satisfied user) and the question is, what size? Here ORION is your friend. Whatever storage array you choose, this will take care of your first level of resilience, if your first node fails you simply mount the disks on another and start back up again, and Oracle will perform crash recovery so no data will be lost.

My advice is, get some numbers at a lower level - MB/s, IOPs - by performing a representative benchmark and take those to your nearest storage vendor and ask them what they've got. With NetApp at least, you can start fairly small and grow, adding another head for more resilience/better performance, adding shelves for more capacity, etc etc. Then test the crap out of it with Orion!


Postgres - Encoding, Collation and CType List of databases
          Name           |  Owner   | Encoding |          Collate           |           Ctype            |
-------------------------+----------+----------+----------------------------+----------------------------|
 MyDatabase              | postgres | UTF8     | English_United States.1252 | English_United States.1252 |

Can someone explain how Encoding, Collate, and Ctype relate to one another?

I know that encoding affects how the information is actually stored (IE whether 'A' requires one byte or multiple bytes, and what value those bytes have, depend on the encoding).

I've been told that collate specifies rules for comparing characters.  If you were to sort a bunch of strings, the collate type would dictate the order.

I've struggled to find what Ctype is; possibly related to concepts like uppercase and lowercase (given 'a' knowing that 'A' is the uppercase form?).

I don't understand how (as in my example) I can have a UTF8 encoded database and use a collate value of English 1252.  UTF8 has many characters that win1252 does not; what happens if I attempt to sort or compare them?  Is my current setup nonsensical....it seems like I would always want the Encoding/Collate/Ctype to agree?

I know that encoding affects how the information is actually
    stored (IE whether 'A' requires one byte or multiple bytes,
    and what value those bytes have, depend on the encoding).

Yes. Encoding is the character-to-byte conversion algorithm.
For mono-byte encoding such as LATIN1, it's trivially
byte value = character number, but for UTF-8 it's more
complicated.

  I've been told that collate specifies rules for comparing
   characters. If you were to sort a bunch of strings, the collate
   type would dictate the order.

Yes, collate specifies how strings are compared. The collation
service is provided by the operating system or optionally
by the ICU library for PostgreSQL 10 or newer.
Sorting arbitrary strings with linguistic rules is a
complicated business.
The Unicode standard and ISO provide rules but they're heavily
customizable and not all C libraries implement them fully anyway.
See for instance https://en.wikipedia.org/wiki/Unicode_collation_algorithm
for more.

  I've struggled to find what Ctype is; possibly related to concepts
  like uppercase and lowercase (given 'a' knowing that 'A' is the
  uppercase form?).

LC_CTYPE in POSIX is related to the functions in ctype.h
Postgres is strongly influenced by POSIX in that matter and uses a
per-database lc_ctype more or less like POSIX uses LC_CTYPE.

Aside from upper() and lower(), it's also relevant for
regular expressions and full text search (case-folding of tokens).

I think that Postgres could do without an independant lc_ctype
(using the same value as lc_collate) except for one thing:
it's interesting to have lc_ctype to something.UTF-8 for instance, to
have the full range of characters properly supported, and
lc_collate to C because C as a collation is much faster than any linguistic sort implied by lc_collate=lang_country.UTF-8.

Robert Haas (postgres committer) wrote an interesting post about essentially that subject when the COLLATE support was enhanced in Postgres: "The Perils of Collation-Aware Comparisons"

  I don't understand how (as in my example) I can have a UTF8 encoded
   database and use a collate value of English 1252. UTF8 has many
   characters that win1252 does not

This is a Windows-specific thing, due to Windows not following the
POSIX model with locales and collations. On Windows and with non-ICU
collations, Postgres will convert the strings from the db encoding to
UTF-16 (wchar_t) and call wcscoll_l(). This is why the encoding is decorrelated
from the collations.

With ICU collations, either Postgres passes directly UTF-8
contents if it can, or it converts the strings to UTF-16,
so again collations are not tied to a particular database encoding,
contrary to the POSIX model and its family of strcoll functions.


MariaDB ignores binlog_format I'm trying to make moodle work on my system, so i need to change bin_log format to MIXED or ROW.

But, i'm having a lot of problems doing that. Any changes i try to do don't work;

MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format'
    -> ;
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format'
    -> ;
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

And i've also set binlog_format=row on my.cnf.
One weird thing i noticied is that it seems that the basedir is wrong. the mariadb basedir is /etc/mysql, but it shows /usr as basedir:

MariaDB [(none)]> SHOW VARIABLES LIKE 'basedir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /usr  |
+---------------+-------+

Anyone knows what the problem can be?

"Global" settings are initial values for when you login.  You failed to log out and log back in, hence it still said STATEMENT.

/etc/ is a place for configuration-like stuff, not for basedir.

/usr does not seem right for basedir, either.  Perhaps /usr/mysql or /usr/mariadb?


Contact table for customers and employees I have this relationship that I need to define in my database.
What would be a good way of referring contact details to my employee and to my customer in MYSQL?
I am not sure if the ||------|| is one to one or bidirectional? Which means the Employee can be searched for by contact details?
When I search for ContactDetails, phone number for example to get the Customer or Employee details

Update
Customer would have:
customerId(PK)
maybe other fields too.
Employee would have
employeeId(Pk)
maybe other fields too
ContactDetails would have:
contactDetailsId(PK)
phoneNumber
emailAddress
other fields
I want when I search for the customerId to get the details I have in the Customer table as well as the Contact details. Same for Employee.
How should I model my tables to achieve that?

with this structure you can find every employye that a customer has, only by querying the Customercontacts table, and only if you need more details from the other tables you join them.
edit.
with this revision  Iadded a date so that you can search for a tine frame where the customer has contact with ione of the empoyees

CREATE TABLE customer (cid BIGINT PRIMARY KEY)

CREATE TABLE customerDeatails (
cdid BIGINT PRIMARY KEY
, cid BIGINT
, Details TEXT
,  FOREIGN KEY (cid) REFERENCES customer(cid) )

CREATE TABLE employee (eid BIGINT PRIMARY KEY)

CREATE TABLE CustomerContactss(
cid BIGINT
, eid BIGINT
,contactdate Datetime
, Details TEXT
, PRIMARY KEY (cid,eid,contactdate)
,  FOREIGN KEY (cid) REFERENCES customer(cid)
,FOREIGN KEY (eid) REFERENCES employee(eid))

db<>fiddle here


how to resolve innodb cluster conflict I am currently maintaining an innodb cluster with three nodes. It runs well but sometimes one node will be MISSING then I have to bring it up online again.

The problem is I inserted a table without primary key. Then a node failed.
When I want to rejoin the failure node to cluster it says it can not join because there is a table without a primary key. I altered the table in cluster to give it primary key and the failure node still complains the same. So I deleted the table in failure node and expected it will rebuild the table.

Now it says

ERROR: Group Replication join failed.
ERROR: Error joining instance to cluster: '192.168.123.45@3306' - Query failed. 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication (RuntimeError)

after I run cluster.checkInstanceState('root@192.168.123.45:3306');

The instance '192.168.123.45:3306' is invalid for the cluster.
The instance contains additional transactions in relation to the cluster.

{
    "reason": "diverged",
    "state": "error"
}

I know it is because the inconsistent state in different db. But I searched the internet but there's no documentation about how to resolve conflict in innodb cluster.
Any advice would be helpful!

Well... So I ended up partially wipe the database and resync it again by reset master and drop database mysql_innodb_cluster_metadata. Now It works fine.

Still wondering if there's anyway to rewind gtid instead of full reset...


Trying to repair query store, but sp_query_store_consistency_check does not exist The query store on our SQL Server 2016 Standard (SP2 CU6) database has gone into ERROR state and we are now trying to get it back online. All of the articles I've seen regarding troubleshooting QS (e.g. this one, and even Microsoft's own docs) suggest to do the following:

ALTER DATABASE [QDS] SET QUERY_STORE = OFF
exec [QDS].dbo.sp_query_store_consistency_check
ALTER DATABASE [QDS] SET QUERY_STORE = ON
ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

But the procedure sp_query_store_consistency_check doesn't seem to exist in any form on our instance. What are we missing?

Like the other Query Store related DMVs, functions, etc, sp_query_store_consistency_check is in the "sys" schema, not the dbo schema.  I imagine this is just a typo on Tracy's blog.  Still, the command should work even when referencing the dbo schema.

You also need to call the procedure from the user database where you have query store enabled.  Try this:

USE YourDatabaseName;
GO

EXEC sys.sp_query_store_consistency_check;

Tony mentioned in the comments that he couldn't find the stored procedure definition anywhere.  This is typical of extended stored procedures.  Running EXEC sp_helptext 'sys.sp_query_store_consistency_check'; on it, even from a DAC connection, returns this:

Text
-----------------
(server internal)

You can see it mentioned in sys.all_objects though:

SELECT
    [name],
    [type_desc],
    is_ms_shipped
FROM sys.all_objects
WHERE [name] LIKE '%consistency%';

Results:

name                              type_desc                 is_ms_shipped
--------------------------------- ------------------------- -------------
sp_query_store_consistency_check  EXTENDED_STORED_PROCEDURE 1

My mistake, I just realized that you are on SQL Server 2016.  The above procedure was introduced in SQL Server 2017.

The only way I know of to get the Query Store back into a good state in 2016 is to clear out all the data:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE CLEAR ALL;


Mysql query group by to be combined with order by desc I have the following query as below.
SELECT Day(vehicleTrip.startDateTime) As day,
       Month(vehicleTrip.startDateTime) As month,
       Sum(TIMESTAMPDIFF(SECOND, startDateTime, endDateTime )) As totalDuration,
       count(vehicleTrip.startDateTime) As totalTrip,
       sum(endMileage-startMileage) As totalMleage
FROM vehicleTrip
WHERE vehicleTrip.vehicleID=:vehicleID
  AND vehicleTrip.vehicleEndCodeID!=1
  AND vehicleTrip.startDateTime BETWEEN :startDateTime And :endDateTime
Group By Day(vehicleTrip.startDateTime),Month(vehicleTrip.startDateTime)

Currently it works fine cause it can group by the daily date the sum of duration, totalTrip, totalMileage etc. But only issue I want the date to be arrange by desc. I have tried putting the order by startDateTime desc it gives me error that its not in GROUP By clause and containts nonaggregated column. I have tried many method like even putting the Max(vehicleTrip.startDateTime) still the same. How best can I resolve this ?
Below is my table design with its index.
CREATE TABLE `vehicleTrip` (
  `vehicleEndCodeID` tinyint NOT NULL DEFAULT '1',
  `vehicleID` mediumint NOT NULL,
  `startDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `endDateTime` timestamp NULL DEFAULT NULL,
  `startMileage` float(11,3) DEFAULT NULL,
  `endMileage` float(11,3) DEFAULT NULL,
  `startLatitude` float(10,8) NOT NULL,
  `startLongitude` float(11,8) NOT NULL,
  `endLatitude` float(10,8) DEFAULT NULL,
  `endLongitude` float(11,8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `vehicleTrip`
  ADD PRIMARY KEY (`vehicleID`,`startDateTime`) USING BTREE;
COMMIT;

Perhaps this is what you are looking for?
SELECT Day(startDateTime) As "day",
       Month(startDateTime) As "month",
       Sum(TIMESTAMPDIFF(SECOND, startDateTime, endDateTime ))
             As totalDuration,
       count(*) As trips,
       sum(endMileage-startMileage) As totalMleage
FROM vehicleTrip
WHERE vehicleID = :vehicleID
  AND vehicleEndCodeID != 1
  AND startDateTime BETWEEN :startDateTime And :endDateTime
Group By 2, 1
ORDER BY 2 DESC, 1 DESC

(I'm assuming you want to sort by both month and day, latest first?)
Also consider
GROUP BY DATE(startDateTime)
ORDER BY DATE(startDateTime) DESC

More
!= is hard to optimize.  I don't see any way to avoid the 'filesort', but this index might help with performance:
INDEX(vehicleID, startDateTime)   -- in this order

However, since that is already the PRIMARY KEY, do not add this INDEX.
Starting over
Now that the CREATE TABLE has been added (and this is why I ask for such so often), here is how I would answer the question:
When possible, have the same ORDER BY as GROUP BY.  This allows the Optimizer to both at the same time.  In your question, you did not quite have the same stuff in each.
A variation on such is to change the direction:
GROUP BY a     , b
ORDER BY a DESC, b DESC

is also optimal.
(Not relevant here:)  In most cases, any mixture of ASC and DESC on the columns in ORDER BY prevents any optimization of the ORDER BY.
My suggestion of using DATE(..) instead of the two columns avoids the issue and solves the potential bug that will show up next January.  BUT...  It may run into the "only full group by" issues since DAY(..), MONTH(..) is not recognized as a subset of `DATE(..)
To avoid some of the above mess, I started by suggesting
GROUP BY 2     , 1
ORDER BY 2 DESC, 1 DESC

If this fails, I will pull a subquery out of my sleeve.


Dealing with Violation of PRIMARY KEY constraint SSIS How do i deal with this error in SSIS. I'm trying to load a data into an archive table. The package failed and came up with the below error message. I know that there are conflicting keys in the destination table that are being brought over from the source.

How do I deal with this? Any help will be appreciated. The data is about 30M records.

  [OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_xxxxxx'. Cannot insert duplicate key in object 'dbo.mytable'.".

If the primary key is a surrogate (ie an IDENTITY valued INT) then you need to remove it from the field list of the insert (if possible) otherwise (if you need to retain the surrogate key values to support foreign keys) to bracket the insert with set identity_insert on; and set identity_insert off;.