| Newly installed Postgresql 9.2 on same box as 9.1 Posted: 05 Oct 2013 03:39 PM PDT I have a new project at work that is using a PostgreSQL 9.2. But, I'm still having to support a project that uses 9.1. So, I'm trying to configure my local dev box to have both installed. I have gotten 9.2 installed and confirmed it runs fine. However, I can't connect to it. xxxxxxx@xxxxxxx-desktop-ubuntu:~$ sudo /etc/init.d/postgresql restart [sudo] password for xxxxxxx: * Restarting PostgreSQL 9.1 database server [ OK ] * Restarting PostgreSQL 9.2 database server [ OK ] xxxxxxx@xxxxxxx-desktop-ubuntu:~$ su postgres Password: postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5432 Password: psql (9.2.4, server 9.1.9) WARNING: psql version 9.2, server version 9.1. Some psql features might not work. SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. postgres=# \q postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5433 Password: psql: FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$
So, how do I connect to this new instance? I thought that the Ubuntu/OS postgres user would allow me to connect, but it doesn't. Other info: - 9.1 is on the default port: 5432
- 9.2 is on port: 5433
- Using Ubuntu 12.04
|
| Feature SQL Server 2012 - Filetable - The location of the file can not be opened Posted: 05 Oct 2013 09:40 AM PDT When trying to access the files available on the virtual directory created for FileTable, you receive the following error message: "The location of the file can not be opened. Access is not enabled or not you have the permissions for it." Already granted all permissions, however, the application displays this error. |
| Mysql high delay in critical peak | Professional Help request Posted: 05 Oct 2013 12:15 PM PDT I have a Centos 6.4 under 2.6.32-358.18.1.el6.x86_64, running Asterisk 11& A2billing in real-time switching with Mysql 5.5.33. My Server it's a Dell R420 having dual Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz, 32 Cores, and 32 Bbs Ram. My problem is I'm facing serious mysql delay in processing requests in peak hours, where I'm finding over 30 seconds delay in process calls, as asterisk agi need to run about 10 queries, into 10 tables in the DB before processing a call... Below is my.cnf configuration: [mysqld] log-error=/var/log/mysqld.log log_warnings = 1 user=mysql back_log = 75 max_connections = 50000 interactive_timeout=1000 skip_innodb key_buffer_size = 384M myisam_sort_buffer_size = 1028M join_buffer_size = 1M read_buffer_size = 2M sort_buffer_size = 2M table_cache = 1028 thread_cache_size = 32 wait_timeout = 7200 connect_timeout = 40 tmp_table_size = 768M max_heap_table_size = 768M max_allowed_packet = 1024M max_connect_errors = 5000 read_rnd_buffer_size = 1024M bulk_insert_buffer_size = 160M query_cache_limit = 8098M query_cache_size =8048M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM symbolic-links=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock thread_concurrency = 128 net_read_timeout=2400
Below is my load in not so overloaded hour: 147 active SIP dialogs uptime 19:02:21 up 6 days, 13:53, 1 user, load average: 1.63, 1.20, 0.90 top top - 19:05:34 up 6 days, 13:56, 1 user, load average: 0.33, 1.08, 0.95 Tasks: 674 total, 2 running, 672 sleeping, 0 stopped, 0 zombie Cpu(s): 5.8%us, 1.3%sy, 0.0%ni, 92.8%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 32829392k total, 19988176k used, 12841216k free, 218132k buffers Swap: 92371960k total, 10272k used, 92361688k free, 13327708k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13976 mysql 20 0 36.1g 4.1g 6552 S 196.3 13.0 36:17.48 mysqld
19032 root -11 0 17.1g 575m 15m S 36.5 1.8 3031:40 asterisk Please, what I'm really bad calculating in this, I'm confused in several values, here... and I'd even require a Professional help in this, please if any are available for help, professionally, I'd really appreciate immediate intervention and advice. Regards, |
| ER relation with unique key Posted: 05 Oct 2013 07:40 AM PDT I've a problem about my ER diagram. Assume that I want to draw a diagram with student, member and team. Here is my specs: - A student does not have to be a member of any team.
- A student could be a member of at most one team.
member has a unique member ID. I did my ER Diagram: member-id (key attribute) | -------- - ----------- - team -------------------- - - mem - ------------- - student - -------- - -----------
As I said before, student does not have to be a member of any team. In this case, my diagram works fine? I asked it because of the member_id is a unique key. |
| Copy all data to slave before mySQL replication connect Posted: 05 Oct 2013 02:48 PM PDT I try to set up mySQL replication, however I have question before I connect 2 servers Q1. Should I import all data from master into slave before I connect 2 servers? ex. if My db have millions of rows, should I copy all data to slave first? Q2. I have read few post about when disaster strikes on master, it switch to slave, so you have time to fix master. What kind of disaster could happen? I have set up mySQL only SELECT, ADD and UPDATE, just wondering what could happen? |
| my mysql stop running and restart after minutes. why? Posted: 05 Oct 2013 04:40 AM PDT I have a problem with my mysql. My site was down for a few minutes with this error: Unable to connect mysql ...
And I saw this log in \var\log\mysqld.log : 131005 12:23:38 mysqld_safe Number of processes running now: 0 131005 12:23:38 mysqld_safe mysqld restarted 131005 12:23:43 InnoDB: Initializing buffer pool, size = 8.0M 131005 12:23:43 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 2 657658929 131005 12:23:43 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 2 657692436 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 13786 row operations to undo InnoDB: Trx id counter is 0 288771072 131005 12:23:49 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting in background the rollback of uncommitted transactions 131005 12:23:51 InnoDB: Rolling back trx with id 0 288770551, 13786 rows to undo InnoDB: Progress in percents: 1131005 12:23:51 InnoDB: Started; log sequence number 2 657692436 2 3 4 5 6 7 8 9 10 11 12 13 14 15131005 12:23:54 [Note] Event Scheduler: Loaded 0 events 131005 12:23:54 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.66' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 InnoDB: Rolling back of trx id 0 288770551 completed 131005 12:23:58 InnoDB: Rollback of non-prepared transactions completed
Why this is happening? How to fix it? Note: Page Load Speed of my site is very low for a few minutes in day and after this minutes that is ok. |
| Export a subset of data, from mysql production database, to make a test database Posted: 05 Oct 2013 02:57 AM PDT I have a mysql production database, with multiple tables. Tables are related with foreign key constraints. I want to make a small test database, by collecting/exporting only a small subset of data from the whole database (i.e. from all tables), so that foreign key constraints are preserved in the data subset. How can I do that? |
| Out-of-Range and Overflow Handling for float values Posted: 04 Oct 2013 11:41 PM PDT am using mysql 5.5 log version with sqlmode='' .I have many columns with float(9,3) as the type. Say for ex one column taxvalue (9,3) Currently if the user enter the value above 999999.000 MYSQL will save its maximum value,ie 1000000.000 and it will not throw any error. If I set the sqlmode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' my insert procedure will throw error, if the value for taxvalue is greater than 999999.000 Am using this column in a user entry page and that application is written in C#. I wanted to show a validation message to user if he tries to enter the value above 999999.000. what is the best method I can use to make sure,user is getting a validation message before he clicks the save button.?I have tried with regular expression but its not giving me the desired result because user can enter any value with the range of (9,3).. |
| Amazon RDS - Can't connect to MySQL database server? Posted: 04 Oct 2013 09:36 PM PDT So I've been working on this issue for a while now, and I'll explain what I'm trying to do. I'm trying to connect to a MySQL database server, hosted by Amazon Web Services in the Amazon RDS service. The database is up and running, and I've created a security group, as well as an EC2 security group. Within the Ec2 security group, I've opened up port 3306 (the MySQL port), and added it as a rule. I've then applied this rule. I've then connected the security group to the EC2 security group, and I connected the database to the security group. Thus the database should have port 3306 open (and for all I know it does!). So here's the issue. I'm trying to connect to the MySQL server based on the IP of the database I'm given by Amazon Web Services. However, when I attempt to connect via the Terminal command shell, the connection times out, saying that the MySQL connection failed. Here's what I type in: "mysql -h *****.rds.amazonaws.com" where the asterisks are part of the IP address. The error is as follows: "ERROR 2003 (HY000): Can't connect to MySQL server on '*****.rds.amazonaws.com' (60)" I've also tried specifying a username and password to the command, but the same error occurs. Any thoughts as to why the connection is failing? Thanks, any help appreciated! Jake |
| Merge Fields into one Row with a Query Posted: 05 Oct 2013 10:57 AM PDT I am working with Microsoft Office Professional Plus 2010. What I am trying to figure out is how to merge all characters for each DOMAIN field into one row, for example: Q01 DOMAIN Q32 ----------------------------------------------- http://redbull.example.com example.com F http://www.example.com example.com B
What I would like to accomplish through a query is to take the DOMAIN field and create a view which merges the "Q32" into one row, for example: DOMAIN Q32 ---------------- example.com B F
Is this possible through a design query or would I have to write a SQL statement? I tried the following SQL statement to create a UNION with two different tables, though I still didn't get the merge displayed correctly. SELECT DOMAIN, Q32 FROM tblMERGE UNION SELECT DOMAIN, Q32 FROM tblASSET;
|
| Backup Oracle DB using TSM server version 6.3.0 Posted: 05 Oct 2013 04:01 PM PDT I'm new to Oracle and TSM server. I installed Oracle11gR2 and TSM server version 6.3.0 (linux Centos 6.4 OS). I did the configuration and everything works fine. so far I arrive to run the following script using rman: run { allocate channel Channel1 type 'SBT_TAPE' parms 'ENV=(DSMO_NODE=TDPOTSMUSR,DSMO_AVG_SIZE=1,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/ tdpo.opt,DSMI_LOG=/opt/tivoli/tsm/client/api/bin64/logs)'; backup full format 'b_%u_%p_%c' database; release channel Channel1 ; }
I need to know how to backup theOracle DB to a storagepool name ORADisk? I need to understand a bit what does channel mean in a script and what does it actually do. really appreciate your hel and reply. thank you |
| Creating Multiple Indexes on SQL Server Posted: 05 Oct 2013 09:44 AM PDT I am creating multiple indexes on a table in SQL Server. The script is still executing but all the required indexes are showing as objects in Object Explorer. Do the objects get created before the index creation has been completed or once the index has been created succesfully? |
| Server Wide DDL Trigger Permissions Issue Posted: 05 Oct 2013 04:25 AM PDT I am trying to implement a server wide DDL Trigger that will audit the DDL changes across the databases on the server using script very similar to the article found here The trigger will log the DDL events into one table within one database, however I am coming up against permissions issues and some users, even myself with sysadmin privileges, receive the following error message. Msg 297, Level 16, State 1, Procedure LogDDLEvent, Line 19 The user does not have permission to perform this action.
I've read about using the WITH EXECUTE AS clause and creating a login specifically for the purpose of executing the trigger though this doesn't appear to have resolved the issue. Could someone advise on the correct method to implement a trigger of this kind and what permissions are required to users or to a specific login to be used in the WITH EXECUTE AS? EDIT: Some further info The server is running SQL Server 2008 R2 and the DDL Trigger is set to DDL_EVENTS as follows: CREATE TRIGGER LogDDLEvent ON ALL SERVER FOR DDL_EVENTS AS DECLARE @eventInfo XML SET @eventInfo = EVENTDATA() INSERT INTO Tools.audit.DDLEvent VALUES ( REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName , (SELECT CAST(client_net_address AS VARCHAR(128)) FROM sys.dm_exec_connections WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand , @eventInfo -- DDLEventXML )
The above script is how I originally created the trigger and this worked fine for me and some other users, though a user who had db_owner role to a particular database kept receiving the error message above. After trying to implement the WITH EXECUTE AS I found that the trigger would not work for myself. |
| Mysql compatibility between client and server of different versions Posted: 05 Oct 2013 06:25 AM PDT How to know what version of mysql client is compatible with what version of the server? Is there backward or forward compatibility tables from the client and/or server point of view? For a given version of the server, how to know which clients are compatible, and conversely? |
| How to determine when to reduce physical memory for SQL Server? Posted: 05 Oct 2013 01:25 PM PDT If I repeatedly notice SQL Server reporting a lot of free memory, can I likely remove about that amount of memory from the server without affecting performance? Or, can/should it use more for caching? For this particular example, the server is running SQL Server 2012 Standard Edition on Windows Server 2012. It has 20 GB of physical memory and hosts over 100 GB of data. SQL Server is the only application running on the server. SQLServer;Buffer Manager Buffer cache hit ratio 99.737 % Page life expectancy 874 s SQLServer:Memory Manager Database Cache Memory 6,744 MB Free Memory 5,937 MB Optimizer Memory 5 MB SQL Cache Memory 29 MB Target Server Memory 19,015 MB Total Server Memory 18,407 MB |
| Create a trigger to update table data on another Server's database Posted: 05 Oct 2013 03:25 AM PDT I am creating a trigger in MySQL and I need a little help. I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2. These databases have the same tables names. I want both the user data on both the websites to be the same. So if one user registers on S1, then that user registration information should be passed to S2. If a user registration information is updated on S1, the same information should be updated on S2. And the same applies for S2. How can I create a trigger so that every time there is an insert / update / delete in database on S1, then the user table on S2 also gets automatically updated. And every time there is an insert / update / delete in database on S2, then the user table on S1 also get automatically updated. Is this possible? Could you provide some examples? |
| creating a report on the mysql jasperReport server Posted: 05 Oct 2013 10:25 AM PDT I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf report or any report without using any tool except jasperReport server. Can I do ? HOw? Note; report will be directly constructed on the whole data reside in the mysql database. (No filtering) I want to see the report on the jasperReport report page. If any change occur on the mysql database, it should be carried out on the report page of the JAsperReport server |
| MySQL error 2006 MySQL server has gone away after upgrade to 5.6.10 using homebrew on Max OS X 10.8.3 Posted: 05 Oct 2013 05:25 AM PDT I upgraded my installation of MySQL on my Mac OS X 10.8.3 using homebrew brew install mysql
Everything seemed to go smoothly, but...not really, as it turns out. I cannot add a new user (neither through the command line nor through phpmyadmin. I always get the error Error in processing request: #2006 MySQL server has gone away
I can create new databases without a problem, and I can install, for example Joomla 3.1.1 with no problems. However, when I try to install a new extension into my Joomla installation, I get errors. For example, I get the following error when I try to install a component from NoNumber: 1062 Duplicate entry '1' for key 'PRIMARY' SQL=INSERT INTO `u5z87_extensions` (`name`,`type`,`element`,`folder`,`client_id`,`enabled`,`access`) VALUES ('plg_system_nnframework','plugin','nnframework','system','0','1','1')
Same thing for many other extensions. I tried to start mysql with mysql.server start --log-warnings=2
and it started with no problem Starting MySQL SUCCESS!
I looked up issues where error #2006 were being reported and how to solve them, and they referred to modifying /etc/my.cnf but there is no /etc/my.cnf configuration file on my system, and there wasn't one in the previous installation of MySQL 5.5.29 (also installed with homebrew) and I had no problems. I ran brew doctor and the output is below $ brew doctor Warning: "config" scripts exist outside your system or Homebrew directories. `./configure` scripts often look for *-config scripts to determine if software packages are installed, and what additional flags to use when compiling and linking. Having additional scripts in your path can confuse software installed via Homebrew if the config script overrides a system or Homebrew provided script of the same name. We found the following "config" scripts: /usr/local/opt/php54/bin/php-config
The output from brew --config is also below: $ brew --config HOMEBREW_VERSION: 0.9.4 ORIGIN: https://github.com/mxcl/homebrew HEAD: 86ce842c700a47abdc354e80cc3b0b3938ab2132 HOMEBREW_PREFIX: /usr/local HOMEBREW_CELLAR: /usr/local/Cellar CPU: 8-core 64-bit nehalem OS X: 10.8.3-x86_64 Xcode: 4.6.2 CLT: 4.6.0.0.1.1365549073 LLVM-GCC: build 2336 Clang: 4.2 build 425 X11: 2.7.4 => /opt/X11 System Ruby: 1.8.7-358 Perl: /usr/bin/perl Python: /usr/bin/python Ruby: /Users/alisamii/.rvm/rubies/ruby-1.9.3-p374/bin/ruby
Exploring a little further, I tried to revert to my previous install of MySQL using brew switch mysql 5.5.29
This resulted in the following output: $ brew switch mysql 5.5.29 Cleaning /usr/local/Cellar/mysql/5.5.29 Cleaning /usr/local/Cellar/mysql/5.6.10 135 links created for /usr/local/Cellar/mysql/5.5.29
I then accessed mysql from the command line and ran status: mysql> status -------------- mysql Ver 14.14 Distrib 5.5.29, for osx10.8 (i386) using readline 5.1 Connection id: 13 Current database: Current user: alisamii@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.10 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 56 min 24 sec
As you can see above, it reports mysql Ver 14.14 Distrib 5.5.29, for osx10.8 (i386) using readline 5.1 But, it also says Server version: 5.6.10 Source distribution So something isn't syncing up. Furthermore, when I show databases, I get the following output: mysql> show databases -> ; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)
But I know I have more than just an information_schema database. in phpMyAdmin, I have 14 databases displaying. So, somehow, it seems to me that mysql is not connecting to the right server, as phpmyadmin reports the same server information that mysql's status command reports: Software version: 5.6.10 - Source distribution. At this point, I am completely lost, and would really need some help. |
| Plugin 'FEDERATED' is disabled Posted: 05 Oct 2013 08:25 AM PDT Im trying to start Mysql using easyPHP : the response is an alert window to a log file. The main error is <-- Plugin 'FEDERATED' is disabled -->. Through my research on internet i found that the solution is to add the federated option to "my.ini" file. I did this but it is still not working. Here is an extract from the log file for more information : 2013-05-03 14:12:57 3776 [Note] Plugin 'FEDERATED' is disabled. 2013-05-03 14:12:57 1b4c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator. 2013-05-03 14:12:57 3776 [Note] InnoDB: The InnoDB memory heap is disabled 2013-05-03 14:12:57 3776 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2013-05-03 14:12:57 3776 [Note] InnoDB: Compressed tables use zlib 1.2.3 2013-05-03 14:12:57 3776 [Note] InnoDB: Not using CPU crc32 instructions 2013-05-03 14:12:57 3776 [Note] InnoDB: Initializing buffer pool, size = 16.0M 2013-05-03 14:12:57 3776 [Note] InnoDB: Completed initialization of buffer pool 2013-05-03 14:12:57 3776 [Note] InnoDB: The first specified data file .\ibdata1 did not exist: a new database to be created! 2013-05-03 14:12:57 3776 [Note] InnoDB: Setting file .\ibdata1 size to 12 MB 2013-05-03 14:12:57 3776 [Note] InnoDB: Database physically writes the file full: wait... 2013-05-03 14:12:57 3776 [Note] InnoDB: Setting log file .\ib_logfile101 size to 5 MB 2013-05-03 14:12:57 3776 [Note] InnoDB: Setting log file .\ib_logfile1 size to 5 MB 2013-05-03 14:12:57 3776 [Note] InnoDB: Renaming log file .\ib_logfile101 to .\ib_logfile0 2013-05-03 14:12:57 3776 [Warning] InnoDB: New log files created, LSN=45781 2013-05-03 14:12:57 3776 [Note] InnoDB: Doublewrite buffer not found: creating new 2013-05-03 14:12:57 3776 [Note] InnoDB: Doublewrite buffer created 2013-05-03 14:12:57 3776 [Note] InnoDB: 128 rollback segment(s) are active. 2013-05-03 14:12:57 3776 [Warning] InnoDB: Creating foreign key constraint system tables. 2013-05-03 14:12:57 3776 [Note] InnoDB: Foreign key constraint system tables created 2013-05-03 14:12:57 3776 [Note] InnoDB: Creating tablespace and datafile system tables. 2013-05-03 14:12:57 3776 [Note] InnoDB: Tablespace and datafile system tables created. 2013-05-03 14:12:57 3776 [Note] InnoDB: Waiting for purge to start 2013-05-03 14:12:57 3776 [Note] InnoDB: 5.6.11 started; log sequence number 0 2013-05-03 14:12:58 3776 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: cadcd135-b3ea-11e2-92bc-e0db55e1304d. 2013-05-03 14:12:58 3776 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 2013-05-03 14:12:58 3776 [Note] - '127.0.0.1' resolves to '127.0.0.1'; 2013-05-03 14:12:58 3776 [Note] Server socket created on IP: '127.0.0.1'. 2013-05-03 14:12:58 3776 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2013-05-03 14:12:58 3776 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened. 2013-05-03 14:12:58 3776 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2013-05-03 14:12:58 3776 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2013-05-03 14:12:58 3776 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened. 2013-05-03 14:12:58 3776 [Note] Event Scheduler: Loaded 0 events 2013-05-03 14:12:58 3776 [Note] C:\PROGRA~1\EASYPH~1.1VC\binaries\mysql\bin\mysqld.exe: ready for connections. Version: '5.6.11-log' socket: '' port: 3306 MySQL Community Server (GPL) 2013-05-03 14:15:59 3776 [Note] C:\PROGRA~1\EASYPH~1.1VC\binaries\mysql\bin\mysqld.exe: Normal shutdown
|
| Why does this procedure raise a privilege error? Posted: 05 Oct 2013 12:25 PM PDT I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine. QUESTIONS - Could somebody help me understand which specific part of this code raises this error, i.e. requires the said privilege?
- Is there any way I could have corresponding functionality without a privileged call?
|
| Archive partition before delete it? Posted: 05 Oct 2013 02:25 AM PDT I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use percona tools or mysqldump itself? IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME = CONCAT(' p' , DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN SET @stmt := CONCAT( 'ALTER TABLE testPart1 DROP PARTITION ' , ' p' , DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ) ); PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME = CONCAT(' p' , DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN SET @stmt := CONCAT( 'ALTER TABLE testPart1 ADD PARTITION (' , 'PARTITION p' , DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ) , ' VALUES LESS THAN (' , TO_DAYS( CURDATE() ) + 2 , '))' ); PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF;
|
| Bi-directional replication for the same MySQL table Posted: 05 Oct 2013 07:25 AM PDT AppA stores/retrieves data from dbA.tableA AppB stores/retrieves data from dbB.tableA tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA (assuming both had 5 rows). row6 was created by AppA (say primary key 6) row7 was created by AppB (say primary key 7) I would like row7 to be copied to dbA.tableA and row6 to dbB.tableA - Is this even possible to setup bi-directional replication, so that the AppA, AppB view the same data at any point in time.
- If the primary key is an auto-increment, would it be possible to maintain integrity of data or is there a possibility that there would be collisions on the primary key.
|
| MySQL5.6 on Mac OS X 10.6.8 problems when granting permissions to root Posted: 05 Oct 2013 11:25 AM PDT I'm having serious problems with a MySQL 5.6 instance on a Mac Server. We had to upgrade MySQL to a newer version, but it turned to be a bad idea, as we lost control to it. We had a backup of the /data directory and the my.cnf file as well. However, when setting an init_file to restore the previous password and its permissions. So we created a text file with this content: UPDATE mysql.user SET Password=PASSWORD('myOriginalPassword') WHERE User='root'; FLUSH PRIVILEGES;
We have double checked the init_file permissions (we even gave it a chmod 777, to make sure it worked), but something is not working. When we run mysqld_safe --init_file=/usr/local/mysql/bin/init_file it tries to start the server, but then it shuts itself down. The log is as follows: 130308 13:23:52 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 2013-03-08 13:23:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2013-03-08 13:23:53 18979 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 2013-03-08 13:23:53 18979 [Note] Plugin 'FEDERATED' is disabled. 2013-03-08 13:23:53 18979 [Note] InnoDB: The InnoDB memory heap is disabled 2013-03-08 13:23:53 18979 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2013-03-08 13:23:53 18979 [Note] InnoDB: Compressed tables use zlib 1.2.3 2013-03-08 13:23:53 18979 [Note] InnoDB: CPU does not support crc32 instructions 2013-03-08 13:23:53 18979 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2013-03-08 13:23:53 18979 [Note] InnoDB: Completed initialization of buffer pool 2013-03-08 13:23:53 18979 [Note] InnoDB: Highest supported file format is Barracuda. 2013-03-08 13:23:53 18979 [Note] InnoDB: 128 rollback segment(s) are active. 2013-03-08 13:23:53 18979 [Note] InnoDB: Waiting for purge to start 2013-03-08 13:23:53 18979 [Note] InnoDB: 1.2.10 started; log sequence number 19440939 2013-03-08 13:23:53 18979 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-locking' 2013-03-08 13:23:53 18979 [ERROR] Aborting 2013-03-08 13:23:53 18979 [Note] Binlog end 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'partition' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INSERTED' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_METRICS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCKS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_TRX' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'InnoDB' 2013-03-08 13:23:53 18979 [Note] InnoDB: FTS optimize thread exiting. 2013-03-08 13:23:53 18979 [Note] InnoDB: Starting shutdown... 2013-03-08 13:23:54 18979 [Note] InnoDB: Shutdown completed; log sequence number 19440949 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'BLACKHOLE' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'ARCHIVE' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MRG_MYISAM' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MyISAM' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MEMORY' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'CSV' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'sha256_password' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_old_password' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_native_password' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'binlog' 2013-03-08 13:23:54 18979 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 130308 13:23:54 mysqld_safe mysqld from pid file /usr/local/mysql/data/server.myServerName.com.pid ended
The problem may definitely come from the [Error] lines, but as we haven't used the --skip-locking, it's just confusing. If we try to log into mysql using mysql -r -p and set the password we used, it can't connect to the server, as it is not started. Any ideas where this problem may come from? |
| PostgreSQL replication for archiving Posted: 05 Oct 2013 09:25 AM PDT I've looked around a bit and haven't found a very clear strategy or method for what I'm trying to do, which is surprising because I would think it would be a fairly common practice. I currently use Bucardo to replicate in a Master Slave setup. This works fine except I want the Slave to retain all records ever written to it. The master is a smaller system which will need to have its tables truncated periodically. I don't want these truncates to be carried over to the Slave. I would think this would be a fairly common practice but have been stumped finding a solution that will work. Could anyone point me in the right direction? The right direction doesn't necessarily need to involve using Bucardo either. Thanks |
| Is it possible to estabilish Dedicated Administrator Connection (DAC) using SSMS? Posted: 05 Oct 2013 11:11 AM PDT Is it possible to connect to the DAC using SSMS? If I understand correctly, it should be possible by using "admin:" prefix before the server name, but I receive the following message: Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer) |
| How do I restore a plain text postgres .backup file Posted: 04 Oct 2013 11:31 PM PDT I exported a postgres db from pgAdmin in a plain format because I wanted to search the whole db text. I then tried to restore the database back but it gives an error saying that the archive file is not valid archive file. How do I restore the db back if it can be restored? |
No comments:
Post a Comment